Analysis: Bug in UPDATE/DELETE FOR PORTION OF with Inheritance Tables

Background: Temporal DML and FOR PORTION OF

PostgreSQL 18 (development tip at the time, referred to here as "19" by the reporter — likely a typo for v18 or referring to the next major after 18) introduced SQL:2011-style temporal DML via the FOR PORTION OF clause. The semantics are: when you UPDATE ... FOR PORTION OF valid_at FROM x TO y, any tuple whose valid_at range overlaps [x, y) is split — the portion overlapping [x, y) is updated (or deleted), and the non-overlapping "leftover" sub-ranges are re-inserted as new tuples preserving the original column values. This is the standard mechanism to maintain application-time period tables without forcing the user to manually split rows.

Implementation-wise, the executor synthesizes INSERTs for the leftover ranges during the UPDATE/DELETE. The machinery was designed primarily with partitioned tables in mind, where leftover tuples must be routed through the root partitioned relation so that tuple routing can place them into the correct partition (which may differ from the original, since the range key was modified).

The Core Bug

The reporter demonstrates two intertwined defects that manifest when FOR PORTION OF is applied to classical (non-partitioned) inheritance hierarchies:

Defect 1: Leftover tuples inserted into the wrong relation

In partitioned tables, inserting the leftover slice into the root is correct because partition routing will dispatch the tuple to the appropriate leaf. In plain inheritance, however, there is no routing — an INSERT into the parent p goes only into p, not into any child. The existing code unconditionally treats the target relation as if it were a routing root, so when the original row lives in child c, the leftover fragments are inserted into p instead of being returned to c. The result is the row "teleports" from child to parent, silently violating the invariant that leftover tuples should be indistinguishable from the original except for the range column.

The fix must distinguish: if the result relation is a partitioned-table root, route through it; otherwise (plain inheritance child), insert the leftovers directly back into the specific child relation that produced the original tuple.

Defect 2: attnum mismatch across inheritance

Classical inheritance permits a child to add its own columns (and historically permitted column order divergence). Therefore the attnum of the range column (valid_at) in the parent is not guaranteed to equal its attnum in any given child. The existing leftover-construction code appears to reuse the parent's attnum when building the modified tuple for the child, which will corrupt tuples when the child layout differs (e.g. extra inherited columns shifting positions, or multiple-inheritance merges reordering attributes).

The correct approach mirrors what the rest of the inheritance executor does: translate attribute numbers through the parent→child attribute map (the same machinery used for expand_inherited_rtentry / make_inh_translation_list / map_variable_attnos, and at execution time via TupleConversionMap or the per-child ResultRelInfo's translated attribute numbers).

Architectural Significance

This bug highlights a recurring pattern in PostgreSQL: features designed in the partitioning era sometimes forget that plain inheritance still exists and has subtly different semantics. Partitioning guarantees (a) disjoint leaf relations, (b) identical tuple descriptors modulo dropped columns handled by routing, and (c) a single logical "root" for INSERT routing. Inheritance guarantees none of these. Any executor code path that synthesizes tuples for a target relation must either:

1. Walk the per-child ResultRelInfo list and use each child's own descriptor/attnums, or
2. Explicitly route through tuple routing only when the target is partitioned (rd_rel->relkind == RELKIND_PARTITIONED_TABLE).

The FOR PORTION OF implementation apparently conflated "has children" with "is partitioned," which is the source of both defects.

The Proposed Patch

Per the report, the patch:

• Detects whether the result relation is a partitioned root vs. a plain-inheritance child, and dispatches leftover INSERTs accordingly — plain-inheritance leftovers go back to the actual source child relation rather than through the parent.
• Resolves the range column's attnum relative to the child relation that owns the tuple being split, rather than the parent's attnum, handling the multiple-inheritance / column-reorder cases.
• Adds regression tests covering both UPDATE and DELETE FOR PORTION OF over inheritance hierarchies.

Release Management Implication

Because temporal DML is a v18 feature (the reporter says "19" but the semantics — and the fact that they are adding it to Open Items — indicate this is targeting the in-development release's open-items list before GA), this is a correctness bug that must be fixed before release. Leftover tuples landing in the wrong relation is silent data corruption from the user's point of view: the row count is preserved but the storage location (and therefore visibility under ONLY, inheritance-based RLS, per-child triggers, per-child constraints, and logical replication publications) is wrong.

Open Questions a Reviewer Would Raise

1. Does the patch handle the case where the child has a dropped column between the parent's range attnum and the child's? The attnum map must account for that, not just additional trailing columns.
2. Should FOR PORTION OF on a plain-inheritance parent even be supported long-term, or should it (like some other temporal features) be restricted? If supported, BEFORE/AFTER row triggers and per-child CHECK constraints on the leftover INSERTs need to fire on the correct relation — the fix for Defect 1 is a prerequisite for correct trigger semantics.
3. Interaction with tableoid in RETURNING: leftover inserts previously going to p would have reported p's OID; the fix changes that to the child's OID, which is user-visible behavior.
4. Are there analogous bugs in MERGE's handling of FOR PORTION OF (if/when extended), or in logical decoding of the synthesized INSERTs?