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