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

Started by Zsolt Parragi3 months ago2 messageshackers
Jump to latest
#1Zsolt Parragi
zsolt.parragi@percona.com

Hello!

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.

Let's say we have a table that silently discards backdated records:

CREATE TABLE emp (
id int,
valid_at daterange,
salary int,
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
INSERT INTO emp VALUES (1, '[2015-01-01,infinity)', 1000);

CREATE FUNCTION no_backdated() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF upper(NEW.valid_at) <= current_date THEN
RETURN NULL;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER no_backdated
BEFORE INSERT ON emp FOR EACH ROW EXECUTE FUNCTION no_backdated();

Then we perform an UPDATE on it:

UPDATE emp FOR PORTION OF valid_at FROM current_date TO 'infinity'
SET salary = 1100 WHERE id = 1 RETURNING *;

Which causes the past portion to be silently discarded.

I think this can be very confusing, as the user didn't execute any
insert statements directly.

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.

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)

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?

#2Paul A Jungwirth
pj@illuminatedcomputing.com
In reply to: Zsolt Parragi (#1)
Re: FOR PORTION OF: BEFORE INSERT triggers can silently drop leftover rows?

On Thu, Sep 3, 2026 at 7:10 AM Zsolt Parragi <zsolt.parragi@percona.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@illuminatedcomputing.com

Attachments:

t253663_2
v1-0001-Document-trigger-behavior-for-temporal-leftovers.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Document-trigger-behavior-for-temporal-leftovers.patchDownload+6-2