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

On Tue, Dec 12, 2023 at 9:16 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
wrote:

> On Tue, 12 Dec 2023 at 09:50, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > 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.

Ah, I see. Thanks for the detailed explanation!

Thanks
Richard