Re: UPDATE of partition key
Robert Haas <robertmhaas@gmail.com>
On Fri, Jun 2, 2017 at 7:07 AM, Amit Khandekar <amitdkhan.pg@gmail.com> wrote:
> So, according to that, below would be the logic :
>
> Run partition constraint check on the original NEW row.
> If it succeeds :
> {
> Fire BR UPDATE trigger on the original partition.
> Run partition constraint check again with the modified NEW row
> (may be do this only if the trigger modified the partition key)
> If it fails,
> abort.
> Else
> proceed with the usual local update.
> }
> else
> {
> Fire BR UPDATE trigger on original partition.
> Find the right partition for the modified NEW row.
> If it is the same partition,
> proceed with the usual local update.
> else
> do the row movement.
> }
Sure, that sounds about right, although the "Fire BR UPDATE trigger on
the original partition." is the same in both branches, so I'm not
quite sure why you have that in the "if" block.
>> Actually, it seems like that's probably the
>> *easiest* behavior to implement. Otherwise, you might fire triggers,
>> discover that you need to re-route the tuple, and then ... fire
>> triggers again on the new partition, which might reroute it again?
>
> Why would update BR trigger fire on the new partition ? On the new
> partition, only BR INSERT trigger would fire if at all we decide to
> fire delete+insert triggers. And insert trigger would not again cause
> the tuple to be re-routed because it's an insert.
OK, sure, that makes sense. I guess it's really the insert case that
I was worried about -- if we have a BEFORE ROW INSERT trigger and it
changes the tuple and we reroute it, I think we'd have to fire the
BEFORE ROW INSERT on the new partition, which might change the tuple
again and cause yet another reroute, and in this worst case this is an
infinite loop. But it sounds like we're going to fix that problem --
I think correctly -- by only ever allowing the tuple to be routed
once. If some trigger tries to make a change the tuple after that
such that re-routing is required, they get an error. And what you are
describing here seems like it will be fine.
> But now I think you are saying, the row that is being inserted into
> the new partition might get again modified by the INSERT trigger on
> the new partition, which might in turn cause it to fail the new
> partition constraint. But in that case, it will not cause another row
> movement, because in the new partition, it's an INSERT, not an UPDATE,
> so the operation would end there, aborted.
Yeah, that's what I was worried about. I didn't want a row movement
to be able to trigger another row movement and so on ad infinitum.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Commits
-
Avoid referencing off the end of subplan_partition_offsets.
- 945f71db8452 11.0 landed
-
Allow UPDATE to move rows between partitions.
- 2f178441044b 11.0 landed
-
Remove useless lookup of root partitioned rel in ExecInitModifyTable().
- dca48d145e0e 11.0 cited
-
Factor error generation out of ExecPartitionCheck.
- 19c47e7c8202 11.0 landed
-
Minor preparatory refactoring for UPDATE row movement.
- ef6087ee5fa8 11.0 landed
-
Simplify and encapsulate tuple routing support code.
- cc6337d2fed5 11.0 landed
-
Avoid coercing a whole-row variable that is already coerced.
- 1c497fa72df7 11.0 landed
-
Use ResultRelInfo ** rather than ResultRelInfo * for tuple routing.
- 60f7c0abef03 11.0 landed
-
Make RelationGetPartitionDispatchInfo expand depth-first.
- 77b6b5e9ceca 11.0 cited
-
Expand partitioned tables in PartDesc order.
- 30833ba154e0 11.0 cited
-
Use a real RT index when setting up partition tuple routing.
- f81a91db4d1c 10.0 cited
-
Fix transition tables for partition/inheritance.
- 501ed02cf6f4 10.0 cited
-
Fix confusion about number of subplans in partitioned INSERT setup.
- 78a030a44196 10.0 cited
-
Prevent BEFORE triggers from violating partitioning constraints.
- 15ce775faa42 10.0 cited
-
Fire per-statement triggers on partitioned tables.
- e180c8aa8caf 10.0 cited
-
Fix reporting of violations in ExecConstraints, again.
- c0a8ae7be392 10.0 cited
-
Don't scan partitioned tables.
- d3cc37f1d801 10.0 cited
-
Allow FDWs to push down quals without breaking EvalPlanQual rechecks.
- 5fc4c26db512 9.6.0 cited