Fw: Problem with plpgsql functions and foreign key constraints.

I forgot to mention that this is happening on 7.0.3and 7.1.1  -- and I'm
running on a RedHat 7.0 machine.

----- Original Message -----
From: "Brian Hirt" <bhirt@berkhirt.com>
To: "Postgres Hackers" <pgsql-hackers@postgresql.org>
Cc: <bhirt@mobygames.com>
Sent: Sunday, June 24, 2001 1:12 AM
Subject: Problem with plpgsql functions and foreign key constraints.


> I recently ran across this (i think) bug relating to constraints and
> functions written in plpgsql. It seems that I'm getting erroneous foreign
> key violations.  I've included two scripts which create the simplest test
> case I can reproduce.  One script has a foreign key defined and the other
> one doesn't.  Other than that, they are identical.  From the data in the
> scripts, it's obvious there aren't any violations of keys.
>
> [postgres@loopy postgres]$ diff /tmp/good.sql /tmp/bad.sql
> 18c18
> < create table c2 ( id int, value_sum int);
> ---
> > create table c2 ( id int references c(id), value_sum int);
>
> [postgres@loopy postgres]$ psql test < /tmp/good.sql
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'c_pkey' for
> table 'c'
> CREATE
> INSERT 19107 1
> INSERT 19108 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> INSERT 19126 1
> INSERT 19127 1
> INSERT 19128 1
> INSERT 19129 1
> INSERT 19130 1
> INSERT 19131 1
> CREATE
> CREATE
> CREATE
> UPDATE 6
>  id | value_sum
> ----+-----------
>   1 |         6
>   2 |        17
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
> [postgres@loopy postgres]$ psql test < /tmp/bad.sql
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'c_pkey' for
> table 'c'
> CREATE
> INSERT 19164 1
> INSERT 19165 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> INSERT 19183 1
> INSERT 19184 1
> INSERT 19185 1
> INSERT 19186 1
> INSERT 19187 1
> INSERT 19188 1
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
> CREATE
> CREATE
> ERROR:  triggered data change violation on relation "c2"
>  id | value_sum
> ----+-----------
> (0 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
>   1
>   2
> (2 rows)
>
>  id
> ----
> (0 rows)
>
>
>