Referential Integrity Stress Problem - Mailing list pgsql-hackers

From Mike Cianflone
Subject Referential Integrity Stress Problem
Date
Msg-id B9F49C7F90DF6C4B82991BFA8E9D547B17D246@BUFORD.littlefeet-inc.com
Whole thread Raw
List pgsql-hackers
We're running Postgres 7.0.3.2. We're running into a referential
integrity violation that seems to crop up randomly, and only when stress
testing the system for a day or so.
We've created some stress test code to fill the tables with about
500 nodes, then delete them from the top and let the cascade-delete delete
all the children. (the test code is a script for our own scripting
language). Each insert and delete has a trigger that simply rearranges each
node in the table, like a linked list. That trigger code is only a few lines
and doesn't look to be the problem, since the problem only crops up randomly
after several hours of stressing.
The repeated adding and deleting works fine for quite a few hours
with the stress test program, but then randomly it'll error out and give a
referential integrity violation in one of the tables. In the stress code
we'll do a delete from system where system_index = XX and expect it to
cascade delete, but a table, like the bts table, will give something like
"ERROR:  bts_fk_constraint referential integrity violation - key referenced
from bts not found in system"
Are there any known bugs in 7.0.3.2 that might cause something like
this to crop up randomly?Any ideas or things to check would be greatly appreciated.



Here are the 6 tables. It's a parent-child-grandchild relationship. The
table below each table, simply references back to the previous one as the
foreign key, and builds the foreign key from the foreign key of its parent.


create sequence omc_index_seq;
create TABLE omc (   omc_index int4 PRIMARY KEY DEFAULT NEXTVAL('omc_index_seq'),   serial_number varchar(32),
operator_stringvarchar(255) DEFAULT 'Value not specified.',   debug_level int4 DEFAULT 1,   software_version
varchar(32),  hardware_version varchar(32),   software_failure_reason int2
 
);


create TABLE system (   system_index int4,   display_name varchar(32),   operator_string varchar(255),   id
varchar(32),  next_system_index int4,   parent_omc_index int4 NOT NULL,   CONSTRAINT system_fk_constraint FOREIGN KEY
(parent_omc_index)      REFERENCES omc (omc_index) ON DELETE CASCADE,   CONSTRAINT system_pkey_constraint PRIMARY KEY
(parent_omc_index,                                                 system_index),   CONSTRAINT
system_display_name_uniqueUNIQUE (display_name)
 
);  



create TABLE bts (   bts_index int4,   display_name varchar(32),   operator_string varchar(255),   id varchar(32),
locationvarchar(255),   next_bts_index int4,   parent_omc_index int4 NOT NULL,   parent_system_index int4 NOT NULL,
CONSTRAINTbts_fk_constraint FOREIGN KEY (parent_omc_index,
parent_system_index)      REFERENCES system (parent_omc_index, system_index) ON DELETE
 
CASCADE,   CONSTRAINT bts_pkey_constraint PRIMARY KEY (parent_omc_index,
parent_system_index,                                              bts_index),   CONSTRAINT bts_display_name_unique
UNIQUE(display_name,                                              parent_system_index)
 
);  


create TABLE cell_area (   cell_area_index int4,   display_name varchar(32),   operator_string varchar(255),
cluster_orientationvarchar(255),   id varchar(32),   chan_1_link_channel_num int4,   chan_2_link_channel_num int4,
chan_1_coverage_channel_numint4,   chan_2_coverage_channel_num int4,   next_cell_area_index int4,   parent_omc_index
int4NOT NULL,   parent_system_index int4 NOT NULL,   parent_bts_index int4 NOT NULL,   CONSTRAINT
cell_area_fk_constraintFOREIGN KEY (parent_omc_index,
parent_system_index,                                                  parent_bts_index)       REFERENCES bts
(parent_omc_index,parent_system_index,                       bts_index) ON DELETE CASCADE,   CONSTRAINT
cell_area_pkey_constraintPRIMARY KEY (parent_omc_index,
 
                                                     parent_system_index,
     parent_bts_index,                                                     cell_area_index),   CONSTRAINT
cell_area_display_name_uniqueUNIQUE (display_name,
parent_system_index,                                                   parent_bts_index)
 
); 

create TABLE unit (   unit_index int4,   display_name varchar(32),   operator_string varchar(255),   ip_address
varchar(15)UNIQUE NOT NULL,   phone_number varchar(32),   type char(1),   next_unit_index int4,   parent_omc_index int4
NOTNULL,   parent_system_index int4 NOT NULL,   parent_bts_index int4 NOT NULL,   parent_cell_area_index int4 NOT NULL,
 CONSTRAINT unit_fk_constraint FOREIGN KEY (parent_omc_index,
parent_system_index,                                             parent_bts_index,
       parent_cell_area_index)       REFERENCES cell_area (parent_omc_index, parent_system_index,
     parent_bts_index, cell_area_index)       ON DELETE CASCADE,
parent_system_index,                                               parent_bts_index,
           parent_cell_area_index,                                                unit_index),   CONSTRAINT
unit_display_name_uniqueUNIQUE (display_name,                                               parent_system_index,
                                      parent_bts_index,
parent_cell_area_index),  FOREIGN KEY (type) REFERENCES spice_types (type) MATCH FULL
 
);





pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Bad behaviour when inserting unspecified variable length datatypes
Next
From: Florian Weimer
Date:
Subject: Re: Escaping strings for inclusion into SQL queries