Re: PITR and rollback - Mailing list pgsql-hackers

> -----Original Message-----
> From: Dhruv Pilania [mailto:dhruv@cs.sunysb.edu]
> Sent: Saturday, July 20, 2002 11:37 PM
> To: pgsql-hackers@postgresql.org
> Cc: pgman@candle.pha.pa.us; richt@multera.com
> Subject: PITR and rollback
>
>
> 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.

Strictly speaking Postgres does not undo transactions but only aborts them.
It does not roll back through the log undoing the effects of the
transaction.  It merely sets the state of the transaction in the commit log
(clog) to aborted.  Then application of the tuple visibility rules prevents
other transactions from seeing any tuples changed by the aborted
transaction.

>
> 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....

What I implemented was a roll forward to a point in time.  That is restore a
backup and roll forward through the wal files until you reach a transaction
that committed at or after the specified time.
I should have a context diff for my roll forward implementation available
against current cvs HEAD by the end of the week.

>
> 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.

The wal file does not contain "preimages" only after images.

> i.e. we open relation, get desired block and work on it etc.
> can someone tell me if this will work?
>
I did a roll forward to a point in time but I think a roll back to a point
in time would work like:
Roll back through the wal files looking for transaction commit records and
change the status in the clog to aborted until you reach the first commit
record that aborted before the specified roll back time.
The main thing that needs to be implemented here is reading backward through
the log files which I'm not sure is possible since the wal records do not
have a length suffix (they have a length prefix for reading forward but I
don't think they have a length suffix as well).

>
> 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....
>
>
>