Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJmBQ-00000000QYG-3ifQ for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJmBO-00000009A7L-33Up for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJayu-00000006d6S-3MWm for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:24:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJays-00000000Gok-0vzF for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:24:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=HeaeOwroZnZ8t2XdewjfGq05PGn9v43c++SVUHALzFs=; b=Dtaq7YejGxeqYvJIWBakCb/80z ofvvKgrTI4iWT4eMkcD7S8lPxLFssfBGjp0pWyjLAVBtjn7jOqva52gzWvpskpwyMVsU3OSG5j9IP l/kkSf8A5hjR0vChlX6ZwNwYR62TdEa/4By5cH7KaY6roqqq6PMr1P3NHR2xA98SodwnK7cISOVSx ZHh5ZsPcWLB1PlkTAb7GHlyMBTu5ZU7yvxZyRa0WzmpB/Md9fSQHIHyDKNiVX/U35eDNdUnskdRQU bv95h5Zyj6zV9kr38NwEuf91UC9L5vRPQ2oiaDjyfuoSngL/loFYS77PdqOvKHEyJwTw3Z1NL+8BX 3UsZFn0g==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJayr-00000000gu8-2Bb4 for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:24:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJayp-000000022w2-3bvM for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 18:24:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19781: UPDATE moving a row of a self-referencing partitioned table fails with 23503 although ON UPDATE CASC To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 21 Oct 2026 18:23:49 +0000 Message-ID: <19781-a8a20b6a904d9050@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19781 Logged by: Alperen Keles Email address: alpkeles99@gmail.com PostgreSQL version: 14.24 Operating system: macOS 26 arm64 Description: =20 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 =3D 936; ERROR: insert or update on table "sp_0" violates foreign key constraint "sp_c1_fkey" DETAIL: Key (c1)=3D(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 =3D 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 =3D k + 600; ERROR: insert or update on table "p_hi" violates foreign key constraint "p_r_fkey" DETAIL: Key (r)=3D(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 =3D 936, c1 =3D 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)=3D(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