From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_D7D26C76-DD76-4520-82FF-9E4C1C32F859"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Fix tuple deformation with virtual generated NOT NULL columns
Message-Id: <A4BC563C-0CA3-4EF3-952A-EA41F9E5BF1E@gmail.com>
Date: Thu, 4 Jun 2026 13:57:05 +0800
Cc: David Rowley <dgrowleyml@gmail.com>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/A4BC563C-0CA3-4EF3-952A-EA41F9E5BF1E%40gmail.com>
Precedence: bulk


--Apple-Mail=_D7D26C76-DD76-4520-82FF-9E4C1C32F859
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Hi,

While testing "Optimize tuple deformation=E2=80=9D, I found a bug:
```
evantest=3D# create table t (a int not null,
evantest(# g int generated always as (a+1) virtual not null,
evantest(# b int not null);
CREATE TABLE
evantest=3D# insert into t (a, b) values (10, 20);
INSERT 0 1
evantest=3D# select a, g, b from t;
 a  | g  | b
----+----+---
 10 | 11 | 0
(1 row)
```

Here, b was inserted as 20, but select only returned 0.

I think the problem is in finding the first non-guaranteed attribute =
where virtual generated attributes are not considered:
```
	for (int i =3D 0; i < tupdesc->natts; i++)
	{
		CompactAttribute *cattr =3D =
TupleDescCompactAttr(tupdesc, i);

		/*
		 * Find the highest attnum which is guaranteed to exist =
in all tuples
		 * in the table.  We currently only pay attention to =
byval attributes
		 * to allow additional optimizations during tuple =
deformation.
		 */
		if (firstNonGuaranteedAttr =3D=3D tupdesc->natts &&
			(cattr->attnullability !=3D ATTNULLABLE_VALID || =
!cattr->attbyval ||
			 cattr->atthasmissing || cattr->attisdropped || =
cattr->attlen <=3D 0))
			firstNonGuaranteedAttr =3D i;
```

To fix this, we should consider virtual generated attributes as =
non-guaranteed. The tricky part is that cattr->attgenerated is only a =
boolean and cannot distinguish virtual generated from stored. So we have =
to further check TupleDescAttr(tupdesc, i)->attgenerated. In the patch, =
I changed the check as follows:
```
		if (firstNonGuaranteedAttr =3D=3D tupdesc->natts &&
			(cattr->attnullability !=3D ATTNULLABLE_VALID || =
!cattr->attbyval ||
			 cattr->atthasmissing || cattr->attisdropped || =
cattr->attlen <=3D 0 ||
			 (cattr->attgenerated &&
			  TupleDescAttr(tupdesc, i)->attgenerated =3D=3D =
ATTRIBUTE_GENERATED_VIRTUAL)))
			firstNonGuaranteedAttr =3D i;
```

This way, we only check TupleDescAttr(tupdesc, i)->attgenerated when =
needed.

