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

Re: Disruptive corruption of data during an update

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: Ennio-Sr <nasr(dot)laili(at)tin(dot)it>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Disruptive corruption of data during an update
Date: 2012-03-29 12:40:55
Message-ID: 1333024855.1671.8.camel@retsol610 (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2012-03-29 at 13:49 +0200, Ennio-Sr wrote:

> A few minutes ago I wrote: 
> ---- quote
> 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?
> 
> -------- unquote
> 
> Having come out of the initial panic I reconsidered the thing with calm
> and of course 'discovered' that the program has done exactly what I told
> it to do, as the 'where' clause was incorporated within the two «'».
> 
> My question now is: 
> is there any possibility to recover such type of error while postgres is
> still running and no other transacion has been committed?
> 


I cannot help with the recovery, but as a general rule I tend to place
all updates/deletes within an explicit transaction. Obviously you can
then rollback the transaction should the results not be as required.

dev_reinsure=# begin;
BEGIN
dev_reinsure=# update address set addrLine1='N where recno=2';
UPDATE 911
dev_reinsure=# rollback;
ROLLBACK


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


-- 




Steve Tucknott
TuSol Ltd

DDI:         01323 488548



In response to

Responses

pgsql-novice by date

Next:From: ennioDate: 2012-03-29 14:04:26
Subject: Re: Disruptive corruption of data during an update
Previous:From: Ennio-SrDate: 2012-03-29 11:49:01
Subject: Disruptive corruption of data during an update

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