public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Postgres hackers <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Fix tuple deformation with virtual generated NOT NULL columns
Date: Thu, 4 Jun 2026 13:57:05 +0800
Message-ID: <[email protected]> (raw)
Hi,
While testing "Optimize tuple deformation”, I found a bug:
```
evantest=# 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=# insert into t (a, b) values (10, 20);
INSERT 0 1
evantest=# 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 = 0; i < tupdesc->natts; i++)
{
CompactAttribute *cattr = 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 == tupdesc->natts &&
(cattr->attnullability != ATTNULLABLE_VALID || !cattr->attbyval ||
cattr->atthasmissing || cattr->attisdropped || cattr->attlen <= 0))
firstNonGuaranteedAttr = 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 == tupdesc->natts &&
(cattr->attnullability != ATTNULLABLE_VALID || !cattr->attbyval ||
cattr->atthasmissing || cattr->attisdropped || cattr->attlen <= 0 ||
(cattr->attgenerated &&
TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)))
firstNonGuaranteedAttr = 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=# 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/
Attachments:
[application/octet-stream] v1-0001-Fix-tuple-deformation-with-virtual-generated-NOT-.patch (4.0K, 2-v1-0001-Fix-tuple-deformation-with-virtual-generated-NOT-.patch)
download | inline diff:
From e462b3468489d2c42d6d836b677a06bd699194ba Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 4 Jun 2026 13:51:24 +0800
Subject: [PATCH v1] Fix tuple deformation with virtual generated NOT NULL
columns
TupleDescFinalize() computes firstNonGuaranteedAttr for the slot
deformation fast path. Virtual generated columns can have valid NOT NULL
constraints, but they are not physically stored in heap tuples. Treating
such columns as part of the guaranteed physical prefix can make tuple
deformation advance the data offset as if the virtual column were stored,
causing following attributes to be read from the wrong location.
Exclude virtual generated columns from the guaranteed prefix while still
allowing stored generated columns to use the optimization.
Add a regression test with a virtual generated NOT NULL column followed by
another fixed-width NOT NULL column, which previously exposed the wrong
offset calculation.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/
---
src/backend/access/common/tupdesc.c | 8 ++++++--
src/test/regress/expected/generated_virtual.out | 9 +++++++++
src/test/regress/sql/generated_virtual.sql | 5 +++++
3 files changed, 20 insertions(+), 2 deletions(-)
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 196472c05d0..4aee876a055 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -521,11 +521,15 @@ TupleDescFinalize(TupleDesc tupdesc)
/*
* 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.
+ * to allow additional optimizations during tuple deformation. Virtual
+ * generated columns are excluded, since they are computed at read
+ * time and are not physically stored in tuples.
*/
if (firstNonGuaranteedAttr == tupdesc->natts &&
(cattr->attnullability != ATTNULLABLE_VALID || !cattr->attbyval ||
- cattr->atthasmissing || cattr->attisdropped || cattr->attlen <= 0))
+ cattr->atthasmissing || cattr->attisdropped || cattr->attlen <= 0 ||
+ (cattr->attgenerated &&
+ TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)))
firstNonGuaranteedAttr = i;
if (cattr->attlen <= 0)
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 24d5dbf46ca..7a5788146f5 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -727,6 +727,15 @@ ERROR: null value in column "b" of relation "gtest21b" violates not-null constr
DETAIL: Failing row contains (null, virtual).
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- virtual generated columns are not physically stored, even when not null
+CREATE TABLE gtest21c (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL NOT NULL, c int NOT NULL);
+INSERT INTO gtest21c (a, c) VALUES (10, 42);
+SELECT a, b, c FROM gtest21c;
+ a | b | c
+----+----+----
+ 10 | 20 | 42
+(1 row)
+
-- not-null constraint with partitioned table
CREATE TABLE gtestnn_parent (
f1 int,
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9c2bb6590b3..126ae3ecda9 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -374,6 +374,11 @@ INSERT INTO gtest21b (a) VALUES (NULL); -- error
ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
INSERT INTO gtest21b (a) VALUES (0); -- ok now
+-- virtual generated columns are not physically stored, even when not null
+CREATE TABLE gtest21c (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) VIRTUAL NOT NULL, c int NOT NULL);
+INSERT INTO gtest21c (a, c) VALUES (10, 42);
+SELECT a, b, c FROM gtest21c;
+
-- not-null constraint with partitioned table
CREATE TABLE gtestnn_parent (
f1 int,
--
2.50.1 (Apple Git-155)
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Fix tuple deformation with virtual generated NOT NULL columns
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox