Обсуждение: Using xmax to detect deleted rows
I would like to use the "xmax" column to detect rows that have been recently deleted. Is it possible to get the deleted row versions with non-zero xmax to remain visible long enough that I could periodically check, say once an hour, and still be able to see rows that were deleted since I last checked?
Meel Velliste wrote: > I would like to use the "xmax" column to detect rows that have been > recently deleted. Is it possible to get the deleted row versions with > non-zero xmax to remain visible long enough that I could periodically > check, say once an hour, and still be able to see rows that were deleted > since I last checked? No. Maybe you want a trigger that saves the deleted row somewhere (a separate table perhaps) which you can later inspect and delete again? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 1/8/16 9:02 AM, Alvaro Herrera wrote: > Meel Velliste wrote: >> I would like to use the "xmax" column to detect rows that have been >> recently deleted. Is it possible to get the deleted row versions with >> non-zero xmax to remain visible long enough that I could periodically >> check, say once an hour, and still be able to see rows that were deleted >> since I last checked? > > No. Maybe you want a trigger that saves the deleted row somewhere (a > separate table perhaps) which you can later inspect and delete again? If that is what you need then PgQ might be a good solution. But it'll be a lot simpler to just do whatever you need to do when the row is actually deleted. Just be sure you deal with rollbacks correctly if you're doing something external. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com