[YSQL] Incorrect ON UPDATE CASCADE Behaviour with Multiple References

[srhickma]

Jira Link: DB-2510
If a table contains multiple columns referencing the same column of another table, one of which uses ON UPDATE CASCADE, updating the referenced value will cause an error.

For example:

SET default_transaction_isolation = 'SERIALIZABLE';

CREATE TABLE test_table(id int UNIQUE, pk int PRIMARY KEY);

CREATE TABLE ref_table(
    id int,
    ref1 int REFERENCES test_table(id),
    ref2 int REFERENCES test_table(id) ON UPDATE CASCADE
);

INSERT INTO test_table(id, pk) VALUES (1, 1);
INSERT INTO ref_table(ref2) VALUES (1);

UPDATE test_table SET id = 2 WHERE pk = 1;

Produces the following error:

ERROR:  insert or update on table "ref_table" violates foreign key constraint "ref_table_ref2_fkey"
DETAIL:  Key (ref2)=(0) is not present in table "test_table".

In this case it is expected that the value of 1 in the ref_table.ref2 column would be updated to 2, without error.

NOTE: This issue goes away if the order of ref1 and ref2 in the ref_table creation statement are reversed.

[ndeodhar]

Note: This works with snapshot isolation but fails in serializable mode.