Thread

1. Recovery Features

   Simon Riggs <simon@2ndquadrant.com> — 2004-07-05T20:21:57Z

   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
   
   
   

2. Re: Recovery Features

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-05T21:30:56Z

   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
   
   

3. Re: Recovery Features

   Simon Riggs <simon@2ndquadrant.com> — 2004-07-05T22:16:48Z

   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
   
   
   

4. Re: Recovery Features

   Mike Mascari <mascarm@mascari.com> — 2004-07-05T22:40:51Z

   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
   
   
   
   

5. Re: Recovery Features

   Simon Riggs <simon@2ndquadrant.com> — 2004-07-05T23:22:26Z

   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
   
   
   

6. Re: Recovery Features

   Mike Mascari <mascarm@mascari.com> — 2004-07-05T23:30:21Z

   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
   
   
   
   
   

7. Re: Recovery Features

   Simon Riggs <simon@2ndquadrant.com> — 2004-07-06T07:05:06Z

   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
   
   
   

8. Re: Recovery Features

   Jan Wieck <janwieck@yahoo.com> — 2004-07-10T14:04:48Z

   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 #
   
   
   

9. Re: Recovery Features

   Simon Riggs <simon@2ndquadrant.com> — 2004-07-10T19:21:40Z

   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
   
   
   
   
   

10. Re: Recovery Features

    Jan Wieck <janwieck@yahoo.com> — 2004-07-10T20:01:13Z

    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 #