I'm looking at a couple of features that are possible to add.

>From what people have said before on list, one of the useful features of
PITR is to recover from rogue transactions.

I have a suggestion that I'd like some considered thought on. I've given
it a couple of weeks thought. My first inclination is that it is cool,
my second that it's pretty stupid, my third, well...?

...While recovering, it is very straightforward to simply ignore every
record associated with one (or more) transactions. That gives us the
ability to recover "all apart from txnid X".

This great because: if you can locate the transactionId you desire (a
problem in itself, but lets not dwell on those complexities for now),
then you blow it away, real smooth - as if it had never existed. Neat,
simple, fast. Dropped tables and removed tablespaces are a bit more of a
problem, but deleted rows, accidental full table updates etc can be
undone in a snap (NOT DDL, in short).

This is awful because: transactions are isolated from each other, but
they also provide changes of state that rely on previous committed
transactions. If you change the past, you could well invalidate the
future. If you blow away a transaction and a later one depends upon it,
then you will have broken the recovery chain and will not be able to
recover to present time. This is only useful IF
i) you know for certain no later transaction has taken place
ii) you know there are later transactions, but you either know they are
invalid because of the rogue, or they're just not as important as the
effects of the rogue.
...but could leave you up to your neck if misused.

I'm thinking:
undo_transactionId = X

Don't flame me, just hold you're fire, think about it and get back to
me. It's a slightly off the wall idea, so if you react quickly, you'll
just say no - and if you don't, go think some more. Anyhow, if not, then
you know who to call when it hits the fan....

This also leaves open the door for some more advanced functionality that
blows away the effects of a transaction and all of its time-ordered
descendants, just to make it 100% safe.

Best regards, Simon Riggs


Mon, 05 Jul 2004 21:21:57 +0100
Simon Riggs <simon@2ndquadrant.com>
Recovery Features
Simon Riggs <simon@2ndquadrant.com> writes:
> ...While recovering, it is very straightforward to simply ignore every
> record associated with one (or more) transactions. That gives us the
> ability to recover "all apart from txnid X".

Don't even *think* of going there.

What will happen when transaction Y comes along and wants to modify or
delete a row that was inserted by X?  There's no chance of staying
consistent.

			regards, tom lane

Mon, 05 Jul 2004 17:30:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Recovery Features 
On Mon, 2004-07-05 at 22:30, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > ...While recovering, it is very straightforward to simply ignore every
> > record associated with one (or more) transactions. That gives us the
> > ability to recover "all apart from txnid X".
> 
> Don't even *think* of going there.

Hmmm... thinking is important, as are differing viewpoints. I value
yours and those of everyone else on this list, hence the post.

> What will happen when transaction Y comes along and wants to modify or
> delete a row that was inserted by X?  There's no chance of staying
> consistent.

I did point out this downside...a few sentences down.
**This is awful because: transactions are isolated from each other, but
they also provide changes of state that rely on previous committed
transactions. If you change the past, you could well invalidate the
future. If you blow away a transaction and a later one depends upon it,
then you will have broken the recovery chain and will not be able to
recover to present time.**

Theoretically, this is a disaster area.

Practically, Oracle10g provides similar-ish features...

...Nobody is shouting YES, so its a dodo...

Best regards, Simon Riggs


Mon, 05 Jul 2004 23:16:48 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Recovery Features
Simon Riggs wrote:

> On Mon, 2004-07-05 at 22:30, Tom Lane wrote:
> 
> ...Nobody is shouting YES, so its a dodo...

I can imagine a scenario where the junior DBA accidentally deletes 
all rows from some obscure table that wouldn't have logical 
implications for later transactions. But I suspect most people would 
perform recovery in a separate instance, and just hand-dump/restore 
the table in question. The point at which the above process becomes 
too complex (or less than obvious) for hand-recovery is precisely 
when unforeseen consequences of nixing a single transaction become 
too great.

IMHO,

Mike Mascari



Mon, 05 Jul 2004 18:40:51 -0400
Mike Mascari <mascarm@mascari.com>
Re: Recovery Features
On Mon, 2004-07-05 at 23:40, Mike Mascari wrote:
> Simon Riggs wrote:
> 
> > 
> > ...Nobody is shouting YES, so its a dodo...
> 

