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

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
>>