Re: BUG #18238: Cross-partitition MERGE/UPDATE with delete-preventing trigger leads to incorrect memory access

On Tue, 12 Dec 2023 at 09:50, Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Mon, Dec 11, 2023 at 8:53 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>>
>> So I think we need something like the attached.
>
> I think this is the right way to go.  +1.
>

Thanks for looking!

> BTW, while testing this patch, I encountered some confusion regarding
> cross-partition update.  As we know, cross-partition update works by
> first deleting the old tuple from the current partition.  So if we have
> BEFORE ROW DELETE triggers that suppress the delete, the update would be
> suppressed.  For in-partition update, there is no such problem.
>
> Does this match the expected behavior?
>

Yes, that's the intended behaviour. I think it's probably sufficiently
well-covered by the docs here:

https://www.postgresql.org/docs/current/trigger-definition.html#:~:text=If%20an%20UPDATE,the%20destination%20partition.

  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 an 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.

and then a couple of paragraphs further down, it mentions how a
row-level BEFORE trigger can return NULL to cause an operation to be
skipped.

So a BEFORE UPDATE trigger can block any kind of update, including a
cross-partition update, whereas a BEFORE DELETE trigger can prevent
rows changing partitions, while allowing other kinds of updates. That
might be quite handy under some circumstances, but it would also block
deletes, so it may not be ideal for all cases.

Anyway, that's what it's supposed to do.

Regards,
Dean