Re: BUG #14124: ON UPDATE CASCADE failure on repeated foreign key column

targen(at)gmail(dot)com writes:
> postgres=# create table bar (z int, foreign key (z, z) references foo (x, y)
> on update cascade);

Hmm. Such a constraint is specifically forbidden by the SQL standard,
see SQL:2011 11.8 <referential constraint definition> syntax rule 8:

Each referencing column shall identify a column of the referencing
table, and the same column shall not be identified more than once.

Can you produce a convincing use-case for this structure?

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

It would be difficult to support that, AFAICS, because the FK enforcement
queries do not depend on things like whether the specific values involved
are equal. Nor would one generally expect that an FK constraint should be
allowed to restrict what can be stored in the referenced table.
Moreover, it's not even real clear to me that there is a well defined
notion of "equal" in such a situation. If x and y are of different types,
it's possible they would have different equality rules. (This is somewhat
constrained by the fact that z would have to have equality operators in
common with both; but I do not think that's quite sufficient to remove the
concern.)

It could be argued that we should hew strictly to the letter of the SQL
standard and forbid foreign-key constraints with duplicate referencing
columns. Or maybe allow it only if the FK action is not one where we'd
run into this problem. I'm not terribly excited about trying to support
the case, though.

regards, tom lane