Re: UPDATE of partition key
Amit Khandekar <amitdkhan.pg@gmail.com>
On 18 May 2017 at 16:52, Amit Kapila <amit.kapila16@gmail.com> wrote: > On Wed, May 17, 2017 at 4:05 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote: >> On Wed, May 17, 2017 at 3:15 PM, Amit Kapila <amit.kapila16@gmail.com> wrote: >>>> Earlier I thought that option1 is better but later I think that this >>>> can complicate the situation as we are firing first BR update then BR >>>> delete and can change the row multiple time and defining such >>>> behaviour can be complicated. >>>> >>> >>> If we have to go by this theory, then the option you have preferred >>> will still execute BR triggers for both delete and insert, so input >>> row can still be changed twice. >> >> Yeah, right as per my theory above Option3 have the same problem. >> >> But after putting some more thought I realised that only for "Before >> Update" or the "Before Insert" trigger row can be changed. >> > > Before Row Delete triggers can suppress the delete operation itself > which is kind of unintended in this case. I think without the user > being aware it doesn't seem advisable to execute multiple BR triggers. By now, majority of the opinions have shown that they do not favour two triggers getting fired on a single update. Amit, do you consider option 2 as a valid option ? That is, fire only UPDATE triggers. BR on source partition, and AR on destination partition. Do you agree that firing BR update trigger is essential since it can modify the row and even prevent the update from happening ? Also, since a user does not have a provision to install a common UPDATE row trigger, (s)he installs it on each of the leaf partitions. And then when an update causes row movement, using option 3 would end up not firing update triggers on any of the partitions. So, I prefer option 2 over option 3 , i.e. make sure to fire BR and AR update triggers. Actually option 2 is what Robert had proposed in the beginning. -- Thanks, -Amit Khandekar EnterpriseDB Corporation The Postgres Database 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