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

Re: Strange INSERT phenomenon with key constraints

From: "D(dot) Duccini" <duccini(at)backpack(dot)com>
To: Lukas Ertl <l(dot)ertl(at)univie(dot)ac(dot)at>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Strange INSERT phenomenon with key constraints
Date: 2001-05-02 21:30:36
Message-ID: Pine.GSO.4.03.10105021629160.6993-100000@ra.bpsi.net (view raw or flat)
Thread:
Lists: pgsql-novice
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!"   
-----------------------------------------------------------------------------


In response to

Responses

pgsql-novice by date

Next:From: Lukas ErtlDate: 2001-05-02 21:44:01
Subject: Re: Strange INSERT phenomenon with key constraints
Previous:From: Lukas ErtlDate: 2001-05-02 20:24:25
Subject: Strange INSERT phenomenon with key constraints

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