Skip site navigation (1) Skip section navigation (2)

Referential Integrity Stress Problem

From: Mike Cianflone <mcianflone(at)littlefeet-inc(dot)com>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Referential Integrity Stress Problem
Date: 2001-09-04 19:05:28
Message-ID: B9F49C7F90DF6C4B82991BFA8E9D547B17D246@BUFORD.littlefeet-inc.com (view raw or flat)
Thread:
Lists: 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_string varchar(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_unique UNIQUE (display_name)
);  



create TABLE bts (
    bts_index int4,
    display_name varchar(32),
    operator_string varchar(255),
    id varchar(32),
    location varchar(255),
    next_bts_index int4,
 
    parent_omc_index int4 NOT NULL,
    parent_system_index int4 NOT NULL,
 
    CONSTRAINT bts_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_orientation varchar(255),
    id varchar(32),
    chan_1_link_channel_num int4,
    chan_2_link_channel_num int4,
    chan_1_coverage_channel_num int4,
    chan_2_coverage_channel_num int4,
    next_cell_area_index int4,
 
    parent_omc_index int4 NOT NULL,
    parent_system_index int4 NOT NULL,
    parent_bts_index int4 NOT NULL,
 
    CONSTRAINT cell_area_fk_constraint FOREIGN 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_constraint PRIMARY KEY (parent_omc_index,

                                                      parent_system_index,
                                                      parent_bts_index,
                                                      cell_area_index),
    CONSTRAINT cell_area_display_name_unique UNIQUE (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 NOT NULL,
    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_unique UNIQUE (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

Next:From: Tom LaneDate: 2001-09-04 22:56:06
Subject: Re: Bytea/Base64 encoders for libpq - interested?
Previous:From: Florian WeimerDate: 2001-09-04 18:42:47
Subject: Re: Escaping strings for inclusion into SQL queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group