# Test plan: UPDATE/DELETE FOR PORTION OF A comprehensive test plan for the PG19 `UPDATE/DELETE ... FOR PORTION OF` feature (commit `8e72d914c52` and follow-ups). The goal is to enumerate *everything worth testing* — core behavior, feature combinations, concurrency, replication, dump/restore, pg_upgrade, and edge cases — and to mark what already has coverage. ## Legend - `[x]` — covered by an existing test (file reference given) - `[~]` — partially covered / covered only incidentally; worth strengthening - `[ ]` — not covered; candidate for a new test - **N/A** — reasoned out to be not applicable (rationale given), listed so we don't wonder about it later Primary existing test artifacts: - `src/test/regress/sql/for_portion_of.sql` — the main functional suite (~1680 lines) - `src/test/regress/sql/without_overlaps.sql` — temporal PK/UNIQUE/FK, some FPO - `src/test/regress/sql/updatable_views.sql` — FPO through views - `src/test/regress/sql/privileges.sql` — column privileges for the range column - `src/test/isolation/specs/for-portion-of.spec` — concurrency - `src/test/isolation/specs/fk-snapshot-3.spec` — temporal FK (NO ACTION) enforcement under concurrency (parent DELETE/UPDATE vs. child INSERT, RC/RR/SERIALIZABLE, crosscheck snapshot) - `src/test/isolation/specs/temporal-range-integrity.spec` — **not** temporal-feature-specific; it's the classic SSI write-skew demo with manual trigger-style range checks (no `WITHOUT OVERLAPS`, no `PERIOD` FK). Listed only so it isn't mistaken for temporal-FK concurrency coverage. - `src/test/subscription/t/034_temporal.pl` — logical replication --- ## 1. Core semantics - [x] Basic `UPDATE FOR PORTION OF` truncates the target row and inserts leftovers — `for_portion_of.sql` - [x] Basic `DELETE FOR PORTION OF` — `for_portion_of.sql` - [x] Works on non-PK range columns (no UNIQUE/PK required) — `for_portion_of.sql:5` - [x] Table alias with `AS` and without `AS` — `for_portion_of.sql:23,32` - [x] `UPDATE ... FROM` and `DELETE ... USING` join forms — `for_portion_of.sql:41,49` - [x] More than one range/period column on the table — `for_portion_of.sql:58` - [x] NULLs allowed in the key/range columns when no PK/UNIQUE — `for_portion_of.sql:87` - [x] `UPDATE`/`DELETE` with no `WHERE` clause — `for_portion_of.sql` - [x] Setting the non-range part of the PK during FPO — `for_portion_of.sql:280` - [x] Row count reported reflects only targeted rows, not leftover inserts — verify in RETURNING/trigger sections ### Portion vs. target overlap matrix (all combinations) - [x] finite/open target vs finite/open portion (all four open/finite corners) — `for_portion_of.sql:178-206` - [x] Exact fit — `for_portion_of.sql:206` - [x] Enclosed span (one leftover on each side) — `for_portion_of.sql:213,220` - [x] Enclosed span with separate protruding spans — `for_portion_of.sql:227` - [x] Multiple enclosed spans in one statement — `for_portion_of.sql:234` - [x] Equal bounds → no-op — `for_portion_of.sql:172` - [x] `empty` target → no-op — `for_portion_of.sql:273` --- ## 2. Bounds / target syntax & expressions - [x] `FROM x TO y` form — throughout - [x] Direct range target `FOR PORTION OF col (rangeval)` — `for_portion_of.sql:241` - [x] String literal coerced to range target — `for_portion_of.sql:248` - [x] Multirange direct target — multirange section - [x] Expression bounds (range intersection `*`) — `for_portion_of.sql:427` - [x] `current_date` (stable) accepted — `for_portion_of.sql:352` - [x] Shift/reduce grammar corner with `tsrange` + `INTERVAL` — `for_portion_of.sql:295-331` - [x] Bound supplied via query parameter ($n) — exercised via PL/pgSQL fn `for_portion_of.sql:397` - [x] `PREPARE`/`EXECUTE` with parameterized bounds, forcing a **generic plan** (const-folding / volatility check hold across custom→generic plan switch) — `for_portion_of.sql` ### Rejections (negative tests) - [x] Missing/unknown range column — `for_portion_of.sql:130` - [x] Directly `SET`ting the range column in the same UPDATE — `for_portion_of.sql:136` (code: `analyze.c:2990`) - [x] Wrong start/end scalar type — `for_portion_of.sql:142,148` - [x] Reversed bounds (start > end) — `for_portion_of.sql:154` - [x] Subquery in bound — `for_portion_of.sql:160` (code: `parse_expr.c:1893`) - [x] Column reference in bound — `for_portion_of.sql:166` (code: `parse_expr.c:592`) - [x] Volatile function (`clock_timestamp()`) in bound — `for_portion_of.sql:365` (code: `planner.c:1114`) - [x] Direct target of wrong range subtype / non-range / NULL — `for_portion_of.sql:255-267` - [x] Aggregate in bound (code path `parse_agg.c:589`) — `for_portion_of.sql` - [x] Window function in bound (code path `parse_agg.c:1046`) — `for_portion_of.sql` - [x] Set-returning function in bound (code path `parse_func.c:2794`) — `for_portion_of.sql` --- ## 3. Data types on the range/target column - [x] `daterange`, `tsrange`, `int4range` — throughout - [x] Multirange columns (FROM/TO, multirange literal, coercion, wrong type, NULL, empty) — multirange section - [x] Custom range type — "Test with a custom range type" - [x] Non-range GiST-able scalar column *with a direct target* (WITHOUT OVERLAPS on scalar) — non-range section - [x] Non-range column with overlaps — non-range section - [x] Domain over a range type (UPDATE/DELETE, target may violate domain, leftover must satisfy) — domain section - [x] Domain over a multirange type — domain section - [x] CHECK constraints re-checked on leftover inserts — domain/CHECK section - [x] Toasted / large out-of-line values preserved correctly in leftover rows — `for_portion_of.sql` - [x] Non-default collation on a non-range column preserved in leftovers — `for_portion_of.sql` --- ## 4. Generated columns, identity, defaults - [x] `GENERATED ... STORED` range column — `for_portion_of.sql` (STORED section) - [x] `GENERATED ... VIRTUAL` range column — `for_portion_of.sql` (VIRTUAL section) - [x] Generated column recomputed when the range narrows (leftover-before / updated / leftover-after) — generated-columns section - [x] Generated column depending on both a SET column and the range column — generated-columns section - [x] Generated column reached through an updatable view (check deferred to planner) — generated-columns section - [x] Generated column rejected in the *bound* expression (code `planner.c:873`) — generated section - [x] **`GENERATED ... AS IDENTITY` column**: leftovers preserve the original identity value and do NOT draw a new one — `for_portion_of.sql` - [x] `DEFAULT nextval(...)` column: leftovers copy the original value, default does not re-fire (sequence `last_value` unchanged) — `for_portion_of.sql` - [~] Volatile `DEFAULT`: covered indirectly by the `nextval` detector above (a leftover re-firing any default would advance the sequence); a dedicated `random()`/`now()` case is not separately observable and was skipped --- ## 5. Triggers - [x] Statement-level triggers (BEFORE/AFTER) on UPDATE/DELETE and the implied INSERT — trigger section - [x] Row-level triggers on updated/deleted row and on inserted leftovers — trigger section - [x] Transition tables with custom names — trigger section - [x] Deferred (CONSTRAINT) AFTER ROW triggers — trigger section - [x] Recursive FPO issued *from within* a trigger fired by FPO (UPDATE↔DELETE, all 4 combos) — trigger section - [x] BEFORE INSERT ROW trigger that modifies the tuple acts consistently on both leftovers — "cross-leftover pollution" test (fix `993a7aa0e4a`) - [x] `UPDATE OF colname` row trigger fires when colname is the range column — `fpo_update_of_trigger` - [x] `tg_temporal` / FPO info exposed to PL/pgSQL triggers — (commits `34ce52fb567`, `2eee9523238`); confirm assertion in trigger section, else `[~]` - [x] INSTEAD OF triggers on views — **resolved by forbidding the combination**: FOR PORTION OF on a view with INSTEAD OF triggers now errors ("views with INSTEAD OF triggers do not support FOR PORTION OF") instead of crashing. Check is in the rewriter. Covers the former "skip leftovers after INSTEAD OF" and "INSTEAD OF targeting too many rows" open items — `updatable_views.sql` (commit `5b5e99047ab`) - [ ] BEFORE UPDATE trigger that changes the range column must be **forbidden** — **open item** (`ab5719ab432` is WIP, not committed) - [ ] Triggers written in a non-plpgsql PL (pltcl/plperl) — optional, low value --- ## 6. Views, rules, inheritance, partitioning - [x] Simple updatable view — `updatable_views.sql:1892` - [x] Non-updatable view rejected (`delete from view ... using FOR PORTION OF`, code `rewriteHandler.c:3518`) — `updatable_views.sql:1912` - [x] FPO with non-updatable view columns (fix `7ca8c942967`) — `for_portion_of.sql` - [x] RULEs rewriting FPO statements (fix `33db6c4baf1`) — RULE section - [x] Single-table inheritance: leftovers stay in the child, no leak to parent — inheritance section - [x] Multiple inheritance with differing range-column attnums — inheritance section - [x] Inheritance children fix (`7d13b03a2e6`) — inheritance section - [x] Partitioned table: update without moving, within partitions of differing attnum layouts — partition section - [x] Partition **tuple routing**: row moves between partitions, each with leftovers — partition section - [x] Update all partitions at once, each producing leftovers — partition section - [x] Leftover routes to no partition (table partitioned by `lower(valid_at)`) → error, statement rolls back — `for_portion_of.sql` - [x] FPO on a partition **directly** (not via the root) — `for_portion_of.sql` --- ## 7. Constraints & keys - [x] Temporal PK / UNIQUE (`WITHOUT OVERLAPS`) present while doing FPO — `without_overlaps.sql:666,717` - [x] Leftover inserts must not violate the WITHOUT OVERLAPS exclusion — implied by above - [~] **Temporal foreign keys**: FPO on the *referenced* table is exercised in `without_overlaps.sql`, but… - [ ] Temporal FK `ON DELETE/UPDATE CASCADE | SET NULL | SET DEFAULT` triggered by FPO — **not committed** (commitfest 5836 / commit `9b4322d7cff` is WIP); test once landed - [x] Temporal FK `NO ACTION` (the only committed action; not `RESTRICT`), non-concurrent: DELETE-FPO leaving a dangling reference errors; UPDATE-FPO preserves coverage via leftovers and succeeds — `for_portion_of.sql` - [x] FPO on the *referencing* table: UPDATE/DELETE-FPO only narrow the child's validity, so leftovers always still satisfy the FK — `for_portion_of.sql` - [x] `NOT NULL` non-range column copied into leftovers (not nulled) — `for_portion_of.sql` --- ## 8. Statement combinations & PL - [x] Inside a CTE (`WITH ... UPDATE FOR PORTION OF ... RETURNING`): visibility semantics — `for_portion_of.sql:333` - [x] CTE visibility: leftovers/old tuples not visible to the outer query — `for_portion_of.sql:376` - [x] `UPDATE ... RETURNING` returns only updated values, not leftover inserts — RETURNING section - [x] `RETURNING` supports `NEW`/`OLD` on the range column — RETURNING section - [x] `DELETE ... RETURNING` returns deleted values — RETURNING section - [x] Inside PL/pgSQL function — `for_portion_of.sql:397` - [x] Inside a new-style (compiled) SQL function; interaction with plan-time generated-column check — SQL-function section - [x] Cursors: `UPDATE/DELETE FOR PORTION OF` rejected with a cursor / `WHERE CURRENT OF` — CURSOR section (fix `bc3ae886a75`) - [x] `MERGE`: `FOR PORTION OF` in a MERGE action is a syntax error — `for_portion_of.sql` - [ ] `COPY` — **N/A** (FOR PORTION OF is not a COPY concept) - [x] Savepoints / subtransaction rollback: `ROLLBACK TO SAVEPOINT` undoes an FPO and its leftovers — `for_portion_of.sql` --- ## 9. Privileges & RLS - [x] Column-level privileges on the range column used by FPO — `privileges.sql:786` - [x] `UPDATE` permission required on the FPO column (fix `7ac030d5b15`) — reflected in privileges test - [ ] **Row-Level Security / POLICY**: leftover inserts must pass the table's `INSERT`/`WITH CHECK` policies; the truncated update must pass `UPDATE`/`USING` — **not tested; this is an open item** ("Fix RLS checks for UPDATE/DELETE FOR PORTION OF leftover rows", Chao Li 2026-06-30) - [x] RLS `USING` visibility interacting with which rows FPO can see/split — FPO only splits rows visible under the policy; hidden rows are untouched (tested as a non-superuser role) — `for_portion_of.sql` --- ## 10. Foreign tables / FDW - [x] FPO directly on a foreign table rejected (`execMain.c:1134`, "foreign tables don't support FOR PORTION OF") — `contrib/postgres_fdw/sql/postgres_fdw.sql:1579,1586` (UPDATE and DELETE on `ft8`) - [x] Reject child-partition FDW in FPO, even when the root is local (commit `a40fdf65886`, check in `CheckValidResultRel`) — `contrib/postgres_fdw/sql/postgres_fdw.sql:1590-1610` (UPDATE and DELETE error on the foreign child) - [x] FPO on a pruned/local partition under such a root succeeds — `contrib/postgres_fdw/sql/postgres_fdw.sql:1608,1610` --- ## 11. Concurrency (isolation tests) Existing spec `for-portion-of.spec` is thorough: - [x] READ COMMITTED, UPDATE+UPDATE: s2 history non-overlapping / contained / partly-contained vs s1 — spec permutations - [x] READ COMMITTED anomaly demonstrated (leftovers added too late for the other txn) — spec header + permutations - [x] READ COMMITTED workaround via `SELECT ... FOR UPDATE` locking — spec - [x] REPEATABLE READ: anomaly disappears; concurrent update/delete failures raised as expected — spec - [x] SERIALIZABLE: a couple of the problematic permutations — spec - [x] EvalPlanQual reload behavior around leftovers — spec - [x] UPDATE+DELETE and DELETE+DELETE mixes — verify all mixes present in spec, else `[~]` - [x] **Temporal FK (NO ACTION) enforcement under concurrency** — parent DELETE / parent UPDATE narrowing `valid_at` vs. a concurrent child INSERT, across RC / RR / SERIALIZABLE, exercising the C crosscheck-snapshot logic — `fk-snapshot-3.spec`. (NO ACTION is the only committed temporal FK action; RESTRICT is not committed.) - [x] **Same scenarios with the parent change done via `FOR PORTION OF`** — added to `fk-snapshot-3.spec` (DELETE-FPO trim vs. carve, RC/RR/SER). Documents that DELETE-FPO differs from a plain narrowing UPDATE under READ COMMITTED: the leftover is not seen by the child's RI recheck (known RC FPO anomaly). Original note: `fk-snapshot-3.spec` uses plain UPDATE/DELETE on the parent, so the leftover-reinsert path is never seen by the FK crosscheck. Worth adding: parent `UPDATE/DELETE FOR PORTION OF` that carves a hole in (or trims) a referenced row's coverage while a child INSERT depending on that coverage runs concurrently — the FK must still reject a dangling reference. RC / RR / SERIALIZABLE. - [ ] **FPO vs. temporal FK referential actions (CASCADE / SET NULL / SET DEFAULT)** — deferred until that work lands (commitfest 5836 / WIP `9b4322d7cff`). - [x] Deadlock behavior with 3+ sessions using the lock-first workaround: a cyclic lock order gives a 3-way deadlock that PostgreSQL detects and rolls one session back (the other two finish their FPO updates); a consistent lock order serializes with no deadlock — `for-portion-of-deadlock.spec` --- ## 12. Logical replication / decoding - [x] REPLICA IDENTITY DEFAULT: UPDATE/DELETE FPO with no-key/PK/unique — `034_temporal.pl` - [x] REPLICA IDENTITY FULL: same matrix — `034_temporal.pl` - [x] Temporal GiST PK/UNIQUE (WITHOUT OVERLAPS) usable as REPLICA IDENTITY — `034_temporal.pl` header - [x] "can't UPDATE/DELETE FOR PORTION OF ... DEFAULT" (no replica identity) error — `034_temporal.pl` - [x] `test_decoding` output: FPO UPDATE decodes as UPDATE (old-key → narrowed new-tuple) + INSERT per leftover; FPO DELETE decodes as DELETE + INSERT per leftover — `contrib/test_decoding/sql/ddl.sql:96-119`, `contrib/test_decoding/expected/ddl.out:211-243` - [x] Row filters / column lists on a publication interacting with leftover inserts — a column list keeps excluded columns unreplicated across the truncated row and leftovers; a row filter on `valid_at` drops a leftover that falls outside it — `034_temporal.pl` --- ## 13. Physical replication, recovery, WAL - [~] WAL replay of FPO — **generic**: leftovers are ordinary heap INSERTs and the truncation is an ordinary heap UPDATE, replayed by existing heap redo. No FPO-specific redo code. - [ ] Optional smoke test: streaming standby / crash-recovery round-trip of a table after FPO, asserting standby matches primary (low incremental value, but cheap insurance) --- ## 14. Dump/restore & pg_upgrade Reasoned scope note: `FOR PORTION OF` is **DML**, so it is never emitted by `pg_dump`; there is nothing FPO-specific to dump. What matters is that the *schema* the feature relies on and the *data* it leaves behind round-trip: - [x] Temporal schema (WITHOUT OVERLAPS PK/UNIQUE, temporal FKs, PERIOD) dump/restore — covered by `without_overlaps.sql` object definitions + standard `pg_dump`/`002_pg_dump.pl` regression of the regression DB - [x] `pg_upgrade` of a database containing temporal tables — covered by the standard `pg_upgrade` test that upgrades the regression database (which now includes `for_portion_of` objects) - [ ] Explicit round-trip check: run FPO to produce leftovers, `pg_dump` + reload, assert row set identical (guards against any surprise like generated/identity columns re-firing on reload — ties to §4) - **N/A** — binary/cross-version upgrade of an in-flight FPO statement (DML is not persisted) --- ## 15. Diagnostics, planning, performance - [x] `EXPLAIN (COSTS OFF)` of an FPO UPDATE/DELETE — stable plan shape — `for_portion_of.sql` - [x] `EXPLAIN (ANALYZE, TIMING OFF, ...)` executes the FPO (stable actual-row counts) — `for_portion_of.sql` - [x] `pg_stat_statements` jumbling of FPO: two statements differing only in bounds normalize to one entry (bounds → `$1/$2`) — `contrib/pg_stat_statements/sql/dml.sql`. (`auto_explain` not separately tested.) - [ ] Large-scale correctness: FPO touching many rows each producing two leftovers (bulk behavior, memory) — smoke/perf, not a regress test --- ## 16. Miscellaneous edge cases - [x] `ALTER TABLE ... DROP COLUMN` then FPO (dropped-attnum handling) — `for_portion_of.sql` - [x] Dropped-and-re-added column creating an attnum gap, then FPO (leftovers preserve the re-added column) — `for_portion_of.sql` - [x] `PREPARE TRANSACTION` (2PC) wrapping an FPO statement: the truncation and leftovers survive a server restart while only prepared, then apply atomically at `COMMIT PREPARED` (and vanish at `ROLLBACK PREPARED`) — `src/test/recovery/t/055_for_portion_of_2pc.pl` - [x] Error thrown by a leftover insert rolls back the whole statement atomically (target row not left truncated) — `for_portion_of.sql` - [x] Empty result (WHERE matches nothing) — no change, no leftovers — `for_portion_of.sql` - [x] Target fully covering the row (whole-row update, zero leftovers) and fully outside (no-op) — `for_portion_of.sql` --- ## Summary ### Newly added tests (this branch) - `for_portion_of.sql`: aggregate/window/SRF-in-bounds rejections; `EXPLAIN` plan shape; `PREPARE`/`EXECUTE` generic plan; identity/`nextval`-default preservation in leftovers; toasted-value preservation; `MERGE` rejection; savepoint rollback; atomic rollback on a failed leftover insert; degenerate targets (no-match / outside / covering); `DROP COLUMN` and dropped-then-readded column; FPO directly on a partition; temporal FK (NO ACTION) non-concurrent. - `fk-snapshot-3.spec`: temporal FK (NO ACTION) vs. a parent change via `DELETE FOR PORTION OF`, RC/RR/SER. Documents that DELETE-FPO differs from a plain narrowing UPDATE under READ COMMITTED (the leftover is invisible to the child's RI recheck — the known RC FPO anomaly). - `contrib/pg_stat_statements/sql/dml.sql`: FPO bound-constant normalization (jumbling). ### Deliberately NOT tested (out of scope per request) - **RLS / policies on leftover inserts** — open item (has its own tests). - **Temporal FK referential actions** (CASCADE/SET NULL/SET DEFAULT) — future feature work (commitfest 5836). - **BEFORE trigger changing the range column** — open item. (**INSTEAD OF triggers on views** is no longer open: resolved by forbidding the combination and tested in `updatable_views.sql`, commit `5b5e99047ab` — see §5/§6.) ### Remaining items — deliberately not tested, with rationale - **`auto_explain`** (§15) — separate module; `pg_stat_statements` jumbling already covers the query-jumble path. - **Physical-replication / crash-recovery smoke** (§13) and a dedicated **`pg_dump` round-trip** of FPO output (§14) — both largely generic (leftovers are ordinary heap tuples with generic redo); the identity/default re-firing risk they guarded is now covered by the §4 regress test.