Data corruption magically disappeared?
От | Ennio-Sr |
---|---|
Тема | Data corruption magically disappeared? |
Дата | |
Msg-id | 20110830165803.GA609@deby.ei.hnet обсуждение исходный текст |
Список | 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 по дате отправления: