Re: PITR and rollback - Mailing list pgsql-hackers

Any chance you can work on save points/nested transactions?  See
doc/TODO.detail/transactions for info.  I can help explaining the ideas
in there.

---------------------------------------------------------------------------

Dhruv Pilania wrote:
> Hi,
> 
> I am a new postgresql developer. needed some help with wal/PITR. Can
> someone working in this area answer my question?
> (the email looks long but the question is simple :) )
> 
> I have been trying to implement undo of transactions using wal. i.e. given
> a xid x, postgres can undo all operations of x. For starters, I
> want to do this in very simple cases i.e. assume x only
> inserts/updates/deletes tuples and does not change database schema. also I
> assume that all of x's wal entries are in one segment.
> 
> The code for this is quite simple if database supports undo or rollback to
> a point in time. There is a lot of discussion on the mailing list about
> PITR. I am eagerly waiting for the PITR code to be available on cvs. so
> my questions are....
> 
> 1. once PITR has been implemented, infinite play forward will work. Will
> undo also be supported? i.e. can we recover to the past from a "current"
> wal log?
> as a very simple scenario---
> xid 1 " insert record y in relation r" commit
> xid 2 " update record x in relation r" commit
> shutdown
> ---now we take database back to start of xid 1.
> 
> if answer to qn 1 is no...
> 2. my approach is something like this,
> scan log back until start of transaction record
> scan forward until commit record
>     if record is for transaction x
>         undo(record)
> to undo,
> use preimage in record and everything else is pretty much same as redo.
> i.e. we open relation, get desired block and work on it etc.
> can someone tell me if this will work?
> 
> 
> hoping someone currently working on wal/pitr can help me on this
> issues....
> 
> thanks,
> Dhruv
> 
> 
> PS.
> 
> transaction dependency tracking
> -------------------------------
> I added support in postgres to do transaction dependency tracking.
> basically, x depends on y if x reads something written by y. I maintain a
> dependency graph and also a corresponding disk based log that is accessed
> only at transaction commit. there is a tool which can be used to query
> this graph. the time over heads are pretty low (< 1%).
> with a dependency graph a DBA can say " I want to undo transaction x and
> all transactions that depend on x".
> 
> so now in the second phase, I am looking at undo of a transactions. any
> thoughts on this are very welcome....
> 
> 
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026