From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: URGENT: undoing a mistake |
Date: | 2002-10-31 03:31:29 |
Message-ID: | 3467.1036035089@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Eric L. Blevins | 2002-10-31 06:15:00 | Signal 11 |
Previous Message | Robert Treat | 2002-10-31 00:21:58 | Re: [ADMIN] Fwd: [BUGS] Fwd: FATAL 1: Database dialup does not exist in pg_database |