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

"Jeff MacDonald" <jeff@interchange.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

"Jeff MacDonald" <jeff@interchange.ca> writes:

Hi,

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.

This is on PostgreSQL 7.2.4 on i386-unknown-freebsd4.7, compiled by GCC
2.95.3

Is there anyway that I can retrieve this data ?

Not unless you happen to have a second 'psql' session, with an open
transaction that was started before you did the update in the first
session. If you don't use BEGIN/END explicitly, each statement is its
own transaction, which is automatically committed unless there's an
error.

Since you haven't vacuumed, it's theoretically retrievable from the
raw data files, but you'll have to learn quite a bit about PG data
representation to be able to pull the old records out.

[Please don't preach about backups, I've already
fwapped myself for that one]

Sorry...

-Doug

Thanks Tom,

I think your first question was the most helpful "how desperate am i" ?

Not "terribly", if a tool existed I'd likly use that, but I think i can
recover the data by hand quicker than i can by delving into the pg*
files, as they present a significant learning curve.. [that i don't have
time for at the moment ;)]

thanks tho.

jeff.