Re: URGENT: undoing a mistake

Andrew Perrin <clists@perrin.socsci.unc.edu> writes:
> I just made a mistake that could be quite costly: I did this:
> update writer_survey set partid='W41308' where survid in (select survid
> from participants where partid='W41291' limit 1);
> when I should have done this:
> update writer_survey set partid='W41308' where survid in (select survid
> from writer_survey where partid='W41291' limit 1);

Ooops.

> Is there any way I can undo this, e.g., set partid back to what it was
> before I ran this command? I know I should have done it in a transaction,
> but I didn't.

You're probably out of luck.  Got a recent backup?

(If you were really desperate, and haven't yet vacuumed the table, you
could imagine manually changing the transaction's commit status in
pg_xlog and then clearing any known-committed status bits in the table.
But this is ticklish stuff and there are no tools for it that I know of.)

> More broadly, can someone explain why it worked? There is no survid column
> in participants, so I would have expected it to generate an error on the
> sub-select, not match all rows!

But the sub-select can reference the outer query's variables.  So as
long as "from writer_survey where partid='W41291'" produced at least
one row, the sub-select would return the outer value of survid, and
thus the IN would succeed.

            regards, tom lane