Обсуждение: URGENT: undoing a mistake
Folks, 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); 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. 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! Thanks for any help. ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
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
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 it. 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 ap ---------------------------------------------------------------------- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu On Wed, 30 Oct 2002, Tom Lane wrote: > 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 >
Andrew Perrin wrote: >- Do a formal backup more often > > > For what it's worth, the FreeBSD "port" (http://cvsweb.freebsd.org/ports/databases/postgresql7/) for PostgreSQL contains a most excellent daily maintenance script (as written it's a FreeBSD "periodic" script, but it's more or less suitable to be a cron job). It does a pg_dump of all the databases (with blobs, as a -Fc) and vacuums all the tables. The one thing it doesn't do is a pg_dumpall -g (to save the users and groups), but I told the author this and he promises to include it in the next version. On a less topical note, there was much rejoicing concerning the ability (in 7.2) to use ident authentication on unix domain socket connections. I have a machine with untrusted (unix) users on it. This made backing up painful, because a password was needed. But now, pg_hba.conf simply has local all ident admin as the last line, and pg_ident.conf has admin pgsql pgsql Thus, unix user pgsql can connect to any database as db user pgsql without a password. Before I upgraded, I had to use local all password pg_suser with a pg_suser file with pgsql and an encrypted password, then set PGPASSWORD in my backup scripts (not the most secure thing to do).
> 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); Ouch. First off, Postgres has transactioning. If you use BEGIN ... [ROLLBACK|COMMIT] you can avoid things like that. (the Perl DBI driver will allow you to do this, check the perldoc page for DBI). As a last resort, you can probably restore from the dump of the table/database you make nightly (riiiight?). Alex
On 31 Oct 2002 at 10:18, Nick Sayer wrote: > Andrew Perrin wrote: > > >- Do a formal backup more often > > > > > > > For what it's worth, the FreeBSD "port" > (http://cvsweb.freebsd.org/ports/databases/postgresql7/) for > PostgreSQL contains a most excellent daily maintenance script (as > written it's a FreeBSD "periodic" script, but it's more or less > suitable to be a cron job). It does a pg_dump of all the databases > (with blobs, as a -Fc) and vacuums all the tables. For those looking for this script: http://www.freebsd.org/cgi/cvsweb.cgi/ports/databases/postgresql7/file s/502.pgsql -- Dan Langille : http://www.langille.org/