> The point at which the above process becomes 
> too complex (or less than obvious) for hand-recovery is precisely 
> when unforeseen consequences of nixing a single transaction become 
> too great.
> 

Agreed. The potential for unforeseen consequences is just too high, and
although I'm fairly sure they would always be spotted during recovery
and cause an error - I think it is something that requires proof. And I
don't have that. So, lets leave that idea alone for 100 years.

> ... hand-recovery ...

hmmm...not sure I know what you mean.

It is very-very-close-to-impossible to edit the transaction logs
manually, unless some form of special-format editor were written for the
purpose.

Is it clear that the PITR features are completely different from
pg_dump? (Which would allow a manual edit and recover). The xlogs are
binary files that refer to all changes to all tables in a cluster
ordered by time, rather than by table.

Best regards, Simon Riggs


Tue, 06 Jul 2004 00:22:26 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Recovery Features
Simon Riggs wrote:

> On Mon, 2004-07-05 at 23:40, Mike Mascari wrote:
>  
> hmmm...not sure I know what you mean.
> 
> It is very-very-close-to-impossible to edit the transaction logs
> manually, unless some form of special-format editor were written for the
> purpose.
> 
> Is it clear that the PITR features are completely different from
> pg_dump? (Which would allow a manual edit and recover). The xlogs are
> binary files that refer to all changes to all tables in a cluster
> ordered by time, rather than by table.

What I meant by hand-restore was

1. A total backup occurrs on Monday morning

2. Transactions occur on Monday, Tuesday and Wednesday, with PITR 
archiving enabled

3. Intern deletes everyting from obscure_table on Thursday afternoon 
and wants to restore it as it was on Thursday morning

4. On some other machine, the total backup is restored into a new 
cluster, the transaction logs replayed to that point-in-time where 
intern deleted everything from obscure_table

5. The table is dumped manually and restored in the production 
database, because it is known that this table has no logicial 
implications for the consisetency of other tables.

That's what I meant by hand-restore.

Mike Mascari




Mon, 05 Jul 2004 19:30:21 -0400
Mike Mascari <mascarm@mascari.com>
Re: Recovery Features
On Tue, 2004-07-06 at 00:30, Mike Mascari wrote:
> Simon Riggs wrote:
> 
> > On Mon, 2004-07-05 at 23:40, Mike Mascari wrote:
> >  
> > hmmm...not sure I know what you mean.
> > 
> > It is very-very-close-to-impossible to edit the transaction logs
> > manually, unless some form of special-format editor were written for the
> > purpose.
> > 
> > Is it clear that the PITR features are completely different from
> > pg_dump? (Which would allow a manual edit and recover). The xlogs are
> > binary files that refer to all changes to all tables in a cluster
> > ordered by time, rather than by table.
> 
> What I meant by hand-restore was
> 
> 1. A total backup occurrs on Monday morning
> 
> 2. Transactions occur on Monday, Tuesday and Wednesday, with PITR 
> archiving enabled
> 
> 3. Intern deletes everyting from obscure_table on Thursday afternoon 
> and wants to restore it as it was on Thursday morning
> 
> 4. On some other machine, the total backup is restored into a new 
> cluster, the transaction logs replayed to that point-in-time where 
> intern deleted everything from obscure_table
> 
> 5. The table is dumped manually and restored in the production 
> database, because it is known that this table has no logicial 
> implications for the consisetency of other tables.
> 
> That's what I meant by hand-restore.
> 

Excellent. Thanks very much for the clarification. I was worried that I
had misrepresented the functionality, but you have it spot on.

The scenario you describe is going to be possible now... 

Best Regards, Simon Riggs


Tue, 06 Jul 2004 08:05:06 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Recovery Features
On 7/5/2004 6:16 PM, Simon Riggs wrote:

