Thread: ran an update outside of transaction, can i roll it back in anyway ?
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 ? [Please don't preach about backups, I've already fwapped myself for that one] Thanks folks, Jeff.
"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
Re: ran an update outside of transaction, can i roll it back in anyway ?
From
"Jeff MacDonald"
Date:
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. >> -----Original Message----- >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] >> Sent: Thursday, April 17, 2003 12:29 PM >> To: Jeff MacDonald >> Cc: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] 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 >>