On Tue, 12 Dec 2023 at 09:50, Richard Guo <guofenglinux@gmail.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:
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.
