Skip site navigation (1) Skip section navigation (2)

Re: URGENT: undoing a mistake

From: Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: URGENT: undoing a mistake
Date: 2002-10-31 12:49:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Thanks for your response. I sort of figured I was out of luck. As it
turned out, things weren't nearly as bad as they could be - I had turned
on the SQL query echo to the log, so I had a complete trace of all the
statements that had gone into creating that table. It took a few hours'
work, but with some perl, grep, and manual editing I managed to recreate

Thanks, too, for the explanation of why the query worked as it did.

Lessons learned:
- When doing unusual things to the database, do them within a transaction
- Do a formal backup more often


Andrew J Perrin -
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists(at)perrin(dot)socsci(dot)unc(dot)edu * andrew_perrin (at)

On Wed, 30 Oct 2002, Tom Lane wrote:

> 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

In response to


pgsql-admin by date

Next:From: working4alivingDate: 2002-10-31 13:17:53
Subject: To Blob or Not to Blob? THAT is the question.
Previous:From: Denis BraekhusDate: 2002-10-31 09:27:14
Subject: Re: Behaviour patterns on pgsql (7.1.3)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group