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)
>
>
>
