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

"Jeff MacDonald" <jeff(at)interchange(dot)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