FOR PORTION OF: BEFORE INSERT triggers can silently drop leftover rows?
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253663psql -h localhost -U postgresBuilt from patchset v2 (message #2), September 14, 2026 at 03:02 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253663_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253663_2 && git checkout t253663_2Patchset v2 (message #2) is on t253663_2
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?
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