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

Thu, 14 Jun 2001 08:47:15 -0400
mlw <markw@mohawksoft.com>
7.0.3 database corruption
mlw wrote:
> 
> 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?


There certainly are bugs in 7.0.3 - I can describe at least two:

1. an index on varchar(8) (an user name) gets corrupted so that some
names are 
   no longer found when searching by index - they are still there when
doing an
   unqualified select and come back after reindex for the qualified one.

1a. "FATAL: bits falling of the end of world" or something like it in
logs and 
   then broken db connection after that

2. Some kind of stuck locks - a single backend stuck in "INSERT waiting"
or 
   "DELETE waiting" state. This happens sporadically and requires a db
system 
   restart to go away


-------------
Hannu

Thu, 14 Jun 2001 15:28:54 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.0.3 database corruption
> mlw wrote:
>> After we run the
>> scripts, it looks like the database is corrupt.

It's impossible to say anything useful with such an undescriptive
description of the problem.

Hannu Krosing <hannu@tm.ee> writes:
> There certainly are bugs in 7.0.3 - I can describe at least two:

I would really like to see a reproducible example of index corruption
in 7.0.*.  We've heard such reports often enough to know the problem
is real, but without a test case in hand it's difficult to do much about
it.

> 2. Some kind of stuck locks - a single backend stuck in "INSERT waiting"

7.0.*'s deadlock detection algorithm is known to have some holes, but
deadlock couldn't be the explanation for just a single stuck backend.
Again, any chance of looking at an example?

			regards, tom lane

Thu, 14 Jun 2001 10:01:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.0.3 database corruption 
Tom Lane wrote:
> 
> > mlw wrote:
> >> After we run the
> >> scripts, it looks like the database is corrupt.
> 
> It's impossible to say anything useful with such an undescriptive
> description of the problem.
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > There certainly are bugs in 7.0.3 - I can describe at least two:
> 
> I would really like to see a reproducible example of index corruption
> in 7.0.*.  We've heard such reports often enough to know the problem
> is real, but without a test case in hand it's difficult to do much about
> it.

I know ;( Unfortunately this has happened only a few times on some quite 
busy servers receiving a workload of quite varied queries.

> > 2. Some kind of stuck locks - a single backend stuck in "INSERT waiting"
> 
> 7.0.*'s deadlock detection algorithm is known to have some holes, but
> deadlock couldn't be the explanation for just a single stuck backend.

that's what "ps ax| grep post" output looks like in my logs

Sun Jun 10 06:31:00 EET 2001
  828 ?        S      0:02 /usr/bin/postmaster -i -o -F
26652 ?        S      5:20 /usr/bin/postgres localhost gamer casino idle
30082 ?        S      0:20 /usr/bin/postgres 127.0.0.1 nobody casino
idle
30084 ?        S      1:26 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31565 ?        S      0:43 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31595 ?        S      0:19 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31596 ?        S      0:21 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31597 ?        S      0:31 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31598 ?        S      1:39 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31600 ?        S      0:17 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31608 ?        S      0:24 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31612 ?        S      0:24 /usr/bin/postgres 127.0.0.1 nobody casino
idle
32080 ?        S      0:43 /usr/bin/postgres localhost gamer casino
UPDATE waiti
32706 ?        S      0:10 /usr/bin/postgres localhost gamer casino idle
  302 ?        S      0:00 /usr/bin/postgres 127.0.0.1 nobody casino
idle
  361 ?        S      0:00 sh -c date;ps ax|grep post
  364 ?        S      0:00 grep post

CHECKING WAITING PIDS: ['32080']
Sun Jun 10 06:31:10 EET 2001
  828 ?        S      0:02 /usr/bin/postmaster -i -o -F
26652 ?        S      5:20 /usr/bin/postgres localhost gamer casino idle
30082 ?        S      0:20 /usr/bin/postgres 127.0.0.1 nobody casino
idle
30084 ?        S      1:26 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31565 ?        S      0:43 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31595 ?        S      0:19 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31596 ?        S      0:21 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31597 ?        S      0:31 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31598 ?        S      1:39 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31600 ?        S      0:17 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31608 ?        S      0:24 /usr/bin/postgres 127.0.0.1 nobody casino
idle
31612 ?        S      0:24 /usr/bin/postgres 127.0.0.1 nobody casino
idle
32080 ?        S      0:43 /usr/bin/postgres localhost gamer casino
UPDATE waiti
32706 ?        S      0:10 /usr/bin/postgres localhost gamer casino idle
  302 ?        S      0:00 /usr/bin/postgres 127.0.0.1 nobody casino
idle
  365 ?        S      0:00 sh -c date;ps ax|grep post
  368 ?        S      0:00 grep post

PROCESS 32080 STILL WAITING, RESTART TIME


> Again, any chance of looking at an example?

I could send you tails of postgres logfiles that are rotated on
detecting 
the INSERT/UPDATE wait condition that does not go away in 10 sec. 
How long logfiles (time) would be enough ?

There seems to be no general pattern that leads to it though ;(

---------------
Hannu

Thu, 14 Jun 2001 16:34:48 +0200
Hannu Krosing <hannu@tm.ee>
Re: 7.0.3 database corruption
Hannu Krosing <hannu@tm.ee> writes:
> I could send you tails of postgres logfiles that are rotated on
> detecting 
> the INSERT/UPDATE wait condition that does not go away in 10 sec. 
> How long logfiles (time) would be enough ?

Do the logs show the queries being executed?  The queries forming
the current transaction of the stuck backend, and all the transactions
that have occurred since that transaction started, would be useful
to look at.

			regards, tom lane

Thu, 14 Jun 2001 10:37:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.0.3 database corruption 