Re: Restore deleted rows

On Wednesday 29 April 2009 14:03:14 Dimitri Fontaine wrote:
> 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).

Sounds similar to Oracle's undo logs.

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

Interesting.

Something like flashback queries would certaily be nice, and it's interesting
that we have most of the machinery to do this stuff already, we just need to
spruce it up a little.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com