Re: ran an update outside of transaction, can i roll it back in anyway ?

"Jeff MacDonald" <jeff(at)interchange(dot)ca> writes:
> This morning in a sleepy daze I typed
> UPDATE boo SET foo = 6;
> at the psql console. i meant to type
> UPDATE boo SET foo = 6 WHERE x = 10;
> I did not have this in a transaction, and have not vacuumed since
> I pressed enter.

> Is there anyway that I can retrieve this data ?

How desperate are you? You could theoretically go into pg_clog and mark
the updating transaction aborted instead of committed (you'd have to
find out its number first, but you could look in the boo table for
that). Then go through boo to clear the known-committed bits from any
tuples touched by the update that have already been examined and marked
known-committed.

AFAIK there aren't tools in existence for either of these tasks,
unfortunately. It'd probably be possible to modify pg_filedump to do
the commit-bit update, and the pg_clog change is a one-byte change that
could be done by hand if you're not afraid of bit-level editing. I'd
definitely recommend making a file-level backup copy of the database so
you can try again if you mess up, though ;-). Also, do NOT try
modifying files from external tools while the postmaster is running.
Shut down, hack, restart.

As long as you don't vacuum the boo table, it won't be too late to
recover. Better turn off any cron-driven vacuuming you might have set
up.

regards, tom lane