MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 7 Apr 2026 01:42:42 -0700
Message-ID: 
 <CAHg+QDcd=t69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA=hM0g@mail.gmail.com>
Subject: FOR PORTION OF does not recompute GENERATED STORED columns that
 depend on the range column
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000ef8776064edac403"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDcd%3Dt69gLf9yQexO07EJ2mx0Z70NFHo6h94X1EDA%3DhM0g%40mail.gmail.com>
Precedence: bulk

--000000000000ef8776064edac403
Content-Type: multipart/alternative; boundary="000000000000ef8773064edac401"

--000000000000ef8773064edac401
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ef8773064edac401
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi hackers,<br><br>It appears that this is a bug where UPD=
ATE FOR ... PORTION OF fails to recompute GENERATED ALWAYS AS ... STORED co=
lumns whose expressions reference the range column being narrowed. Please f=
ind the repro below.<br><br>postgres=3D# CREATE TABLE t (id int, valid_at i=
nt4range NOT NULL, val int,<br>=C2=A0 =C2=A0 range_len int GENERATED ALWAYS=
 AS (upper(valid_at) - lower(valid_at)) STORED);<br>INSERT INTO t VALUES (1=
, &#39;[1,100)&#39;, 10);<br>UPDATE t FOR PORTION OF valid_at FROM 30 TO 70=
 SET val =3D 99;<br>SELECT *, upper(valid_at) - lower(valid_at) AS expected=
 FROM t ORDER BY valid_at;<br>CREATE TABLE<br>INSERT 0 1<br>UPDATE 1<br>=C2=
=A0id | valid_at | val | range_len | expected <br>----+----------+-----+---=
--------+----------<br>=C2=A0 1 | [1,30) =C2=A0 | =C2=A010 | =C2=A0 =C2=A0 =
=C2=A0 =C2=A029 | =C2=A0 =C2=A0 =C2=A0 29<br>=C2=A0 1 | [30,70) =C2=A0| =C2=
=A099 | =C2=A0 =C2=A0 =C2=A0 =C2=A099 | =C2=A0 =C2=A0 =C2=A0 40<br>=C2=A0 1=
 | [70,100) | =C2=A010 | =C2=A0 =C2=A0 =C2=A0 =C2=A030 | =C2=A0 =C2=A0 =C2=
=A0 30<br>(3 rows)<br><br><br>The updated row [30,70) retains the stale ran=
ge_len =3D 99 from the original [1,100) range. The leftover inserts are cor=
rect because CMD_INSERT unconditionally recomputes all generated columns. V=
irtual generated columns are not affected and are computed correctly becaus=
e they&#39;re evaluated at read time from the actual stored valid_at value.=
<br><br>Further looking at the code it appears, In transformForPortionOfCla=
use(), the range column is intentionally not added to perminfo-&gt;updatedC=
ols. Since the range column is absent from updatedCols, any generated store=
d column whose expression depends solely on the range column (e.g., upper(v=
alid_at) - lower(valid_at)) is skipped. Therefore, its expression is never =
prepared and never recomputed during the FPO update.<br><br>Attached a draf=
t patch that has the test scenario and a fix to address this issue. In Exec=
InitGenerated, 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.<div>=
<br></div><div>Thanks,</div><div>Satya</div></div>

--000000000000ef8773064edac401--

--000000000000ef8776064edac403
Content-Type: application/octet-stream;
	name="v1-0001-fpo-generated-stored-fix.patch"
Content-Disposition: attachment;
	filename="v1-0001-fpo-generated-stored-fix.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnodfdu50>
X-Attachment-Id: f_mnodfdu50

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVNb2RpZnlUYWJsZS5jIGIvc3Jj
L2JhY2tlbmQvZXhlY3V0b3Ivbm9kZU1vZGlmeVRhYmxlLmMKaW5kZXggZGZkN2IzM2EuLmMwMjUw
ZTY1IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlTW9kaWZ5VGFibGUuYwor
KysgYi9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlTW9kaWZ5VGFibGUuYwpAQCAtNDc1LDYgKzQ3
NSwzOCBAQCBFeGVjSW5pdEdlbmVyYXRlZChSZXN1bHRSZWxJbmZvICpyZXN1bHRSZWxJbmZvLAog
CWVsc2UKIAkJdXBkYXRlZENvbHMgPSBOVUxMOwogCisJLyoKKwkgKiBGb3IgVVBEQVRFIC4uLiBG
T1IgUE9SVElPTiBPRiwgdGhlIHJhbmdlIGNvbHVtbiBpcyBhbHNvIGJlaW5nIG1vZGlmaWVkCisJ
ICogKG5hcnJvd2VkIHZpYSBpbnRlcnNlY3Rpb24pLCBidXQgaXQgaXMgbm90IGluY2x1ZGVkIGlu
IHVwZGF0ZWRDb2xzCisJICogYmVjYXVzZSB0aGUgdXNlciBkb2VzIG5vdCBuZWVkIFVQREFURSBw
ZXJtaXNzaW9uIG9uIGl0LiAgV2UgbXVzdAorCSAqIGFjY291bnQgZm9yIGl0IGhlcmUgc28gdGhh
dCBnZW5lcmF0ZWQgY29sdW1ucyByZWZlcmVuY2luZyB0aGUgcmFuZ2UKKwkgKiBjb2x1bW4gYXJl
IHJlY29tcHV0ZWQuCisJICovCisJaWYgKHVwZGF0ZWRDb2xzKQorCXsKKwkJRm9yUG9ydGlvbk9m
U3RhdGUgKmZwb1N0YXRlID0gcmVzdWx0UmVsSW5mby0+cmlfZm9yUG9ydGlvbk9mOworCisJCWlm
IChmcG9TdGF0ZSA9PSBOVUxMICYmIHJlc3VsdFJlbEluZm8tPnJpX1Jvb3RSZXN1bHRSZWxJbmZv
KQorCQkJZnBvU3RhdGUgPSByZXN1bHRSZWxJbmZvLT5yaV9Sb290UmVzdWx0UmVsSW5mby0+cmlf
Zm9yUG9ydGlvbk9mOworCQlpZiAoZnBvU3RhdGUgIT0gTlVMTCkKKwkJeworCQkJaW50CQlyYW5n
ZUF0dG5vID0gZnBvU3RhdGUtPmZwX3JhbmdlQXR0bm87CisKKwkJCS8qIE1hcCBmcm9tIHJvb3Qg
YXR0bm8gdG8gY2hpbGQgYXR0bm8gaWYgbmVlZGVkICovCisJCQlpZiAocmVzdWx0UmVsSW5mby0+
cmlfUm9vdFJlc3VsdFJlbEluZm8pCisJCQl7CisJCQkJVHVwbGVDb252ZXJzaW9uTWFwICptYXAg
PSBFeGVjR2V0Um9vdFRvQ2hpbGRNYXAocmVzdWx0UmVsSW5mbywKKwkJCQkJCQkJCQkJCQkJCSAg
IGVzdGF0ZSk7CisKKwkJCQlpZiAobWFwKQorCQkJCQlyYW5nZUF0dG5vID0gbWFwLT5hdHRyTWFw
LT5hdHRudW1zW3JhbmdlQXR0bm8gLSAxXTsKKwkJCX0KKworCQkJdXBkYXRlZENvbHMgPSBibXNf
YWRkX21lbWJlcihibXNfY29weSh1cGRhdGVkQ29scyksCisJCQkJCQkJCQkJIHJhbmdlQXR0bm8g
LSBGaXJzdExvd0ludmFsaWRIZWFwQXR0cmlidXRlTnVtYmVyKTsKKwkJfQorCX0KKwogCS8qCiAJ
ICogTWFrZSBzdXJlIHRoZXNlIGRhdGEgc3RydWN0dXJlcyBhcmUgYnVpbHQgaW4gdGhlIHBlci1x
dWVyeSBtZW1vcnkKIAkgKiBjb250ZXh0IHNvIHRoZXknbGwgc3Vydml2ZSB0aHJvdWdob3V0IHRo
ZSBxdWVyeS4KZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvZm9yX3BvcnRp
b25fb2Yub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9mb3JfcG9ydGlvbl9vZi5vdXQK
aW5kZXggMzFmNzcyYzcuLmE5OTE3YjRhIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4
cGVjdGVkL2Zvcl9wb3J0aW9uX29mLm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVk
L2Zvcl9wb3J0aW9uX29mLm91dApAQCAtMjA5Nyw0ICsyMDk3LDYyIEBAIFNFTEVDVCAqIEZST00g
dGVtcG9yYWxfcGFydGl0aW9uZWRfNSBPUkRFUiBCWSBpZCwgdmFsaWRfYXQ7CiAoNCByb3dzKQog
CiBEUk9QIFRBQkxFIHRlbXBvcmFsX3BhcnRpdGlvbmVkOworLS0gVVBEQVRFIEZPUiBQT1JUSU9O
IE9GIHdpdGggZ2VuZXJhdGVkIHN0b3JlZCBjb2x1bW5zCistLSBUaGUgZ2VuZXJhdGVkIGNvbHVt
biBkZXBlbmRzIG9uIHRoZSByYW5nZSBjb2x1bW4sIHNvIGl0IG11c3QgYmUKKy0tIHJlY29tcHV0
ZWQgd2hlbiBGT1IgUE9SVElPTiBPRiBuYXJyb3dzIHRoZSByYW5nZS4KK0NSRUFURSBUQUJMRSBm
cG9fZ2VuZXJhdGVkX3N0b3JlZCAoCisgIGlkIGludCwKKyAgdmFsaWRfYXQgaW50NHJhbmdlLAor
ICByYW5nZV9sZW4gaW50IEdFTkVSQVRFRCBBTFdBWVMgQVMgKHVwcGVyKHZhbGlkX2F0KSAtIGxv
d2VyKHZhbGlkX2F0KSkgU1RPUkVECispOworSU5TRVJUIElOVE8gZnBvX2dlbmVyYXRlZF9zdG9y
ZWQgKGlkLCB2YWxpZF9hdCkgVkFMVUVTCisgICgxLCAnWzEwLDEwMCknKTsKK1NFTEVDVCAqIEZS
T00gZnBvX2dlbmVyYXRlZF9zdG9yZWQgT1JERVIgQlkgdmFsaWRfYXQ7CisgaWQgfCB2YWxpZF9h
dCB8IHJhbmdlX2xlbiAKKy0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLQorICAxIHwgWzEwLDEw
MCkgfCAgICAgICAgOTAKKygxIHJvdykKKworLS0gQWZ0ZXIgRlBPLCBhbGwgdGhyZWUgcm93cyAo
bGVmdG92ZXItYmVmb3JlLCB1cGRhdGVkLCBsZWZ0b3Zlci1hZnRlcikKKy0tIG11c3QgaGF2ZSBj
b3JyZWN0IHJhbmdlX2xlbiB2YWx1ZXMuCitVUERBVEUgZnBvX2dlbmVyYXRlZF9zdG9yZWQKKyAg
Rk9SIFBPUlRJT04gT0YgdmFsaWRfYXQgRlJPTSAzMCBUTyA3MAorICBTRVQgaWQgPSAyOworU0VM
RUNUICogRlJPTSBmcG9fZ2VuZXJhdGVkX3N0b3JlZCBPUkRFUiBCWSB2YWxpZF9hdDsKKyBpZCB8
IHZhbGlkX2F0IHwgcmFuZ2VfbGVuIAorLS0tLSstLS0tLS0tLS0tKy0tLS0tLS0tLS0tCisgIDEg
fCBbMTAsMzApICB8ICAgICAgICAyMAorICAyIHwgWzMwLDcwKSAgfCAgICAgICAgNDAKKyAgMSB8
IFs3MCwxMDApIHwgICAgICAgIDMwCisoMyByb3dzKQorCistLSBBbHNvIHRlc3Qgd2l0aCBhIGdl
bmVyYXRlZCBjb2x1bW4gdGhhdCByZWZlcmVuY2VzIGJvdGggYSBTRVQgY29sdW1uCistLSBhbmQg
dGhlIHJhbmdlIGNvbHVtbi4KK1RSVU5DQVRFIGZwb19nZW5lcmF0ZWRfc3RvcmVkOworRFJPUCBU
QUJMRSBmcG9fZ2VuZXJhdGVkX3N0b3JlZDsKK0NSRUFURSBUQUJMRSBmcG9fZ2VuZXJhdGVkX3N0
b3JlZCAoCisgIGlkIGludCwKKyAgdmFsaWRfYXQgaW50NHJhbmdlLAorICBpZF9wbHVzX2xlbiBp
bnQgR0VORVJBVEVEIEFMV0FZUyBBUyAoaWQgKyB1cHBlcih2YWxpZF9hdCkgLSBsb3dlcih2YWxp
ZF9hdCkpIFNUT1JFRAorKTsKK0lOU0VSVCBJTlRPIGZwb19nZW5lcmF0ZWRfc3RvcmVkIChpZCwg
dmFsaWRfYXQpIFZBTFVFUworICAoMSwgJ1sxMCwxMDApJyk7CitTRUxFQ1QgKiBGUk9NIGZwb19n
ZW5lcmF0ZWRfc3RvcmVkIE9SREVSIEJZIHZhbGlkX2F0OworIGlkIHwgdmFsaWRfYXQgfCBpZF9w
bHVzX2xlbiAKKy0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tCisgIDEgfCBbMTAsMTAwKSB8
ICAgICAgICAgIDkxCisoMSByb3cpCisKK1VQREFURSBmcG9fZ2VuZXJhdGVkX3N0b3JlZAorICBG
T1IgUE9SVElPTiBPRiB2YWxpZF9hdCBGUk9NIDMwIFRPIDcwCisgIFNFVCBpZCA9IDI7CitTRUxF
Q1QgKiBGUk9NIGZwb19nZW5lcmF0ZWRfc3RvcmVkIE9SREVSIEJZIHZhbGlkX2F0OworIGlkIHwg
dmFsaWRfYXQgfCBpZF9wbHVzX2xlbiAKKy0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tCisg
IDEgfCBbMTAsMzApICB8ICAgICAgICAgIDIxCisgIDIgfCBbMzAsNzApICB8ICAgICAgICAgIDQy
CisgIDEgfCBbNzAsMTAwKSB8ICAgICAgICAgIDMxCisoMyByb3dzKQorCitEUk9QIFRBQkxFIGZw
b19nZW5lcmF0ZWRfc3RvcmVkOwogUkVTRVQgZGF0ZXN0eWxlOwpkaWZmIC0tZ2l0IGEvc3JjL3Rl
c3QvcmVncmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yuc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
Zm9yX3BvcnRpb25fb2Yuc3FsCmluZGV4IGQ0MDYyYWNmLi5mMWUzOTM3YyAxMDA2NDQKLS0tIGEv
c3JjL3Rlc3QvcmVncmVzcy9zcWwvZm9yX3BvcnRpb25fb2Yuc3FsCisrKyBiL3NyYy90ZXN0L3Jl
Z3Jlc3Mvc3FsL2Zvcl9wb3J0aW9uX29mLnNxbApAQCAtMTM2NSw0ICsxMzY1LDQ0IEBAIFNFTEVD
VCAqIEZST00gdGVtcG9yYWxfcGFydGl0aW9uZWRfNSBPUkRFUiBCWSBpZCwgdmFsaWRfYXQ7CiAK
IERST1AgVEFCTEUgdGVtcG9yYWxfcGFydGl0aW9uZWQ7CiAKKy0tIFVQREFURSBGT1IgUE9SVElP
TiBPRiB3aXRoIGdlbmVyYXRlZCBzdG9yZWQgY29sdW1ucworLS0gVGhlIGdlbmVyYXRlZCBjb2x1
bW4gZGVwZW5kcyBvbiB0aGUgcmFuZ2UgY29sdW1uLCBzbyBpdCBtdXN0IGJlCistLSByZWNvbXB1
dGVkIHdoZW4gRk9SIFBPUlRJT04gT0YgbmFycm93cyB0aGUgcmFuZ2UuCisKK0NSRUFURSBUQUJM
RSBmcG9fZ2VuZXJhdGVkX3N0b3JlZCAoCisgIGlkIGludCwKKyAgdmFsaWRfYXQgaW50NHJhbmdl
LAorICByYW5nZV9sZW4gaW50IEdFTkVSQVRFRCBBTFdBWVMgQVMgKHVwcGVyKHZhbGlkX2F0KSAt
IGxvd2VyKHZhbGlkX2F0KSkgU1RPUkVECispOworSU5TRVJUIElOVE8gZnBvX2dlbmVyYXRlZF9z
dG9yZWQgKGlkLCB2YWxpZF9hdCkgVkFMVUVTCisgICgxLCAnWzEwLDEwMCknKTsKK1NFTEVDVCAq
IEZST00gZnBvX2dlbmVyYXRlZF9zdG9yZWQgT1JERVIgQlkgdmFsaWRfYXQ7CisKKy0tIEFmdGVy
IEZQTywgYWxsIHRocmVlIHJvd3MgKGxlZnRvdmVyLWJlZm9yZSwgdXBkYXRlZCwgbGVmdG92ZXIt
YWZ0ZXIpCistLSBtdXN0IGhhdmUgY29ycmVjdCByYW5nZV9sZW4gdmFsdWVzLgorVVBEQVRFIGZw
b19nZW5lcmF0ZWRfc3RvcmVkCisgIEZPUiBQT1JUSU9OIE9GIHZhbGlkX2F0IEZST00gMzAgVE8g
NzAKKyAgU0VUIGlkID0gMjsKK1NFTEVDVCAqIEZST00gZnBvX2dlbmVyYXRlZF9zdG9yZWQgT1JE
RVIgQlkgdmFsaWRfYXQ7CisKKy0tIEFsc28gdGVzdCB3aXRoIGEgZ2VuZXJhdGVkIGNvbHVtbiB0
aGF0IHJlZmVyZW5jZXMgYm90aCBhIFNFVCBjb2x1bW4KKy0tIGFuZCB0aGUgcmFuZ2UgY29sdW1u
LgorVFJVTkNBVEUgZnBvX2dlbmVyYXRlZF9zdG9yZWQ7CitEUk9QIFRBQkxFIGZwb19nZW5lcmF0
ZWRfc3RvcmVkOworQ1JFQVRFIFRBQkxFIGZwb19nZW5lcmF0ZWRfc3RvcmVkICgKKyAgaWQgaW50
LAorICB2YWxpZF9hdCBpbnQ0cmFuZ2UsCisgIGlkX3BsdXNfbGVuIGludCBHRU5FUkFURUQgQUxX
QVlTIEFTIChpZCArIHVwcGVyKHZhbGlkX2F0KSAtIGxvd2VyKHZhbGlkX2F0KSkgU1RPUkVECisp
OworSU5TRVJUIElOVE8gZnBvX2dlbmVyYXRlZF9zdG9yZWQgKGlkLCB2YWxpZF9hdCkgVkFMVUVT
CisgICgxLCAnWzEwLDEwMCknKTsKK1NFTEVDVCAqIEZST00gZnBvX2dlbmVyYXRlZF9zdG9yZWQg
T1JERVIgQlkgdmFsaWRfYXQ7CisKK1VQREFURSBmcG9fZ2VuZXJhdGVkX3N0b3JlZAorICBGT1Ig
UE9SVElPTiBPRiB2YWxpZF9hdCBGUk9NIDMwIFRPIDcwCisgIFNFVCBpZCA9IDI7CitTRUxFQ1Qg
KiBGUk9NIGZwb19nZW5lcmF0ZWRfc3RvcmVkIE9SREVSIEJZIHZhbGlkX2F0OworCitEUk9QIFRB
QkxFIGZwb19nZW5lcmF0ZWRfc3RvcmVkOworCiBSRVNFVCBkYXRlc3R5bGU7Cg==
--000000000000ef8776064edac403--