Re: [HACKERS] UPDATE of partition key - Mailing list pgsql-hackers

On 6 June 2017 at 23:52, Robert Haas <robertmhaas@gmail.com> wrote:
> 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 after coding this logic, it looks a bit different. See
ExecUpdate() in the attached file  trigger_related_changes.patch

----

Now that we are making sure trigger won't change the partition of the
tuple, next thing we need to do is, make sure the tuple routing setup
is done *only* if the UPDATE is modifying partition keys. Otherwise,
this will degrade normal update performance.

Below is the logic I am implementing for determining whether the
UPDATE is modifying partition keys.

In ExecInitModifyTable() ...
Call GetUpdatedColumns(mtstate->rootResultRelInfo, estate) to get
updated_columns.
For each of the updated_columns :
{
    Check if the column is part of partition key quals of any of
    the relations in mtstate->resultRelInfo[] array.
    /*
     * mtstate->resultRelInfo[] contains exactly those leaf partitions
     * which qualify the update quals.
     */

    If (it is part of partition key quals of at least one of the relations)
    {
       Do ExecSetupPartitionTupleRouting() for the root partition.
       break;
    }
}

Few things need to be considered :

Use Relation->rd_partcheck to get partition check quals of each of the
relations in mtstate->resultRelInfo[].

The Relation->rd_partcheck of the leaf partitions would include the
ancestors' partition quals as well. So we are good: we don't have to
explicitly get the upper partition constraints. Note that an UPDATE
can modify a column which is not used in a partition constraint
expressions of any of the partitions or partitioned tables in the
subtree, but that column may have been used in partition constraint of
a partitioned table belonging to upper subtree.

All of the relations in mtstate->resultRelInfo are already open. So we
don't need to re-open any more relations to get the partition quals.

The column bitmap set returned by GetUpdatedColumns() refer to
attribute numbers w.r.t. to the root partition. And the
mstate->resultRelInfo[] have attnos w.r.t. to the leaf partitions. So
we need to do something similar to map_partition_varattnos() to change
the updated columns attnos to the leaf partitions and walk down the
partition constraint expressions to find if the attnos are present
there.


Thanks,
-Amit Khandekar
EnterpriseDB Corporation
The Postgres Database Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers