UPDATE/DELETE FOR PORTION OF fire FOR EACH STATEMENT more than once

First seen: 2026-05-03 04:48:52+00:00 · Messages: 5 · Participants: 3

Latest Update

2026-05-06 · opus 4.7

Core Issue: Statement-Level Triggers and SQL:2011 Temporal FOR PORTION OF

Background

PostgreSQL's UPDATE ... FOR PORTION OF (and the DELETE variant) is part of the SQL:2011 temporal tables feature set, committed for PG18. Semantically, when you update a "portion" of a time-ranged row (e.g. update [1,10) over the portion [2,4)), the engine must:

  1. Delete or update the original row covering [1,10).
  2. Re-insert the leftover slices — [1,2) and [4,10) — so that only the requested portion is affected by the new value.

This slice-and-reinsert behavior is an implementation consequence of storing temporal data as range-keyed rows. The question raised by Jian is whether these synthetic inserts should fire statement-level triggers, and if so, how a user can disambiguate them from a "real" user-issued INSERT.

The Reported Behavior

With a BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH STATEMENT trigger on a temporal table, an UPDATE ... FOR PORTION OF fires the statement trigger four times: once for the UPDATE itself, and three additional times tagged as INSERT (the test case produces two leftover slices per affected row × two affected rows, roughly). This seems to contradict the documented guarantee that a FOR EACH STATEMENT trigger "only executes once for any given operation."

Design Rationale (Paul Jungwirth's Position)

Paul Jungwirth — the primary author of the temporal tables work and therefore the authoritative voice here — clarifies that this is intentional and standards-conformant:

This framing has an important architectural implication: the leftover inserts are first-class DML within the transaction, subject to RLS, constraints, FKs, and triggers — not invisible internal rewrites like HOT updates or partition tuple routing.

The Real Usability Problem

Thom Brown's probing exposes the practical wrinkle. He initially hypothesized that pg_trigger_depth() would distinguish the original statement (depth 0) from the synthetic inserts (depth 1), which would give user code a clean disambiguation hook. Testing showed all invocations run at depth 1 — meaning pg_trigger_depth() is not useful here. (The implication is that the whole FOR PORTION OF operation runs inside some implicit nesting level, so even the "original" UPDATE isn't seen at depth 0 from the trigger's perspective. This is itself a somewhat surprising observation about how the temporal machinery is wired into the executor.)

That leaves users with no clean way to ask the question "is this INSERT a user-issued INSERT, or is it a leftover-slice reinsertion from a FOR PORTION OF UPDATE/DELETE?" The only workaround — pattern-matching current_query() — is correctly described as kludgy and is fragile (whitespace, comments, wrapping functions all break it).

Proposed Direction: Exposing FOR PORTION OF Metadata to Triggers

Paul points to an in-flight patch series (commitfest entry 5836) with three components:

  1. Expose FOR PORTION OF parameters in the C TriggerData struct. This gives C-language triggers and internal code access to the temporal bounds driving the operation.
  2. Use that metadata internally to implement CASCADE / SET NULL / SET DEFAULT referential actions on temporal foreign keys. This is architecturally significant: temporal FKs need to cascade not just by key but by the overlapping portion of time, and that requires the referenced-side trigger to know which time range was affected. Without plumbing this through TriggerData, the RI trigger implementation would have to re-derive it, which is both duplicative and error-prone.
  3. Surface the same information in PL/pgSQL via new TG_* variables, giving SQL-level users a first-class way to read the portion bounds.

The open design question Paul raises at the end is the crux of this thread: should these TG_* variables also be populated for the synthetic INSERT triggers (the leftover-slice reinsertions), not just for the UPDATE/DELETE trigger that caused them?

Tradeoff Analysis

Arguments for populating on the INSERT side:

Arguments against:

Paul himself acknowledges it "seems a little strange, but it would be unambiguous." Given that he's the feature's author and the committer-track maintainer of this area, his leaning carries significant weight.

Architectural Takeaways

  1. The documentation's "fires once per statement" guarantee needs clarification. With temporal tables, a single user UPDATE decomposes into multiple SQL-standard statements. The docs under CREATE TRIGGER should probably cross-reference FOR PORTION OF semantics.

  2. pg_trigger_depth() is not a reliable "am I inside a synthetic operation?" probe for this feature. Whether that's a bug, a documentation gap, or intentional is worth revisiting — arguably the leftover inserts should run at depth+1 relative to the driving UPDATE, which would make the existing introspection function Just Work.

  3. TriggerData needs richer causal context as PostgreSQL accumulates more DML-rewriting features (temporal, MERGE, partition routing, logical replication apply). The patch at CF 5836 is a targeted solution for the temporal case, but the broader design question of how trigger context communicates "why am I running?" is becoming more pressing.

Status

No consensus yet on whether INSERT triggers should receive FOR PORTION OF metadata. The patch series (CF 5836) is uncommitted. The immediate user-facing confusion is resolved (this is intended behavior), but the usability gap Thom identified remains open.