Hi, On Tuesday 28 April 2009 20:43:38 Robert Treat wrote: > 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.
There has been another idea proposed to solve this problem: http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php The idea is to have VACUUM not discard the no more visible tuples but store them on a specific fork (which you'll want to have on a WORM (cheap) tablespace, separate issue). Then you want to be able to associate the tuple xid info with a timestamptz clock, which could be done thanks to txid and txid_snapshot by means of a ticker daemon. PGQ from Skytools has such a daemon, a C version is being prepared for the 3.0 release (alpha1 released). Hannu said: >Reintroducing keeping old tuples "forever" would also allow us to bring >back time travel feature, that is > >SELECT .... AS OF 'yesterday afternoon'::timestamp; It could be that there's a simpler way to implement the feature than provide a ticker daemon (one more postmaster child), but the linked thread show some other use cases of an integrated ticker. I know we use PGQ alone here to obtain reliable batches as presented at Prato: http://wiki.postgresql.org/wiki/Image:Prato_2008_pgq_batches.pdf Regards, -- dim
signature.asc
Description: This is a digitally signed message part.