See the attached patch for details. I also added a regression test case =
to cover this fix. With the fix, select now returns correct values:
```
evantest=3D# select a, g, b from t;
 a  | g  | b
----+----+----
 10 | 11 | 20
(1 row)
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_D7D26C76-DD76-4520-82FF-9E4C1C32F859
Content-Disposition: attachment;
	filename=v1-0001-Fix-tuple-deformation-with-virtual-generated-NOT-.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-Fix-tuple-deformation-with-virtual-generated-NOT-.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=20e462b3468489d2c42d6d836b677a06bd699194ba=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Thu,=204=20Jun=202026=2013:51:24=20+0800=0ASubject:=20[PATCH=20=
v1]=20Fix=20tuple=20deformation=20with=20virtual=20generated=20NOT=20=
NULL=0A=20columns=0A=0ATupleDescFinalize()=20computes=20=
firstNonGuaranteedAttr=20for=20the=20slot=0Adeformation=20fast=20path.=20=
Virtual=20generated=20columns=20can=20have=20valid=20NOT=20NULL=0A=
constraints,=20but=20they=20are=20not=20physically=20stored=20in=20heap=20=
tuples.=20Treating=0Asuch=20columns=20as=20part=20of=20the=20guaranteed=20=
physical=20prefix=20can=20make=20tuple=0Adeformation=20advance=20the=20=
data=20offset=20as=20if=20the=20virtual=20column=20were=20stored,=0A=
causing=20following=20attributes=20to=20be=20read=20from=20the=20wrong=20=
location.=0A=0AExclude=20virtual=20generated=20columns=20from=20the=20=
guaranteed=20prefix=20while=20still=0Aallowing=20stored=20generated=20=
columns=20to=20use=20the=20optimization.=0A=0AAdd=20a=20regression=20=
test=20with=20a=20virtual=20generated=20NOT=20NULL=20column=20followed=20=
by=0Aanother=20fixed-width=20NOT=20NULL=20column,=20which=20previously=20=
exposed=20the=20wrong=0Aoffset=20calculation.=0A=0AAuthor:=20Chao=20Li=20=
<lic@highgo.com>=0AReviewed-by:=0ADiscussion:=20https://postgr.es/m/=0A=
---=0A=20src/backend/access/common/tupdesc.c=20=20=20=20=20=20=20=20=20=20=
=20=20=20|=208=20++++++--=0A=20=
src/test/regress/expected/generated_virtual.out=20|=209=20+++++++++=0A=20=
src/test/regress/sql/generated_virtual.sql=20=20=20=20=20=20|=205=20=
+++++=0A=203=20files=20changed,=2020=20insertions(+),=202=20deletions(-)=0A=
=0Adiff=20--git=20a/src/backend/access/common/tupdesc.c=20=
b/src/backend/access/common/tupdesc.c=0Aindex=20196472c05d0..4aee876a055=20=
100644=0A---=20a/src/backend/access/common/tupdesc.c=0A+++=20=
b/src/backend/access/common/tupdesc.c=0A@@=20-521,11=20+521,15=20@@=20=
TupleDescFinalize(TupleDesc=20tupdesc)=0A=20=09=09/*=0A=20=09=09=20*=20=
Find=20the=20highest=20attnum=20which=20is=20guaranteed=20to=20exist=20=
in=20all=20tuples=0A=20=09=09=20*=20in=20the=20table.=20=20We=20=
currently=20only=20pay=20attention=20to=20byval=20attributes=0A-=09=09=20=
*=20to=20allow=20additional=20optimizations=20during=20tuple=20=
deformation.=0A+=09=09=20*=20to=20allow=20additional=20optimizations=20=
during=20tuple=20deformation.=20Virtual=0A+=09=09=20*=20generated=20=
columns=20are=20excluded,=20since=20they=20are=20computed=20at=20read=0A=
+=09=09=20*=20time=20and=20are=20not=20physically=20stored=20in=20=
tuples.=0A=20=09=09=20*/=0A=20=09=09if=20(firstNonGuaranteedAttr=20=3D=3D=20=
tupdesc->natts=20&&=0A=20=09=09=09(cattr->attnullability=20!=3D=20=
ATTNULLABLE_VALID=20||=20!cattr->attbyval=20||=0A-=09=09=09=20=
cattr->atthasmissing=20||=20cattr->attisdropped=20||=20cattr->attlen=20=
<=3D=200))=0A+=09=09=09=20cattr->atthasmissing=20||=20=
cattr->attisdropped=20||=20cattr->attlen=20<=3D=200=20||=0A+=09=09=09=20=
(cattr->attgenerated=20&&=0A+=09=09=09=20=20TupleDescAttr(tupdesc,=20=
i)->attgenerated=20=3D=3D=20ATTRIBUTE_GENERATED_VIRTUAL)))=0A=20=09=09=09=
firstNonGuaranteedAttr=20=3D=20i;=0A=20=0A=20=09=09if=20(cattr->attlen=20=
<=3D=200)=0Adiff=20--git=20=
a/src/test/regress/expected/generated_virtual.out=20=
b/src/test/regress/expected/generated_virtual.out=0Aindex=20=
24d5dbf46ca..7a5788146f5=20100644=0A---=20=
a/src/test/regress/expected/generated_virtual.out=0A+++=20=
b/src/test/regress/expected/generated_virtual.out=0A@@=20-727,6=20=
+727,15=20@@=20ERROR:=20=20null=20value=20in=20column=20"b"=20of=20=
relation=20"gtest21b"=20violates=20not-null=20constr=0A=20DETAIL:=20=20=
Failing=20row=20contains=20(null,=20virtual).=0A=20ALTER=20TABLE=20=
gtest21b=20ALTER=20COLUMN=20b=20DROP=20NOT=20NULL;=0A=20INSERT=20INTO=20=
gtest21b=20(a)=20VALUES=20(0);=20=20--=20ok=20now=0A+--=20virtual=20=
generated=20columns=20are=20not=20physically=20stored,=20even=20when=20=
not=20null=0A+CREATE=20TABLE=20gtest21c=20(a=20int=20NOT=20NULL,=20b=20=
int=20GENERATED=20ALWAYS=20AS=20(a=20*=202)=20VIRTUAL=20NOT=20NULL,=20c=20=
int=20NOT=20NULL);=0A+INSERT=20INTO=20gtest21c=20(a,=20c)=20VALUES=20=
(10,=2042);=0A+SELECT=20a,=20b,=20c=20FROM=20gtest21c;=0A+=20a=20=20|=20=
b=20=20|=20c=20=20=0A+----+----+----=0A+=2010=20|=2020=20|=2042=0A+(1=20=
row)=0A+=0A=20--=20not-null=20constraint=20with=20partitioned=20table=0A=20=
CREATE=20TABLE=20gtestnn_parent=20(=0A=20=20=20=20=20f1=20int,=0Adiff=20=
--git=20a/src/test/regress/sql/generated_virtual.sql=20=
b/src/test/regress/sql/generated_virtual.sql=0Aindex=20=
9c2bb6590b3..126ae3ecda9=20100644=0A---=20=
a/src/test/regress/sql/generated_virtual.sql=0A+++=20=
b/src/test/regress/sql/generated_virtual.sql=0A@@=20-374,6=20+374,11=20=
@@=20INSERT=20INTO=20gtest21b=20(a)=20VALUES=20(NULL);=20=20--=20error=0A=
=20ALTER=20TABLE=20gtest21b=20ALTER=20COLUMN=20b=20DROP=20NOT=20NULL;=0A=20=
INSERT=20INTO=20gtest21b=20(a)=20VALUES=20(0);=20=20--=20ok=20now=0A=20=0A=
+--=20virtual=20generated=20columns=20are=20not=20physically=20stored,=20=
even=20when=20not=20null=0A+CREATE=20TABLE=20gtest21c=20(a=20int=20NOT=20=
NULL,=20b=20int=20GENERATED=20ALWAYS=20AS=20(a=20*=202)=20VIRTUAL=20NOT=20=
NULL,=20c=20int=20NOT=20NULL);=0A+INSERT=20INTO=20gtest21c=20(a,=20c)=20=
VALUES=20(10,=2042);=0A+SELECT=20a,=20b,=20c=20FROM=20gtest21c;=0A+=0A=20=
--=20not-null=20constraint=20with=20partitioned=20table=0A=20CREATE=20=
TABLE=20gtestnn_parent=20(=0A=20=20=20=20=20f1=20int,=0A--=20=0A2.50.1=20=
(Apple=20Git-155)=0A=0A=

--Apple-Mail=_D7D26C76-DD76-4520-82FF-9E4C1C32F859--