> On Mon, 2004-07-05 at 22:30, Tom Lane wrote:
>> Simon Riggs <simon@2ndquadrant.com> writes:
>> > ...While recovering, it is very straightforward to simply ignore every
>> > record associated with one (or more) transactions. That gives us the
>> > ability to recover "all apart from txnid X".
>> 
>> Don't even *think* of going there.
> 
> Hmmm... thinking is important, as are differing viewpoints. I value
> yours and those of everyone else on this list, hence the post.
> 
>> What will happen when transaction Y comes along and wants to modify or
>> delete a row that was inserted by X?  There's no chance of staying
>> consistent.
> 
> I did point out this downside...a few sentences down.
> **This is awful because: transactions are isolated from each other, but
> they also provide changes of state that rely on previous committed
> transactions. If you change the past, you could well invalidate the
> future. If you blow away a transaction and a later one depends upon it,
> then you will have broken the recovery chain and will not be able to
> recover to present time.**
> 
> Theoretically, this is a disaster area.
> 
> Practically, Oracle10g provides similar-ish features...

IF ... the recovery process would be primary key based, and IF the 
database definitions would allow for balance type field handling (the 
log contains value deltas for balance fields instead of overwriting 
them), THEN this would be a direction I would be looking into.

But as things are, the whole recovery is ctid and binary block based. So 
you would now leave out the ctid based changes to several tuples because 
of belonging to said transaction. Later on, an original whole block 
appears in the WAL and overwrites ... so you get what ... partial 
transactions into the recoverd DB?

> 
> ...Nobody is shouting YES, so its a dodo...

No way!

> 
> Best regards, Simon Riggs
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Sat, 10 Jul 2004 10:04:48 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Recovery Features
On Sat, 2004-07-10 at 15:04, Jan Wieck wrote:
> On 7/5/2004 6:16 PM, Simon Riggs wrote:
> 
> > On Mon, 2004-07-05 at 22:30, Tom Lane wrote:
> >> Simon Riggs <simon@2ndquadrant.com> writes:
> >> > ...While recovering, it is very straightforward to simply ignore every
> >> > record associated with one (or more) transactions. That gives us the
> >> > ability to recover "all apart from txnid X".
> >> 
> >> Don't even *think* of going there.
> > 
> > Hmmm... thinking is important, as are differing viewpoints. I value
> > yours and those of everyone else on this list, hence the post.
> > 
> >> What will happen when transaction Y comes along and wants to modify or
> >> delete a row that was inserted by X?  There's no chance of staying
> >> consistent.
> > 
> > I did point out this downside...a few sentences down.
> > **This is awful because: transactions are isolated from each other, but
> > they also provide changes of state that rely on previous committed
> > transactions. If you change the past, you could well invalidate the
> > future. If you blow away a transaction and a later one depends upon it,
> > then you will have broken the recovery chain and will not be able to
> > recover to present time.**
> > 
> > Theoretically, this is a disaster area.
> > 
> > Practically, Oracle10g provides similar-ish features...
> 
> IF ... the recovery process would be primary key based, and IF the 
> database definitions would allow for balance type field handling (the 
> log contains value deltas for balance fields instead of overwriting 
> them), THEN this would be a direction I would be looking into.
> 
> But as things are, the whole recovery is ctid and binary block based. So 
> you would now leave out the ctid based changes to several tuples because 
> of belonging to said transaction. Later on, an original whole block 
> appears in the WAL and overwrites ... so you get what ... partial 
> transactions into the recoverd DB?
> 
> > 
> > ...Nobody is shouting YES, so its a dodo...
> 
> No way!
> 

Sorry...I meant "this idea is dead, just like the extinct Dodo bird".-
I've been trying to be succinct, but that has led to information loss.

...you've come up with an even better reason why the idea is not good.

Many thanks,

Best Regards, Simon Riggs




Sat, 10 Jul 2004 20:21:40 +0100
Simon Riggs <simon@2ndquadrant.com>
Re: Recovery Features
On 7/10/2004 3:21 PM, Simon Riggs wrote:

> On Sat, 2004-07-10 at 15:04, Jan Wieck wrote:

>> > ...Nobody is shouting YES, so its a dodo...
>> 
>> No way!
>> 
> 
> Sorry...I meant "this idea is dead, just like the extinct Dodo bird".-
> I've been trying to be succinct, but that has led to information loss.

I know, "preserve the Dodo way of life!" or did you mean the one from 
Alice in Wonderland? I was only trying to shout "NO" in a slightly more 
polite way :-)


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Sat, 10 Jul 2004 16:01:13 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Recovery Features