Re: [GENERAL] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how can i view deleted records?
On Thu, May 04, 2006 at 02:42:29PM -0500, Jim C. Nasby wrote: 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. There's the contrib/spi/timetravel module, though maybe it does something slightly different. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] how can i view deleted records?
There used to be a knob that would allow you to temporarily see deleted tuples. Don't know if it's still there. Sounded kinda dangerous. http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php Also, you could start (now) using PITR so you could simply restore to the moment before the records you are interested in were deleted. Good luck, - Ian On 4/29/06, Dan Black [EMAIL PROTECTED] wrote: Hello, everybody! How can I view deleted records in table? -- Verba volent, scripta manent Dan Black ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] how can i view deleted records?
Hello, everybody! How can I view deleted records in table? -- Verba volent, scripta manent Dan Black ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how can i view deleted records?
On Apr 29, 2006, at 12:49 PM, Dan Black wrote: Hello, everybody! How can I view deleted records in table? SELECT * FROM recycle_bin; ;-) Just kidding... once you delete your records... they are gone. You could restore your database... assuming that you have a backup. -Robby Robby Russell Founder Executive Director PLANET ARGON, LLC Ruby on Rails Development, Consulting Hosting www.planetargon.com www.robbyonrails.com +1 503 445 2457 +1 877 55 ARGON [toll free] +1 815 642 4968 [fax] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] how can i view deleted records?
On Apr 29, 2006, at 4:18 PM, Robby Russell wrote: On Apr 29, 2006, at 12:49 PM, Dan Black wrote: Hello, everybody! How can I view deleted records in table? SELECT * FROM recycle_bin; ;-) 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. 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. It's certainly not something a DBA should even think about (outside of security issues) but deleted tuples are available in a forensics situation, as long as vacuum hasn't been run. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
