BUG #19781: UPDATE moving a row of a self-referencing partitioned table fails with 23503 although ON UPDATE CASC

The following bug has been logged on the website:

Bug reference: 19781
Logged by: Alperen Keles
Email address: alpkeles99(at)gmail(dot)com
PostgreSQL version: 14.24
Operating system: macOS 26 arm64
Description:

Hello,

A partitioned table has a foreign key to itself with ON UPDATE CASCADE.
An UPDATE that changes a referenced key so that the row moves to another
partition fails with a foreign key violation when a row being moved
still holds the old key, even though the cascade would update it. The
same UPDATE works on a plain table, and on the partitioned table when
the row stays in its partition.

CREATE TABLE sp (c0 int8 PRIMARY KEY, c1 int8) PARTITION BY RANGE (c0);
CREATE TABLE sp_0 PARTITION OF sp FOR VALUES FROM (936) TO (MAXVALUE);
CREATE TABLE sp_1 PARTITION OF sp DEFAULT;
ALTER TABLE sp ADD FOREIGN KEY (c1) REFERENCES sp (c0) ON UPDATE CASCADE;
INSERT INTO sp VALUES (104, 104);
UPDATE sp SET c0 = 936;
ERROR: insert or update on table "sp_0" violates foreign key constraint
"sp_c1_fkey"
DETAIL: Key (c1)=(104) is not present in table "sp".

CREATE TABLE sn (c0 int8 PRIMARY KEY, c1 int8 REFERENCES sn (c0) ON UPDATE
CASCADE);
INSERT INTO sn VALUES (104, 104);
UPDATE sn SET c0 = 936; -- UPDATE 1
TABLE sn; -- (936,936)

I expected (936,936) in sp as well. The UPDATE reference page says row
movement is done as a DELETE plus an INSERT behind the scenes, but since
commit ba9a7e39217 ("Enforce foreign key correctly during cross-partition
updates", v15) foreign keys treat the move as an UPDATE of the root
table, and the partitioning limitations in the docs say nothing about
foreign keys. Changing the key within one partition works.

The row does not have to reference itself. Two rows of the table that
reference each other fail in the same way, as does a row that references
another row when both move in one UPDATE and the referencing row is
processed first:

CREATE TABLE p (k int PRIMARY KEY, r int) PARTITION BY RANGE (k);
CREATE TABLE p_a PARTITION OF p FOR VALUES FROM (0) TO (100);
CREATE TABLE p_b PARTITION OF p FOR VALUES FROM (100) TO (500);
CREATE TABLE p_hi PARTITION OF p FOR VALUES FROM (500) TO (MAXVALUE);
ALTER TABLE p ADD FOREIGN KEY (r) REFERENCES p (k) ON UPDATE CASCADE;
INSERT INTO p VALUES (150, NULL), (50, 150);
UPDATE p SET k = k + 600;
ERROR: insert or update on table "p_hi" violates foreign key constraint
"p_r_fkey"
DETAIL: Key (r)=(150) is not present in table "p".

On a plain table this gives (650,750), (750,NULL). With the rows the
other way round (50 referenced by 150), the partitioned UPDATE works.
I see the same with ON UPDATE SET NULL and SET DEFAULT, with a
two-column key, with list, hash and two-level partitioning, and when the
foreign key is declared on each partition rather than on the parent.
It works if the constraint is DEFERRABLE INITIALLY DEFERRED, or if the
UPDATE also sets the referencing column (SET c0 = 936, c1 = 936). A
separate referencing table is cascaded correctly.

Reproduced on 18.6 (REL_18_STABLE a85b5523e67), 19rc1 (REL_19_STABLE
0fb192f1b02) and master dd568b047ac (2026-10-21), macOS arm64,
--enable-cassert.
14.24 (REL_14_STABLE b1c44848ce3) rejects the first UPDATE too, with a
different error from the delete half of the move:

ERROR: update or delete on table "sp_1" violates foreign key constraint
"sp_c1_fkey2" on table "sp"
DETAIL: Key (c0)=(104) is still referenced from table "sp".

ba9a7e39217 changed the error to the current one.

As far as I can tell, the cause is the order in which the move queues
its after-trigger events. ExecCrossPartitionUpdate() inserts the new row
into the destination partition, which queues that partition's
RI_FKey_check_ins for it. Only after that does
ExecCrossPartitionUpdateForeignKey() queue the UPDATE event on the root,
whose RI_FKey_cascade_upd would rewrite the reference. In
AfterTriggerSaveEvent() the FK-side trigger for that root event is
skipped on the grounds that "the insert event that is fired on the
destination leaf partition would suffice to perform the necessary
foreign key check". So the insert check runs first, looks for the old
key, and finds it already deleted. On a plain table the check is either
not queued (the FK column is unchanged) or runs after the action
trigger. createForeignKeyActionTriggers() names the triggers
RI_ConstraintTrigger_a_/_c_ for this reason: the ON UPDATE action has to
fire before the check for a self-referential FK.

Found with a query fuzzer.

Regards,
Alperen Keles