On 10/07/26 18:13, Matheus Alcantara wrote:
On Mon Mar 16, 2026 at 12:36 PM -03, Matheus Alcantara wrote:
Attaching v5 with documentation changes included and rebased with master.
Attaching v6 rebased due to changes on a4639d64e21
Attaching v7 rebased with master
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 5ca633f056bdc390db8ba5892490cb51777e2823 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Fri, 28 Aug 2026 08:14:30 -0300
Subject: [PATCH v7] Compute virtual generated columns during INSERT/UPDATE
Previously, virtual generated column values were never computed and
stored in the slot. Instead, references to e.g virtual columns in check
constraints were expanded to their generation expressions during
ExecCheck().
This commit changes the approach to compute virtual generated column
values and store them in the slot before constraint checking, similar
to how stored generated columns are handled. This has two benefits:
Check constraints can now read virtual column values directly from
the slot, simplifying constraint evaluation. It also avoid re-execute
the virtual expression for each check constraint evaluation.
Error messages now display the actual computed value (e.g., "10")
instead of the "virtual" string, making them more consistent with
stored generated columns and easier to understand.
Discussion:
https://www.postgresql.org/message-id/DG5DV8SED62G.2WFJB46D7WIT8%40gmail.com
---
doc/src/sgml/ddl.sgml | 9 +-
doc/src/sgml/ref/create_foreign_table.sgml | 13 +-
doc/src/sgml/ref/create_table.sgml | 4 +-
src/backend/executor/execExprInterp.c | 7 +-
src/backend/executor/execMain.c | 21 ++-
src/backend/executor/execReplication.c | 12 ++
src/backend/executor/nodeModifyTable.c | 131 +++++++++++++++++-
src/include/executor/nodeModifyTable.h | 4 +
src/include/nodes/execnodes.h | 10 ++
.../regress/expected/generated_virtual.out | 22 ++-
src/test/regress/sql/generated_virtual.sql | 4 +-
11 files changed, 201 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index cb8bf7965cd..32112157952 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -357,10 +357,11 @@ INSERT INTO people (id, name, address) VALUES
(<emphasis>DEFAULT</emphasis>, 'C'
columns. Thus, it is for columns what a view is for tables. There are two
kinds of generated columns: stored and virtual. A stored generated column
is computed when it is written (inserted or updated) and occupies storage
- as if it were a normal column. A virtual generated column occupies no
- storage and is computed when it is read. Thus, a virtual generated column
- is similar to a view and a stored generated column is similar to a
- materialized view (except that it is always updated automatically).
+ as if it were a normal column. A virtual generated column is also computed
when
+ it is written, but occupies no storage and is recomputed on every read.
+ Thus, a virtual generated column is similar to a view and a stored generated
+ column is similar to a materialized view (except that it is always updated
+ automatically)
</para>
<para>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml
b/doc/src/sgml/ref/create_foreign_table.sgml
index 083f16772b7..e85579be18f 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -403,12 +403,13 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
<para>
When <literal>VIRTUAL</literal> is specified, the column will be
- computed when it is read. (The foreign-data wrapper will see it as a
- null value in new rows and may choose to store it as a null value or
- ignore it altogether.) When <literal>STORED</literal> is specified, the
- column will be computed on write. (The computed value will be presented
- to the foreign-data wrapper for storage and must be returned on
- reading.) <literal>VIRTUAL</literal> is the default.
+ computed when it is written, but it will not occupy any storage.
+ (The foreign-data wrapper will see it as a null value in new rows
+ and may choose to store it as a null value or ignore it altogether.)
+ When <literal>STORED</literal> is specified, the column will be computed
+ on write. (The computed value will be presented to the foreign-data
+ wrapper for storage and must be returned on reading.)
+ <literal>VIRTUAL</literal> is the default.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index e342585c7f0..b5d214e3611 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -933,8 +933,8 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- When <literal>VIRTUAL</literal> is specified, the column will be
- computed when it is read, and it will not occupy any storage. When
+ When <literal>VIRTUAL</literal> is specified, the column will be computed
+ when it is written and read, but it will not occupy any storage. When
<literal>STORED</literal> is specified, the column will be computed on
write and will be stored on disk. <literal>VIRTUAL</literal> is the
default.
diff --git a/src/backend/executor/execExprInterp.c
b/src/backend/executor/execExprInterp.c
index 397219f7a3a..3e49d5bf267 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2415,9 +2415,10 @@ CheckVarSlotCompatibility(TupleTableSlot *slot, int
attnum, Oid vartype)
attr = TupleDescAttr(slot_tupdesc, attnum - 1);
- /* Internal error: somebody forgot to expand it. */
- if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- elog(ERROR, "unexpected virtual generated column
reference");
+ /*
+ * Virtual generated columns are now computed and stored in the
slot
+ * by ExecComputeVirtualGenerated(), so reading them is valid.
+ */
if (attr->attisdropped)
ereport(ERROR,
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 6e47856cf25..5cbaf11e93e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -53,6 +53,8 @@
#include "foreign/fdwapi.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/queryjumble.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
@@ -1357,6 +1359,8 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_GenVirtualNotNullConstraintExprs = NULL;
resultRelInfo->ri_GeneratedExprsI = NULL;
resultRelInfo->ri_GeneratedExprsU = NULL;
+ resultRelInfo->ri_VirtualGeneratedExprsI = NULL;
+ resultRelInfo->ri_VirtualGeneratedExprsU = NULL;
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
resultRelInfo->ri_onConflict = NULL;
@@ -1893,7 +1897,13 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
continue;
checkconstr = stringToNode(check[i].ccbin);
- checkconstr = (Expr *)
expand_generated_columns_in_expr((Node *) checkconstr, rel, 1);
+
+ /*
+ * No need to call expand_generated_columns_in_expr()
here.
+ * Virtual generated column values are computed and
stored in the
+ * slot by ExecComputeVirtualGenerated() before we get
here, so
+ * the expression evaluator can read them directly from
the slot.
+ */
resultRelInfo->ri_CheckConstraintExprs[i] =
ExecPrepareExpr(checkconstr, estate);
}
@@ -2554,9 +2564,12 @@ ExecBuildSlotValueDescription(Oid reloid,
if (table_perm || column_perm)
{
- if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- val = "virtual";
- else if (slot->tts_isnull[i])
+ /*
+ * Virtual generated column values are computed and
stored in the
+ * slot by ExecComputeVirtualGenerated(), so we can
read them
+ * directly like regular columns.
+ */
+ if (slot->tts_isnull[i])
val = "null";
else
{
diff --git a/src/backend/executor/execReplication.c
b/src/backend/executor/execReplication.c
index b2ca5cbf117..640c0ff58cf 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -838,6 +838,12 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_INSERT);
+ /* Compute virtual generated columns */
+ if (rel->rd_att->constr &&
+ rel->rd_att->constr->has_generated_virtual)
+ ExecComputeVirtualGenerated(resultRelInfo, estate, slot,
+
CMD_INSERT);
+
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
ExecConstraints(resultRelInfo, slot, estate);
@@ -942,6 +948,12 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_UPDATE);
+ /* Compute virtual generated columns */
+ if (rel->rd_att->constr &&
+ rel->rd_att->constr->has_generated_virtual)
+ ExecComputeVirtualGenerated(resultRelInfo, estate, slot,
+
CMD_UPDATE);
+
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
ExecConstraints(resultRelInfo, slot, estate);
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 3056b850f73..9683de4dc04 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -439,8 +439,10 @@ ExecCheckTIDVisible(EState *estate,
* Initialize generated columns handling for a tuple
*
* This fills the resultRelInfo's ri_GeneratedExprsI/ri_NumGeneratedNeededI or
- * ri_GeneratedExprsU/ri_NumGeneratedNeededU fields, depending on cmdtype.
- * This is used only for stored generated columns.
+ * ri_GeneratedExprsU/ri_NumGeneratedNeededU fields for stored generated
+ * columns, and ri_VirtualGeneratedExprsI/ri_NumVirtualGeneratedNeededI or
+ * ri_VirtualGeneratedExprsU/ri_NumVirtualGeneratedNeededU fields for virtual
+ * generated columns, depending on cmdtype.
*
* If cmdType == CMD_UPDATE, the ri_extraUpdatedCols field is filled too.
* This is used by both stored and virtual generated columns.
@@ -460,6 +462,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
int natts = tupdesc->natts;
ExprState **ri_GeneratedExprs;
int ri_NumGeneratedNeeded;
+ ExprState **ri_VirtualGeneratedExprs;
+ int ri_NumVirtualGeneratedNeeded;
Bitmapset *updatedCols;
MemoryContext oldContext;
@@ -487,6 +491,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs = palloc0_array(ExprState *, natts);
ri_NumGeneratedNeeded = 0;
+ ri_VirtualGeneratedExprs = (ExprState **) palloc0(natts *
sizeof(ExprState *));
+ ri_NumVirtualGeneratedNeeded = 0;
for (int i = 0; i < natts; i++)
{
@@ -522,6 +528,11 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs[i] = ExecPrepareExpr(expr,
estate);
ri_NumGeneratedNeeded++;
}
+ else if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ ri_VirtualGeneratedExprs[i] =
ExecPrepareExpr(expr, estate);
+ ri_NumVirtualGeneratedNeeded++;
+ }
/* If UPDATE, mark column in
resultRelInfo->ri_extraUpdatedCols */
if (cmdtype == CMD_UPDATE)
@@ -538,14 +549,24 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs = NULL;
}
+ if (ri_NumVirtualGeneratedNeeded == 0)
+ {
+ /* didn't need it after all */
+ pfree(ri_VirtualGeneratedExprs);
+ ri_VirtualGeneratedExprs = NULL;
+ }
+
/* Save in appropriate set of fields */
if (cmdtype == CMD_UPDATE)
{
/* Don't call twice */
Assert(resultRelInfo->ri_GeneratedExprsU == NULL);
+ Assert(resultRelInfo->ri_VirtualGeneratedExprsU == NULL);
resultRelInfo->ri_GeneratedExprsU = ri_GeneratedExprs;
resultRelInfo->ri_NumGeneratedNeededU = ri_NumGeneratedNeeded;
+ resultRelInfo->ri_VirtualGeneratedExprsU =
ri_VirtualGeneratedExprs;
+ resultRelInfo->ri_NumVirtualGeneratedNeededU =
ri_NumVirtualGeneratedNeeded;
resultRelInfo->ri_extraUpdatedCols_valid = true;
}
@@ -553,9 +574,12 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
{
/* Don't call twice */
Assert(resultRelInfo->ri_GeneratedExprsI == NULL);
+ Assert(resultRelInfo->ri_VirtualGeneratedExprsI == NULL);
resultRelInfo->ri_GeneratedExprsI = ri_GeneratedExprs;
resultRelInfo->ri_NumGeneratedNeededI = ri_NumGeneratedNeeded;
+ resultRelInfo->ri_VirtualGeneratedExprsI =
ri_VirtualGeneratedExprs;
+ resultRelInfo->ri_NumVirtualGeneratedNeededI =
ri_NumVirtualGeneratedNeeded;
}
MemoryContextSwitchTo(oldContext);
@@ -651,6 +675,85 @@ ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo,
MemoryContextSwitchTo(oldContext);
}
+/*
+ * Compute virtual generated columns for a tuple
+ *
+ * This evaluates the generation expressions for virtual generated columns
+ * and stores the results directly in the slot. Unlike stored generated
+ * columns, these values are not persisted to disk but are computed for
+ * use in constraint checking and error messages.
+ */
+void
+ExecComputeVirtualGenerated(ResultRelInfo *resultRelInfo,
+ EState *estate,
TupleTableSlot *slot,
+ CmdType cmdtype)
+{
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ ExprContext *econtext = GetPerTupleExprContext(estate);
+ ExprState **ri_VirtualGeneratedExprs;
+ MemoryContext oldContext;
+
+ /* We should not be called unless this is true */
+ Assert(tupdesc->constr && tupdesc->constr->has_generated_virtual);
+
+ /*
+ * Initialize the expressions if we didn't already, and check whether we
+ * can exit early because nothing needs to be computed.
+ */
+ if (cmdtype == CMD_UPDATE)
+ {
+ if (resultRelInfo->ri_VirtualGeneratedExprsU == NULL)
+ ExecInitGenerated(resultRelInfo, estate, cmdtype);
+ if (resultRelInfo->ri_NumVirtualGeneratedNeededU == 0)
+ return;
+ ri_VirtualGeneratedExprs =
resultRelInfo->ri_VirtualGeneratedExprsU;
+ }
+ else
+ {
+ if (resultRelInfo->ri_VirtualGeneratedExprsI == NULL)
+ ExecInitGenerated(resultRelInfo, estate, cmdtype);
+ if (resultRelInfo->ri_NumVirtualGeneratedNeededI == 0)
+ return;
+ ri_VirtualGeneratedExprs =
resultRelInfo->ri_VirtualGeneratedExprsI;
+ }
+
+ oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+
+ /* Make sure all base column values are available */
+ slot_getallattrs(slot);
+
+ econtext->ecxt_scantuple = slot;
+
+ for (int i = 0; i < natts; i++)
+ {
+ CompactAttribute *attr = TupleDescCompactAttr(tupdesc, i);
+
+ if (ri_VirtualGeneratedExprs[i])
+ {
+ Datum val;
+ bool isnull;
+
+ Assert(TupleDescAttr(tupdesc, i)->attgenerated ==
ATTRIBUTE_GENERATED_VIRTUAL);
+
+ val = ExecEvalExpr(ri_VirtualGeneratedExprs[i],
econtext, &isnull);
+
+ /*
+ * We must make a copy of val as we have no guarantees
about where
+ * memory for a pass-by-reference Datum is located.
+ */
+ if (!isnull)
+ val = datumCopy(val, attr->attbyval,
attr->attlen);
+
+ slot->tts_values[i] = val;
+ slot->tts_isnull[i] = isnull;
+ }
+ }
+
+ MemoryContextSwitchTo(oldContext);
+}
+
/*
* ExecInitInsertProjection
* Do one-time initialization of projection data for INSERT tuples.
@@ -959,6 +1062,14 @@ ExecInsert(ModifyTableContext *context,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_INSERT);
+ /*
+ * Compute virtual generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+
resultRelationDesc->rd_att->constr->has_generated_virtual)
+ ExecComputeVirtualGenerated(resultRelInfo, estate, slot,
+
CMD_INSERT);
+
/*
* If the FDW supports batching, and batching is requested,
accumulate
* rows and insert them in batches. Otherwise use the per-row
inserts.
@@ -1084,6 +1195,14 @@ ExecInsert(ModifyTableContext *context,
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_INSERT);
+ /*
+ * Compute virtual generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+
resultRelationDesc->rd_att->constr->has_generated_virtual)
+ ExecComputeVirtualGenerated(resultRelInfo, estate, slot,
+
CMD_INSERT);
+
/*
* Check any RLS WITH CHECK policies.
*
@@ -2439,6 +2558,14 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo,
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeStoredGenerated(resultRelInfo, estate, slot,
CMD_UPDATE);
+
+ /*
+ * Compute virtual generated columns
+ */
+ if (resultRelationDesc->rd_att->constr &&
+ resultRelationDesc->rd_att->constr->has_generated_virtual)
+ ExecComputeVirtualGenerated(resultRelInfo, estate, slot,
+
CMD_UPDATE);
}
/*
diff --git a/src/include/executor/nodeModifyTable.h
b/src/include/executor/nodeModifyTable.h
index f6070e1cdf3..7cbbe8c1a1f 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -23,6 +23,10 @@ extern void ExecComputeStoredGenerated(ResultRelInfo
*resultRelInfo,
EState *estate, TupleTableSlot *slot,
CmdType cmdtype);
+extern void ExecComputeVirtualGenerated(ResultRelInfo *resultRelInfo,
+
EState *estate, TupleTableSlot *slot,
+
CmdType cmdtype);
+
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState
*estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
extern void ExecReScanModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index f0cb21444b2..e3ec3192924 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -603,6 +603,16 @@ typedef struct ResultRelInfo
int ri_NumGeneratedNeededI;
int ri_NumGeneratedNeededU;
+ /*
+ * Arrays of virtual generated columns ExprStates for
INSERT/UPDATE/MERGE.
+ */
+ ExprState **ri_VirtualGeneratedExprsI;
+ ExprState **ri_VirtualGeneratedExprsU;
+
+ /* number of virtual generated columns we need to compute */
+ int ri_NumVirtualGeneratedNeededI;
+ int ri_NumVirtualGeneratedNeededU;
+
/* list of RETURNING expressions */
List *ri_returningList;
diff --git a/src/test/regress/expected/generated_virtual.out
b/src/test/regress/expected/generated_virtual.out
index 6ee029796f1..43f5cd8dc78 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -166,11 +166,9 @@ SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
2 | 4
(1 row)
--- test that overflow error happens on read
+-- test that overflow error happens on write
INSERT INTO gtest1 VALUES (2000000000);
-SELECT * FROM gtest1;
ERROR: integer out of range
-DELETE FROM gtest1 WHERE a = 2000000000;
-- test with joins
CREATE TABLE gtestx (x int, y int);
INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);
@@ -647,7 +645,7 @@ CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED
ALWAYS AS (a * 2) VIRTU
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
ERROR: new row for relation "gtest20" violates check constraint
"gtest20_b_check"
-DETAIL: Failing row contains (30, virtual).
+DETAIL: Failing row contains (30, 60).
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates
constraint
ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated
by some row
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
@@ -693,7 +691,7 @@ ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK
(gtest20c IS NOT NULL)
INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ERROR: new row for relation "gtest20c" violates check constraint
"whole_row_check"
-DETAIL: Failing row contains (null, virtual).
+DETAIL: Failing row contains (null, null).
ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); --
violates constraint
ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated
by some row
-- index with whole-row reference needs rebuild
@@ -708,12 +706,12 @@ CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED
ALWAYS AS (nullif(a, 0
INSERT INTO gtest21a (a) VALUES (1); -- ok
INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21a" violates not-null
constraint
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, null).
-- also check with table constraint syntax
CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS
(nullif(a, 0)) VIRTUAL, CONSTRAINT cc NOT NULL b);
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null
constraint
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, null).
INSERT INTO gtest21ax (a) VALUES (1); --ok
-- SET EXPRESSION supports not null constraint
ALTER TABLE gtest21ax ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1)); --error
@@ -723,17 +721,17 @@ CREATE TABLE gtest21ax (a int PRIMARY KEY, b int
GENERATED ALWAYS AS (nullif(a,
ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b;
INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21ax" violates not-null
constraint
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, null).
DROP TABLE gtest21ax;
CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0))
VIRTUAL);
ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
INSERT INTO gtest21b (a) VALUES (1); -- ok
INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint
ERROR: null value in column "b" of relation "gtest21b" violates not-null
constraint
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, null).
INSERT INTO gtest21b (a) VALUES (NULL); -- error
ERROR: null value in column "b" of relation "gtest21b" violates not-null
constraint
-DETAIL: Failing row contains (null, virtual).
+DETAIL: Failing row contains (null, null).
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
@@ -790,10 +788,10 @@ CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent
default;
INSERT INTO gtestnn_parent VALUES (2, 2, default), (3, 5, default), (14, 12,
default); -- ok
INSERT INTO gtestnn_parent VALUES (1, 2, default); -- error
ERROR: null value in column "f3" of relation "gtestnn_child" violates
not-null constraint
-DETAIL: Failing row contains (1, 2, virtual).
+DETAIL: Failing row contains (1, 2, null).
INSERT INTO gtestnn_parent VALUES (2, 10, default); -- error
ERROR: null value in column "f3" of relation "gtestnn_child" violates
not-null constraint
-DETAIL: Failing row contains (2, 10, virtual).
+DETAIL: Failing row contains (2, 10, null).
ALTER TABLE gtestnn_parent ALTER COLUMN f3 SET EXPRESSION AS (nullif(f1, 2) +
nullif(f2, 11)); -- error
ERROR: column "f3" of relation "gtestnn_child" contains null values
INSERT INTO gtestnn_parent VALUES (10, 11, default); -- ok
diff --git a/src/test/regress/sql/generated_virtual.sql
b/src/test/regress/sql/generated_virtual.sql
index e4ea63bb3a1..3cd013d150b 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -71,10 +71,8 @@ SELECT * FROM gtest1 ORDER BY a;
SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
--- test that overflow error happens on read
+-- test that overflow error happens on write
INSERT INTO gtest1 VALUES (2000000000);
-SELECT * FROM gtest1;
-DELETE FROM gtest1 WHERE a = 2000000000;
-- test with joins
CREATE TABLE gtestx (x int, y int);
--
2.50.1 (Apple Git-155)
