Re: how can i view deleted records?

On Sun, Apr 30, 2006 at 12:33:04PM +0200, Martijn van Oosterhout wrote:
> On Sat, Apr 29, 2006 at 07:05:35PM -0700, Steve Atkins wrote:
> > >Just kidding... once you delete your records... they are gone.
> >
> > That's.... not true.
> >
> > Deleted (or modified) records don't go away until the space
> > they use is recycled by the VACUUM command.
>
> Well yes, but with autovacuum you don't know when that might be.
>
> > However, there's no support in postgresql for any sort of
> > "time travel", including viewing deleted tuples. The data
> > is there on the disk, but there is no clean way to view it
> > via the database.
>
> Well, there is a timetravel module which you can enable per table. Just
> showing deleted records in general doesn't work well because it
> violates all sorts of constraints. If you show deleted records, all of
> a sudden your unique indexes arn't unique anymore. Timetravel is
> expensive though, which is why it's not by default.

There is? The only time travel capability I know of is
http://www.varlena.com/GeneralBits/122.php. There is also the idea of
having vacuum move old tuples to some form of secondary storage instead
of sending them to the bit-bucket.

An interesting alternative would be to allow for starting a transaction
that uses a different XID for reading data than what it would normally
use. Provided vacuum hasn't nuked anything that old you should
theoretically be able to get a consistent view of data, excluding some
things like TRUNCATE.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461