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

Data corruption magically disappeared?

From: Ennio-Sr <nasr(dot)laili(at)tin(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: Data corruption magically disappeared?
Date: 2011-08-30 16:58:03
Message-ID: 20110830165803.GA609@deby.ei.hnet (view raw or flat)
Thread:
Lists: pgsql-novice
[Using postgresql-8.4.8-0squeeze1 under GNU-linux/debian]

Hi all!

A few days ago I asked for help in relation to something strange occurred in one of my
tables, where 454 records out of 1964 were showing the same content in 4 non consecutive
fields. Apparently there was no wrong command in .psql_history, although the wrong records were
there! (see: [NOVICE] Data corruption after an update set ...?)

I had a backup for 402 of the corrupted rows and re-wrote the four fields in the remaining 52
so that the original table could be re-constructed. My plan to obtain that was quite simple:
1. delete wrong records from the table (BTW, its name was 'bibl_provv')
2. append to it the corrected records from a provisional table
My action was in fact a bit different and in the middle of it I discovered that the original
table had been corrected automagically, and I cannot understand how this may have happened!

# This is the history of the latest commands given in 'psql mydb'
================================================================
\\q
\\d
# Saved the original corrupted table and made some checking:
create table bibl_provv_errato as select * from bibl_provv order by n_prog;
select count(*) from bibl_provv_errato;
select count(*) from bibl_provv;
select count(*) from bibl_provv_errato where n_prog > 10274;
select count(*) from bibl_provv_errato where n_prog > 10274 and n_prog < 10727;
select count(*) from bibl_provv_errato where n_prog >= 10274 and n_prog <= 10727;
select * from bibl_provv_errato where n_prog >= 10274 and n_prog <= 10727;
# Deleted the corrupted rows:
delete  * from bibl_provv where n_prog >= 10274 and n_prog <= 10727;
delete  from bibl_provv where n_prog >= 10274 and n_prog <= 10727;
# Again some checking and cleaning:
analyze verbose bibl_provv\^J;
vacuum full verbose bibl_provv\^J;
analyze verbose bibl_provv\^J;
# Copied the 52 rows that I had corrected in a text file (I was following a longer way here ...)
\\copy bibl_provv from libri_dacorregg with delimiter '|'
# Some checking:
analyze verbose bibl_provv\^J;
analyze verbose bibl_provv\^J;
select count(*) from bibl_provv;
# AT this point the table contained 1510+52 records (the remaining 402 were in an old table)
# and I renamed it so that I could import the backup table
alter table bibl_provv rename to bibl_provv0\^J;
\\d
# In another console I gave this command from shell:
$ pg_dump biblio_bkup -t bibl_provv | psql mydb ennio
# In my intention it should copy the backup table bibl_provv, containing the remaining 402 rec.
# but when I checked in the previous console (where psql was still running)
\\d
select count(*) from bibl_provv0\^J;
select count(*) from bibl_provv;
select count(*) from bibl_provv0\^J;
\\c
\\c biblio_bkup
\\d
select count(*) from bibl_provv;
# I realized that nothing had happened. I thought that could be related to the fact that psql
# was still going in that console, so I exit it:
\\q
# and when I re-launched psql mydb the table 'bibl_provv' was there, and it contained all the
# original 1964 records (not only the 402 of the backup) and no sign left of the corrupted
# rows?!

Is there any chance to discover how this may have happened?

Thanks for you attention.
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 <-- !!!


pgsql-novice by date

Next:From: bradgDate: 2011-08-30 17:03:25
Subject: Re: Trouble including "pg_type.h"
Previous:From: Tom LaneDate: 2011-08-30 04:06:29
Subject: Re: Known problem? Column mixup in sql-function

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