FOR PORTION OF Should Reject GENERATED Columns

Technical Problem

This thread addresses a validation gap in PostgreSQL's FOR PORTION OF functionality — a feature related to temporal/period-aware DML operations. The core issue is that FOR PORTION OF does not currently reject operations targeting GENERATED (computed) columns, which is semantically invalid and potentially dangerous.

Background: FOR PORTION OF

FOR PORTION OF is part of the SQL:2011 temporal extensions to PostgreSQL. It allows DML statements (UPDATE, DELETE) to operate on a portion of a temporal period associated with a row. For example:

UPDATE t FOR PORTION OF valid_period FROM '2025-01-01' TO '2025-06-01'
SET salary = 50000 WHERE emp_id = 1;

This causes the system to split rows at temporal boundaries — the original row may be split into up to three pieces: the portion before the specified range (unchanged), the portion within the range (modified), and the portion after the range (unchanged). This splitting necessarily involves writing new values for the period/range column endpoints.

The GENERATED Column Problem

GENERATED columns (both GENERATED ALWAYS AS (expr) STORED and GENERATED ALWAYS AS IDENTITY) have their values computed automatically by the system. Users cannot directly assign values to them via INSERT or UPDATE. The issue is that when FOR PORTION OF splits a row, it internally needs to write new period boundary values. If the period column (or columns composing the period) are GENERATED, this creates a contradiction:

1. Semantic violation: The system would need to override the generated expression to write the split boundary values, which violates the contract of GENERATED columns.
2. Logical inconsistency: A generated column's value is derived from other columns. Allowing FOR PORTION OF to manipulate it directly would break referential consistency between the generated column and its source expressions.
3. Potential data corruption: Without proper validation, the operation might silently produce incorrect results or fail in unexpected ways.

The fix should add an explicit check during query analysis/rewriting that raises a clear error when FOR PORTION OF targets a period defined over generated columns, similar to how direct UPDATE of generated columns is already rejected.

Proposed Solution

Based on the referenced conversation (linked in the message), Nathan Bossart posted a patch that adds validation to reject FOR PORTION OF when the temporal period involves GENERATED columns. The fix likely resides in the rewriter or parser phase, where the FOR PORTION OF clause is processed. The check would inspect the column attributes of the period's constituent columns and raise an error if any have attgenerated set.

This is a relatively straightforward defensive check, but it's important because:

• It prevents silent misbehavior at the boundary of two complex features (temporal DML and generated columns).
• It aligns PostgreSQL's behavior with the SQL standard expectation that period columns are mutable by the system's temporal splitting logic.
• It provides a clear, early error message rather than an obscure failure deeper in execution.

Architectural Significance

This issue sits at the intersection of several PostgreSQL subsystems:

1. Rewriter/Parser: Where FOR PORTION OF is desugared into the row-splitting logic.
2. Column constraints: The GENERATED column infrastructure that enforces computed-value semantics.
3. Temporal tables: The broader SQL:2011 temporal feature set being incrementally added to PostgreSQL.

The fix is small but represents an important principle: when new features are composed, the interaction space must be explicitly validated. The temporal DML features are relatively new, and generated columns are an independent feature — their intersection wasn't fully covered in initial implementation.

Thread Context

This thread was created specifically for commitfest tracking purposes. The substantive technical discussion occurred in a prior thread (referenced via the message-id link). Paul Jungwirth (pj@illuminatedcomputing.com) — the primary developer of PostgreSQL's temporal table features — created this separate thread to ensure proper tracking in the commitfest application, referencing Nathan Bossart's fix posted the previous day.

Status

The patch appears to be in the early review/commitfest stage. Given that it's a targeted validation fix rather than a feature change, it is likely a small, focused patch suitable for relatively quick review and commit.