Re: URGENT: undoing a mistake

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

30 октября 2002 г. в 22:31:34

Andrew Perrin  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

Re: URGENT: undoing a mistake

От:

Nick Sayer <nsayer@quack.kfu.com>

Дата:

31 октября 2002 г. в 13:22:19

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).

Re: URGENT: undoing a mistake

От:

"Dan Langille" <dan@langille.org>

Дата:

4 декабря 2002 г. в 15:20:32

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/

Re: URGENT: undoing a mistake

От:

"Alex J. Avriette" <avriettea@speakeasy.net>

Дата:

1 ноября 2002 г. в 13:11:40

> 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

URGENT: undoing a mistake

От:

Andrew Perrin <clists@perrin.socsci.unc.edu>

Дата:

30 октября 2002 г. в 15:53:53

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

Re: URGENT: undoing a mistake

От:

Andrew Perrin <clists@perrin.socsci.unc.edu>

Дата:

31 октября 2002 г. в 07:50:20

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  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
>