The following bug has been logged on the website:
Bug reference: 14124
Logged by: Manuel Gómez
Email address: targen@gmail.com
PostgreSQL version: 9.4.7
Operating system: Ubuntu Linux 14.04
Description:
Foreign keys with ON UPDATE CASCADE generate invalid UPDATE statements if a
column is used multiple times on the referring side of the foreign key.
postgres=# create table foo (x int, y int, unique (x, y));
CREATE TABLE
postgres=# create table bar (z int, foreign key (z, z) references foo (x, y)
on update cascade);
CREATE TABLE
postgres=# insert into foo values (42, 42);
INSERT 0 1
postgres=# insert into bar values (42);
INSERT 0 1
postgres=# update foo set x = 27, y = 27;
ERROR: multiple assignments to same column "z"
CONTEXT: SQL statement "UPDATE ONLY "bar" SET "z" = $1, "z" = $2 WHERE $3
OPERATOR(pg_catalog.=) "z" AND $4 OPERATOR(pg_catalog.=) "z""
I don't know whether this is expected behavior. Indeed it would make sense
for the process to fail if the two referenced columns are set to different
values, as it would be unclear, in this example, whether z should take its
value from x or y. However, the cascading update should work when they are
indeed equal, and not produce a malformed statement.
I can work around this with manually defined triggers, but this can probably
be fixed. I could provide more complete context for the practical situation
in which this issue arises if motivation is required.
