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