Problem with plpgsql functions and foreign key constraints.

From: "Brian Hirt" <bhirt(at)berkhirt(dot)com>
To: "Postgres Hackers" <pgsql-hackers(at)postgresql(dot)org>
Cc: <bhirt(at)mobygames(dot)com>
Subject: Problem with plpgsql functions and foreign key constraints.
Date: 2001-06-24 07:12:38
Message-ID: 006e01c0fc7d$5ba18f00$640b0a0a@berkhirt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
good.sql application/octet-stream 958 bytes
bad.sql application/octet-stream 975 bytes

Browse pgsql-hackers by date

  From Date Subject
Next Message Brian Hirt 2001-06-24 07:17:08 Fw: Problem with plpgsql functions and foreign key constraints.
Previous Message Joe Conway 2001-06-24 06:57:07 Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards