On Monday 27 April 2009 11:17:42 Tom Lane wrote: > Alvaro Herrera <alvhe...@commandprompt.com> writes: > > Anton Egorov escribió: > >> I need to recover deleted rows from table. After I delete those rows I > >> stopped postgres immediately and create tar archive of database. I found > >> solution > >> http://archives.postgresql.org/pgsql-hackers/2005-03/msg00965.php, but > >> is there another (easyer) way to do it? > > > > I guess you could figure out the Xid of the transaction that deleted the > > tuples, and mark it as aborted in pg_clog; you'd also need to reset the > > hint bits on the tuples themselves. Not necessarily any easier than the > > above, but at least you don't have to patch Postgres code. > > The solution recommended in that message doesn't work anyway --- it will > consider *all* tuples visible, even ones you don't want. Reversing a > single transaction, or small number of transactions, as Alvaro suggests > is much less likely to create a huge mess. >
We had started down the path of making a function to read deleted tuples from a table for a DR scenario we were involved with once. The idea was that you could do something like select * from viewdeletedpages('tablename') t (table type), which would allow you to see the dead rows. It ended up unnessesary, so we never finished it, but I still think the utility of such a function would be high... for most people, if you told them that they could do create table as select * from viewdeletedttuples(...) t(...) after doing a mis-placed delete/update, at the cost of having to sift through extra data, they would make that trade in a heartbeat. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers