Обсуждение: Disruptive corruption of data during an update

[Using postgresql-8.4.8-0squeeze1 under GNU-linux/debian]


Hi all!

an entire table of my db has been corrupted as a consequence of this erroneous
update command:
--------
mydb=> update bibl set argomento='curatori', autore='DELLA VOLPE Galvano \
       (con due capitoli aggiunti e note di)  where n_prog=3285;
mydb'> ';

UPDATE 12845
mydb=>
--------

Having an adequate back-up I think I'll be able to recover the data, but
can someone explain what the hell happened?
I realized the single quote was missing after the closing parenthesis
and just wrote it plus a second semicolon ';' ....

Regards,
     ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.    \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]    (°|°)
 Ricevo solo messaggi Content-Type: plain/text (no html o multipart).   )=(
 !!! --> e-mail a mio nome via OE (M$) sono false  e infette <-- !!!

On 29 March 2012 16:47, Ennio-Sr <nasr.laili@tin.it> wrote:
> --------
> mydb=> update bibl set argomento='curatori', autore='DELLA VOLPE Galvano \
>       (con due capitoli aggiunti e note di)  where n_prog=3285;
> mydb'> ';
>
> UPDATE 12845
> mydb=>
> --------
>
> Having an adequate back-up I think I'll be able to recover the data, but
> can someone explain what the hell happened?

Because of the single quote being at the wrong place, your query was
processed as though not having a where clause at all, and the ENTIRE
table got updated with the text:

DELLA VOLPE Galvano \
       (con due capitoli aggiunti e note di)  where n_prog=3285;

in the autore field (and similarly in the argomento field).

What I typically do is to attach "where 1 = 0" to inhibit a query from
making any changes, if I find an error in a multiline query and do not
want to Control-C out of psql.

Binand

* Binand Sethumadhavan <binand@gmx.net> [290312, 17:03]:
>
> Because of the single quote being at the wrong place, your query was
> processed as though not having a where clause at all, and the ENTIRE
> table got updated with the text:
> [...]
> in the autore field (and similarly in the argomento field).
>
Oh yes, I did realize that, but too late ;(

> What I typically do is to attach "where 1 = 0" to inhibit a query from
> making any changes, if I find an error in a multiline query and do not
> want to Control-C out of psql.
>
> Binand

Thanks for your suggestion, regards,
     ennio


--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.    \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]    (°|°)
 Ricevo solo messaggi Content-Type: plain/text (no html o multipart).   )=(
 !!! --> e-mail a mio nome via OE (M$) sono false  e infette <-- !!!

On Thursday, March 29, 2012 05:03:03 PM Binand Sethumadhavan wrote:
> What I typically do is to attach "where 1 = 0" to inhibit a query from
> making any changes, if I find an error in a multiline query and do not
> want to Control-C out of psql.

It is also a good idea to always always do a "begin" before doing any manual
update, and only commit changes if it looks like it did the right thing.