Re: UPDATE of partition key

Hi Amit,

On 2017/04/04 20:11, Amit Khandekar wrote:
> On 3 April 2017 at 17:13, Amit Langote wrote:
>>>> On 31 March 2017 at 14:04, Amit Langote wrote:
>> How about something like:
>> For an <command>UPDATE</> that causes a row to move from one partition to
>> another due the partition key being updated, the following caveats exist:
>> <a brief description of the possibility of surprising results in the
>> presence of concurrent manipulation of the row in question>
>
> Now with the slightly changed doc structuring for partitioning in
> latest master, I have described in the end of section "5.10.2.
> Declarative Partitioning" this note :
>
> ---
>
> "Updating the partition key of a row might cause it to be moved into a
> different partition where this row satisfies its partition
> constraint."
>
> ---
>
> And then in the Limitations section, I have replaced the earlier
> can't-update-partition-key limitation with this new limitation as
> below :
>
> "When an UPDATE causes a row to move from one partition to another,
> there is a chance that another concurrent UPDATE or DELETE misses this
> row. Suppose, during the row movement, the row is still visible for
> the concurrent session, and it is about to do an UPDATE or DELETE
> operation on the same row. This DML operation can silently miss this
> row if the row now gets deleted from the partition by the first
> session as part of its UPDATE row movement. In such case, the
> concurrent UPDATE/DELETE, being unaware of the row movement,
> interprets that the row has just been deleted so there is nothing to
> be done for this row. Whereas, in the usual case where the table is
> not partitioned, or where there is no row movement, the second session
> would have identified the newly updated row and carried UPDATE/DELETE
> on this new row version."
>
> ---

OK.

> Further, in the Notes section of update.sgml, I have kept a link to
> the above limitations section like this :
>
> "In the case of a partitioned table, updating a row might cause it to
> no longer satisfy the partition constraint of the containing
> partition. In that case, if there is some other partition in the
> partition tree for which this row satisfies its partition constraint,
> then the row is moved to that partition. If there isn't such a
> partition, an error will occur. The error will also occur when
> updating a partition directly. Behind the scenes, the row movement is
> actually a DELETE and INSERT operation. However, there is a
> possibility that a concurrent UPDATE or DELETE on the same row may
> miss this row. For details see the section Section 5.10.2.3."

OK, too. It seems to me that the details in 5.10.2.3 provide more or less
the same information as "concurrent UPDATE or DELETE looking at the moved
row will miss this row", but maybe that's fine.

>> If an UPDATE on a partitioned table causes a row to move to another
>> partition, it will be performed as a DELETE from the original partition
>> followed by INSERT into the new partition. In this case, all row-level
>> BEFORE UPDATE triggers and all row-level BEFORE DELETE triggers are fired
>> on the original partition. Then all row-level BEFORE INSERT triggers are
>> fired on the destination partition. The possibility of surprising outcomes
>> should be considered when all these triggers affect the row being moved.
>> As far as AFTER ROW triggers are concerned, AFTER DELETE and AFTER INSERT
>> triggers are applied; but AFTER UPDATE triggers are not applied because
>> the UPDATE has been converted to a DELETE and INSERT. None of the DELETE
>> and INSERT statement-level triggers are fired, even if row movement
>> occurs; only the UPDATE triggers of the target table used in the UPDATE
>> statement will be fired.
>
> Yeah, most of the above makes sense to me. I have kept the phrase "as
> far as statement-level triggers are concerned".

OK, sure.

>> Finally, I forgot to mention during the last review that the new parameter
>> 'returning' to ExecDelete() could be called 'process_returning'.
>
> Done, thanks.
>
> Attached updated patch v7 has the above changes.

Marked as ready for committer.

Thanks,
Amit