Re: FOR PORTION OF: BEFORE INSERT triggers can silently drop leftover rows?

On Thu, Sep 3, 2026 at 7:10 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Currently a before insert trigger returning NULL can drop FOR PORTION
> OF leftovers. This seems unintuitive to me, and the original thread
> only discussed trigger firing order, not whether it should allow
> dropping the leftovers.

I think this behavior is correct. If you return NULL from an INSERT
trigger, we should skip the insert. The leftovers are supposed to
execute as separate insert statements, so they should have the same
behavior as regular statements. But I agree we ought to document it to
avoid confusion. Here is a patch doing that.

> Shouldn't this scenario either result in an error, or be at least very
> clearly documented, or print some diagnostics? I first considered
> proposing a patch that errors out for this scenario, but I am not sure
> if that's the proper way to handle this.

I don't think it should be an error or print a warning.

> While looking into this I also found out that there's already a
> precedent for this in the code, a cross partition UPDATE similarly
> fires a before insert trigger, but with an important difference: in
> that case, if the insert drops the row the UPDATE reports 0 rows,
> while FOR PORTION OF always reports 1 rows, regardless if the
> leftovers gets inserted or not. (UPDATE is also questionable, as it
> deletes 1 row in that case, I am not saying that it's better, it's
> just different)

The command tag refers to the rows updated/deleted by the primary
statement, not the temporal leftovers. In the case of a
cross-partition update canceled by an insert trigger, returning 0 is
appropriate, since we're talking about the row actually being updated.

If the wire protocol had a way to add extra command tag information, I
wouldn't mind including a supplemental number for how many leftovers
were inserted. (This would also give us a way for ON CONFLICT DO
UPDATE to distinguish between inserted & updated rows.) But changing
the existing number creates ambiguity about what it means.

> Perhaps a better question for 20 and later is: shouldn't the trigger
> be able to check if this is a leftover row, or if it's a
> partition-moving update?

I agree that would be very useful. It has come up in a few other
conversations from people testing this feature. Here is a patch for
it: https://commitfest.postgresql.org/patch/7239/ If that doesn't meet
your needs, please let me know.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com