Re: Strange INSERT phenomenon with key constraints

PERL buffers its output -- you'll need to flush it after each line


On Wed, 2 May 2001, Lukas Ertl wrote:

> 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 Universitt Wien
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------