Thread: Disruptive corruption of data during an update

Disruptive corruption of data during an update

From
Ennio-Sr
Date:
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?


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


Re: Disruptive corruption of data during an update

From
Steve T
Date:
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



Re: Disruptive corruption of data during an update

From
Ennio-Sr
Date:
* Steve T <steve@retsol.co.uk> [290312, 13:40]:
> On Thu, 2012-03-29 at 13:49 +0200, Ennio-Sr wrote:
>
> 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
>

Thank you Steve, this seems to be a good practice which I must get
accustomed to use!
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 <-- !!!