Re: Restore deleted rows

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