Problem with plpgsql functions and foreign key constraints.
От | Brian Hirt |
---|---|
Тема | Problem with plpgsql functions and foreign key constraints. |
Дата | |
Msg-id | 006e01c0fc7d$5ba18f00$640b0a0a@berkhirt.com обсуждение исходный текст |
Список | 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@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)
В списке pgsql-hackers по дате отправления: