FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column

From: SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: FOR PORTION OF does not recompute GENERATED STORED columns that depend on the range column
Date: 2026-04-07 08:42:42
Message-ID: CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

It appears that this is a bug where UPDATE FOR ... PORTION OF fails to
recompute GENERATED ALWAYS AS ... STORED columns whose expressions
reference the range column being narrowed. Please find the repro below.

postgres=# CREATE TABLE t (id int, valid_at int4range NOT NULL, val int,
range_len int GENERATED ALWAYS AS (upper(valid_at) - lower(valid_at))
STORED);
INSERT INTO t VALUES (1, '[1,100)', 10);
UPDATE t FOR PORTION OF valid_at FROM 30 TO 70 SET val = 99;
SELECT *, upper(valid_at) - lower(valid_at) AS expected FROM t ORDER BY
valid_at;
CREATE TABLE
INSERT 0 1
UPDATE 1
id | valid_at | val | range_len | expected
----+----------+-----+-----------+----------
1 | [1,30) | 10 | 29 | 29
1 | [30,70) | 99 | 99 | 40
1 | [70,100) | 10 | 30 | 30
(3 rows)

The updated row [30,70) retains the stale range_len = 99 from the original
[1,100) range. The leftover inserts are correct because CMD_INSERT
unconditionally recomputes all generated columns. Virtual generated columns
are not affected and are computed correctly because they're evaluated at
read time from the actual stored valid_at value.

Further looking at the code it appears, In transformForPortionOfClause(),
the range column is intentionally not added to perminfo->updatedCols. Since
the range column is absent from updatedCols, any generated stored column
whose expression depends solely on the range column (e.g., upper(valid_at)
- lower(valid_at)) is skipped. Therefore, its expression is never prepared
and never recomputed during the FPO update.

Attached a draft patch that has the test scenario and a fix to address this
issue. In ExecInitGenerated, after retrieving updatedCols, the patch
additionally checks whether the owning ModifyTableState contains an FPO
clause. If it does, the attribute number (attno) of the range column is
added to updatedCols.

Thanks,
Satya

Attachment Content-Type Size
v1-0001-fpo-generated-stored-fix.patch application/octet-stream 5.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Antonin Houska 2026-04-07 08:57:46 Re: Adding REPACK [concurrently]
Previous Message Srinath Reddy Sadipiralla 2026-04-07 08:42:22 Re: Adding REPACK [concurrently]