Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitba9a7e3

Browse files
committed
Enforce foreign key correctly during cross-partition updates
When an update on a partitioned table referenced in foreign keyconstraints causes a row to move from one partition to another,the fact that the move is implemented as a delete followed by an inserton the target partition causes the foreign key triggers to havesurprising behavior. For example, a given foreign key's delete triggerwhich implements the ON DELETE CASCADE clause of that key will deleteany referencing rows when triggered for that internal DELETE, althoughit should not, because the referenced row is simply being moved from onepartition of the referenced root partitioned table into another, notbeing deleted from it.This commit teaches trigger.c to skip queuing such delete trigger eventson the leaf partitions in favor of an UPDATE event fired on the roottarget relation. Doing so is sensible because both the old and the newtuple "logically" belong to the root relation.The after trigger event queuing interface now allows passing the sourceand the target partitions of a particular cross-partition update whenregistering the update event for the root partitioned table. Along withthe two ctids of the old and the new tuple, the after trigger event nowalso stores the OIDs of those partitions. The tuples fetched from thesource and the target partitions are converted into the root tableformat, if necessary, before they are passed to the trigger function.The implementation currently has a limitation that only the foreign keyspointing into the query's target relation are considered, not those ofits sub-partitioned partitions. That seems like a reasonablelimitation, because it sounds rare to have distinct foreign keyspointing to sub-partitioned partitions instead of to the root table.This misbehavior stems from commitf56f8f8 (which added support forforeign keys to reference partitioned tables) not paying sufficientattention to commit2f17844 (which had introduced cross-partitionupdates a year earlier). Even though the former commit goes back toPostgres 12, we're not backpatching this fix at this time for fear ofdestabilizing things too much, and because there are a few ABI breaks init that we'd have to work around in older branches. It also depends oncommitf456634, which had its own share of backpatchability issuesas 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.comDiscussion:https://postgr.es/m/CAL54xNZsLwEM1XCk5yW9EqaRzsZYHuWsHQkA2L5MOSKXAwviCQ@mail.gmail.com
1 parent3f513ac commitba9a7e3

File tree

11 files changed

+926
-80
lines changed

11 files changed

+926
-80
lines changed

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

Lines changed: 7 additions & 0 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp