BUG #5664: index "idx000_mytable19" contains unexpected zero page - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5664
Logged by:          simon
Email address:      xubochin@163.com
PostgreSQL version: 8.3.11
Operating system:   suse 10 Linux omu 2.6.16.60-0.54.5-bigsmp #1 SMP Fri Sep
Description:        index "idx000_mytable19" contains unexpected zero page
Details:

Version information]
    Postgres 8.3.11

[Symptom]
omu=# vacuum mytable19;
ERROR: SQLSTATE XX002: index "idx000_mytable19" contains unexpected zero
page at block 523
HINT:     Please REINDEX it.
LOCATION: _bt_checkpage, nbtpage.c:432

[Operation information before the problem occurs]
1. Define 300 tables for the following table (the value of %d ranges from 0
to 299).
CREATE TABLE mytable%d(
    id integer NOT NULL,
    iformatid integer NOT NULL,
    imodulenum integer NOT NULL,
    icircuitno integer NOT NULL,
    icircuitstatus smallint NOT NULL,
    igrpcode integer NOT NULL,
    icic integer NOT NULL,
    isendamplify smallint NOT NULL,
    ireceiveamplify smallint NOT NULL,
    icallerctrflag smallint NOT NULL,
    icallobserveflag smallint NOT NULL,
    ireserved smallint NOT NULL,
    iv5pcmid smallint NOT NULL,
    icirsortnum integer NOT NULL,
    imgwindex integer NOT NULL,
    sterminationid character varying(16) NOT NULL,
    iserverindex smallint NOT NULL,
    idthflag smallint NOT NULL,
    itid integer NOT NULL,
    icircuittype smallint NOT NULL,
    i_mog integer NOT NULL,
    i_referable integer NOT NULL,
    istatus smallint NOT NULL
);
CREATE UNIQUE INDEX idx000_mytable%d ON mytable%d  USING btree (id DESC,
istatus);
CREATE INDEX idx001_mytable%d ON mytable%d  USING btree (iformatid);
CREATE INDEX idx002_mytable%d ON mytable%d  USING btree (iformatid,
istatus);
CREATE INDEX idx003_mytable%d ON mytable%d  USING btree (imodulenum,
icircuitno, istatus);
CREATE INDEX idx004_mytable%d ON mytable%d  USING btree (igrpcode, icic,
istatus);
CREATE INDEX idx005_mytable%d ON mytable%d  USING btree (igrpcode,
icircuitno, istatus);
CREATE INDEX idx006_mytable%d ON mytable%d  USING btree (igrpcode,
sterminationid, istatus);
CREATE INDEX idx007_mytable%d ON mytable%d  USING btree (id, imodulenum,
istatus);
CREATE INDEX idx008_mytable%d ON mytable%d  USING btree (igrpcode,
iserverindex, imodulenum, istatus, id);
CREATE INDEX idx009_mytable%d ON mytable%d  USING btree (imodulenum,
iserverindex, istatus);
CREATE INDEX idx010_mytable%d ON mytable%d  USING btree (imodulenum,
istatus);
CREATE INDEX idx011_mytable%d ON mytable%d  USING btree (iserverindex,
istatus);
CREATE INDEX idx012_mytable%d ON mytable%d  USING btree (icic, igrpcode,
istatus);
CREATE INDEX idx013_mytable%d ON mytable%d  USING btree (sterminationid,
imgwindex, istatus);
CREATE INDEX idx014_mytable%d ON mytable%d  USING btree (sterminationid,
imgwindex, igrpcode, istatus);
CREATE INDEX idx015_mytable%d ON mytable%d  USING btree (sterminationid,
igrpcode, imodulenum, istatus);
CREATE INDEX idx016_mytable%d ON mytable%d  USING btree (icircuitno,
imodulenum, istatus);
/

2. For each mytable% table, first insert 100,000 records, establishes
indexes, and then circularly run the following commands by simultaneously
starting 600 links (The indexes function normally during the following
operations):
INSERT INTO PUBLIC.MYTABLE%d VALUES(%d, 123777, 456678, 789555, 45, 65455,
9874, 12, 34, 56, 78, 90, 2123, 4456, 5567, 'AAABBBCCCDDD', 4345, 6222, 1,
87, 7894562, 66548, 98)
SELECT * FROM PUBLIC.MYTABLE%d WHERE ID = %d
DELETE FROM PUBLIC.MYTABLE%d WHERE ID = %d

3. The postgres process was killed during the concurrent execution of the
600 links.

4. After the concurrent execution is complete, the system powers off.


[Preliminary analysis]
The index file idx000_mytable19 has 524 pages in total, and page 523 of the
file is blank. When checking the validity of page 523 during the vacuum
operation, the system believes that the page is invalid and reports the
preceding error.
The use of the indexes, however, is not affected. You can obtain the same
information by running select * from mytable19 order by id,istatus and
select * from mytable19.

[Scenario recurrence based on simulation]
  1. Start 600 links to concurrently operate data tables. After a period,
kill the postgres process. Repeat this scenario for more than 50 times, the
symptom does not recur.
  2. Add a breakpoint at the smgrextend function, and kill the postgres
process after about ten minutes. Repeat this scenario for more than 50
times, the symptom does not recur. The blank page is displayed at the last
part each time, which is normal.

[Information obtained from the forums]
  The following solutions are provided in almost all forums:
  1. Set fsync to on (The parameter is set to on in the current version).
  2. Reestablish the indexes.