Strange INSERT phenomenon with key constraints - Mailing list pgsql-novice

Hi,

I wanted to do a little benchmark on PostgreSQL 7.1 and though about a
database filled with random stuff.

The tables look like this:

CREATE TABLE foo (
        id serial not null primary key,
        foo varchar(255) not null
);

CREATE TABLE baz (
        id serial not null primary key,
        baz varchar(255),
);

CREATE TABLE bar (
        id serial not null primary key,
        date timestamp not null,
        foo_id integer not null references foo on update cascade
               on delete cascade,
        baz_id integer not null references baz on update cascade
               on delete cascade,
        bar varchar(255) not null,
        parent integer null references bar on update cascade
               on delete cascade
);

So far, so good.

Then I wrote a perl-script, that first fills 1000 entries into foo, 500
entries into baz, and then it should fill 20000 entries into bar.

The problem is that after about 1000 rows of bar, I get a "violation of
reference integrity", because obviously the db thinks, a value that I
wanted to insert into foo_id is not a valid key in foo. (The error shows
up randomly, not exactly at the 1000th row, but always above row 1000.)

I checked the script about fifty times right now, and I printed the values
to STDOUT to see what values are inserted, so I'm quite sure the bug isn't
in my script. The strange thing is that the same value of foo_id was
already used several times in the bar table, but if the rowcount passes
1000, it doesn't get accepted anymore.

Is this a bug, or am I just blind stupid?

lg,
le

--
Lukas Ertl                          eMail: l.ertl@univie.ac.at
WWW-Redaktion                       Tel.:  (+43 1) 4277-14073
Zentraler Informatikdienst (ZID)    Fax.:  (+43 1) 4277-9140
der Universität Wien