Joseph Shraibman <jks@selectacast.net> writes:
> His question was how to extract the data from postgres, since it is
> there until a vacuum.
Depends. If the table hadn't been touched at all since the erroneous
transaction, he could go into the pg_log file and twiddle the two bits
that give the commit status of that transaction --- change it from
"committed" to "aborted" and voila, it never happened.
However, I imagine he's already done something like "select count(*)
from table" (or anything else that will visit all the tuples), and if
so the deleted tuples are marked on-disk as "known committed dead".
The first scan that verifies a tuple's state as committed will mark
the tuple that way to save later scans from having to consult pg_log
every time.
So at this point, the only way to fix the problem is to change the
tuple headers on disk. In theory it could be done: scan the table,
find tuples marked as deleted by the target transaction number, mark
them not-deleted. In practice we have no tools available to do that
kind of thing; ordinary scans will ignore deleted tuples entirely.
For that matter I don't think it'd be very easy even to determine which
transaction number to look for --- you'd have to guess at it (perhaps by
looking to see which transaction number accounts for the largest number
of deleted tuples in the table).
Bottom line is it's not too easy, though someday there might be a way
to do it.
regards, tom lane
