Thread: Disruptive corruption of data during an update
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 <-- !!!
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
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 |
* 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 <-- !!!