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(dot)ertl(at)univie(dot)ac(dot)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(at)postgresql(dot)org
>

-----------------------------------------------------------------------------
david(at)backpack(dot)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!"
-----------------------------------------------------------------------------