7.0.3 database corruption
От | mlw |
---|---|
Тема | 7.0.3 database corruption |
Дата | |
Msg-id | 3B28B253.FE742DC1@mohawksoft.com обсуждение исходный текст |
Список | pgsql-hackers |
I know you guys want to focus on 7.1 or 7.2, and yes I am trying to move to 7.1, but it won't happen overnight. We have a serious problem with 7.0.3 and data corruption. We have a program that compares records in two tables. It creates a set of SQL scripts that either update, insert, or delelte records based on the comparison of the two tables. These scripts are then run against multiple database servers, which are slaves. Some of these scripts get pretty big, and take a while to run (10s of thousands of records are affected). After we run the scripts the database seems fine. Then we run two SQL scripts which create some summary tables. After we run the scripts, it looks like the database is corrupt. Oddly enough, if we run vacuum prior to running these scripts, the database does not seem to get corrupted. All I really need to know is if anyone has seen anything in the code which would explain this, and if so, do you know if is fixed in 7.1.x? The scripts look like: <<<<<<<<<<<<< stattbl.sql >>> drop table musicstat ; -- This crap is to find oldest new release create temp table lastnr ( name varchar, value date ); insert into lastnr select 'LASTNRUPDATE' as "name", min( to_date(released,'MM/DD/YYYY')) as "value" from new_ztitles where released != 'n/a' ; select name, released as "value" into musicstat from new_ztitles, lastnr where to_date(released, 'MM/DD/YYYY') = lastnr.value limit 1; -- Count number of songs insert into musicstat select 'tracks' as "name", count(*) as "value" from zsong; -- Count number of albums insert into musicstat select 'albums' as "name", count(*) as "value" from ztitles; -- Count number of artists insert into musicstat select 'artists' as "name", count(*) as "value" from dartists; insert into musicstat select 'dotclick_albums' as "name", count(*) as "value" from dotclicktitle ; insert into musicstat select 'dotclick_songs' as "name", count(*) as "value" from dotclicksong ; insert into musicstat select 'deleted songs' as "name", count(*) as "value" from old_zsong ; insert into musicstat select 'deleted titles' as "name", count(*) as "value" from old_ztitles ; insert into musicstat select 'new titles' as "name", count(*) as "value" from ztitles where acd = 'A'; insert into musicstat select 'new songs' as "name", count(*) as "value" from zsong, ztitles where ztitles.acd = 'A' and zsong.muzenbr = ztitles.muzenbr; insert into musicstat select 'updated albums' as "name", count( distinct( muzenbr)) as "value" from zsong where acd = 'U' ; insert into musicstat select 'new artists' as "name", count(*) as "value" from tartists, dartists where tartists.artist = dartists.artist ; insert into musicstat (name,value) values('CONCERTGO' , '1' ); <<<<<<<<< end >>>>>>>>>>>>>>>>> <<<<<<<< newrel >>>>>>>>>>>>>> drop table new_ztitles ; drop table new_releases ; create table new_ztitles as select * from ztitles T where T.origrel > (date_part('year','now'::abstime)-1) and to_date(T.released, 'MM/DD/YYYY') > ('now'::abstime - '30 days'::reltime) and T.datasrc = 1; create index newztitles_muzenbr on new_ztitles (muzenbr); create table new_releases as select T.origrel, T.released, T.title, T.muzenbr, T.artistid, T.performer2 as artist, S.song, S.trackid from new_ztitles T, zsong S where S.muzenbr = T.muzenbr and S.datasrc = 1 order by performer2; -- new releases indexes create index new_rel_artist_lower on new_releases (varchar_lower(artist) ); create index new_rel_artist_meta on new_releases (metatext(artist) ); create index new_rel_artist_strip on new_releases (strip(artist) ); create index new_rel_title_lower on new_releases (varchar_lower(title) ); create index new_rel_title_meta on new_releases (metatext(title) ); create index new_rel_title_strip on new_releases (strip(title) ); create index new_rel_song_lower on new_releases (varchar_lower(song) ); create index new_rel_song_meta on new_releases (metatext(song) ); create index new_rel_song_strip on new_releases (strip(song) ); -- new_ztitles indexes create index newztitles_artistid on new_ztitles (artistid); create index newztitles_cat3 on new_ztitles(cat3); <<<<<<<<end>>>>>>>>>>>>>
В списке pgsql-hackers по дате отправления: