| 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 |
| 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] |