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.... ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html