Skip to content

Commit ba9a7e3

Browse files
alvherrealvherre
committed
Enforce foreign key correctly during cross-partition updates
When an update on a partitioned table referenced in foreign key constraints causes a row to move from one partition to another, the fact that the move is implemented as a delete followed by an insert on the target partition causes the foreign key triggers to have surprising behavior. For example, a given foreign key's delete trigger which implements the ON DELETE CASCADE clause of that key will delete any referencing rows when triggered for that internal DELETE, although it should not, because the referenced row is simply being moved from one partition of the referenced root partitioned table into another, not being deleted from it. This commit teaches trigger.c to skip queuing such delete trigger events on the leaf partitions in favor of an UPDATE event fired on the root target relation. Doing so is sensible because both the old and the new tuple "logically" belong to the root relation. The after trigger event queuing interface now allows passing the source and the target partitions of a particular cross-partition update when registering the update event for the root partitioned table. Along with the two ctids of the old and the new tuple, the after trigger event now also stores the OIDs of those partitions. The tuples fetched from the source and the target partitions are converted into the root table format, if necessary, before they are passed to the trigger function. The implementation currently has a limitation that only the foreign keys pointing into the query's target relation are considered, not those of its sub-partitioned partitions. That seems like a reasonable limitation, because it sounds rare to have distinct foreign keys pointing to sub-partitioned partitions instead of to the root table. This misbehavior stems from commit f56f8f8 (which added support for foreign keys to reference partitioned tables) not paying sufficient attention to commit 2f17844 (which had introduced cross-partition updates a year earlier). Even though the former commit goes back to Postgres 12, we're not backpatching this fix at this time for fear of destabilizing things too much, and because there are a few ABI breaks in it that we'd have to work around in older branches. It also depends on commit f456634, which had its own share of backpatchability issues as well. Author: Amit Langote <amitlangote09@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reported-by: Eduard Català <eduard.catala@gmail.com> Discussion: https://postgr.es/m/CA+HiwqFvkBCmfwkQX_yBqv2Wz8ugUGiBDxum8=WvVbfU1TXaNg@mail.gmail.com Discussion: https://postgr.es/m/CAL54xNZsLwEM1XCk5yW9EqaRzsZYHuWsHQkA2L5MOSKXAwviCQ@mail.gmail.com
1 parent 3f513ac commit ba9a7e3

File tree

11 files changed

+926
-80
lines changed

11 files changed

+926
-80
lines changed

‎doc/src/sgml/ref/update.sgml

+7
Original file line numberDiff line numberDiff line change
@@ -316,6 +316,13 @@ UPDATE <replaceable class="parameter">count</replaceable>
316316
partition (provided the foreign data wrapper supports tuple routing), they
317317
cannot be moved from a foreign-table partition to another partition.
318318
</para>
319+
320+
<para>
321+
An attempt of moving a row from one partition to another will fail if a
322+
foreign key is found to directly reference an ancestor of the source
323+
partition that is not the same as the ancestor that's mentioned in the
324+
<command>UPDATE</command> query.
325+
</para>
319326
</refsect1>
320327

321328
<refsect1>

0 commit comments

Comments
 (0)