public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matheus Alcantara <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Cc: Yugo Nagata <[email protected]>
Subject: Re: Show expression of virtual columns in error messages
Date: Tue, 03 Mar 2026 14:01:43 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On 26/02/26 19:14, Tom Lane wrote:
>> Thanks for the feedback. After investigating the code a bit, I found
>> that IIUC virtual column values are actually never computed and stored
>> separately, they're computed by expanding the expression wherever the
>> column is referenced.
>
> Correct: rather than storing them, we recalculate the expression
> whenever the column's value is demanded. But what I'm suggesting is
> that we ought to calculate the value during INSERT/UPDATE as well,
> even though it will not get written to disk. It'd be useful to do
> that for the purposes of this error message. But I think we ought to
> do it even when there are no constraints, because that ensures that
> it's *possible* to calculate the value, and that there are not for
> instance overflow problems. As things stand, it's possible to create
> a row that cannot be fetched:
>
> regression=# create table foo (f1 int, f2 int generated always as (f1 * 1000000));
> CREATE TABLE
> regression=# insert into foo values(1);
> INSERT 0 1
> regression=# table foo;
> f1 | f2
> ----+---------
> 1 | 1000000
> (1 row)
>
> regression=# insert into foo values(1000000);
> INSERT 0 1
> regression=# table foo;
> ERROR: integer out of range
>
> That may or may not be per spec, but I submit that it's extremely
> unhelpful and unfriendly, as well as being an undesirable discrepancy
> from the behavior of a stored generated column.
>
> Of course, calculating the value at insertion time won't completely
> prevent such problems: if the expression is less immutable than
> advertised, we could still fail at readout time. But that's pretty
> clearly a case of user misfeasance. Index expressions that aren't
> really immutable cause worse problems than this, so I don't have a
> problem with saying "you broke it, you get to keep both pieces".
>
> Perhaps someone will argue that their expression is too expensive for
> it to be okay to calculate at insertion. But if it's that expensive,
> why in the world did they make the column virtual rather than stored?
>
An addition to this, the expression is evaluated multiple times for
each check constraint that a virtual column have.
> In short, I think we ought to compute these values during
> INSERT/UPDATE, even though we're not going to write them to disk.
> And then they'd be available to display in this error message.
>
Please see the attached patch where I implement this. I followed a
similar pattern used for stored generated columns with some small
changes. Some special handling for virtual columns was removed but I
still left some comments but I'm not sure if it's really needed.
With this change I think that doc/src/sgml/ddl.sgml should be updated
to mention that a virtual generated column expression is also
evaluated when writing the data, or perhaps we can just remove the
following:
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
+ storage. Thus, a virtual generated column
is similar to a view and a stored generated column is similar to a
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From b45bcdee84c55065d3f84dbdeedfa16b4e916b40 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Mon, 2 Feb 2026 19:06:44 -0300
Subject: [PATCH v4] 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
---
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/expected/partition_merge.out | 8 +-
src/test/regress/sql/generated_virtual.sql | 4 +-
src/test/regress/sql/partition_merge.sql | 6 +-
10 files changed, 194 insertions(+), 31 deletions(-)
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 61ff5ddc74c..fc1ca898842 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2405,9 +2405,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 bfd3ebc601e..fcc59a784f5 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -51,6 +51,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"
@@ -1296,6 +1298,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;
@@ -1815,7 +1819,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);
}
@@ -2476,9 +2486,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 2497ee7edc5..7e9152e4224 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -834,6 +834,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);
@@ -938,6 +944,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 793c76d4f82..5a93a74bcc9 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -425,8 +425,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.
@@ -446,6 +448,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
int natts = tupdesc->natts;
ExprState **ri_GeneratedExprs;
int ri_NumGeneratedNeeded;
+ ExprState **ri_VirtualGeneratedExprs;
+ int ri_NumVirtualGeneratedNeeded;
Bitmapset *updatedCols;
MemoryContext oldContext;
@@ -473,6 +477,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs = (ExprState **) palloc0(natts * sizeof(ExprState *));
ri_NumGeneratedNeeded = 0;
+ ri_VirtualGeneratedExprs = (ExprState **) palloc0(natts * sizeof(ExprState *));
+ ri_NumVirtualGeneratedNeeded = 0;
for (int i = 0; i < natts; i++)
{
@@ -508,6 +514,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)
@@ -524,14 +535,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;
}
@@ -539,9 +560,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);
@@ -637,6 +661,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.
@@ -945,6 +1048,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.
@@ -1070,6 +1181,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.
*
@@ -2178,6 +2297,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 63c067d5aae..502e4e14d64 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -573,6 +573,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 6dab60c937b..18cfd5ab46c 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);
@@ -638,7 +636,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
@@ -684,18 +682,18 @@ 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).
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
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
@@ -705,17 +703,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
-- not-null constraint with partitioned table
@@ -730,10 +728,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/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 925fe4f570a..82914be45e4 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1060,9 +1060,9 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
DROP TABLE t;
-- Test for generated columns (different order of columns in partitioned table
-- and partitions).
-CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + tableoid::int)) PARTITION BY RANGE (i);
-CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
-CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
+CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + 2)) PARTITION BY RANGE (i);
+CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + 2), i int);
+CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + 2), i int);
ALTER TABLE t ATTACH PARTITION tp_1 FOR VALUES FROM (-1) TO (10);
ALTER TABLE t ATTACH PARTITION tp_2 FOR VALUES FROM (10) TO (20);
ALTER TABLE t ADD CHECK (g > 0);
@@ -1073,7 +1073,7 @@ INSERT INTO t VALUES (16);
-- ERROR: new row for relation "tp_12" violates check constraint "t_i_check"
INSERT INTO t VALUES (0);
ERROR: new row for relation "tp_12" violates check constraint "t_i_check"
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, 2).
-- Should be 3 rows: (5), (15), (16):
SELECT i FROM t ORDER BY i;
i
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index e750866d2d8..c8e6c749992 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);
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index a211fee2ad1..df4239718cb 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -762,9 +762,9 @@ DROP TABLE t;
-- Test for generated columns (different order of columns in partitioned table
-- and partitions).
-CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + tableoid::int)) PARTITION BY RANGE (i);
-CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
-CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
+CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + 2)) PARTITION BY RANGE (i);
+CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + 2), i int);
+CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + 2), i int);
ALTER TABLE t ATTACH PARTITION tp_1 FOR VALUES FROM (-1) TO (10);
ALTER TABLE t ATTACH PARTITION tp_2 FOR VALUES FROM (10) TO (20);
ALTER TABLE t ADD CHECK (g > 0);
--
2.52.0
Attachments:
[text/plain] v4-0001-Compute-virtual-generated-columns-during-INSERT-U.patch (22.0K, ../[email protected]/2-v4-0001-Compute-virtual-generated-columns-during-INSERT-U.patch)
download | inline diff:
From b45bcdee84c55065d3f84dbdeedfa16b4e916b40 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Mon, 2 Feb 2026 19:06:44 -0300
Subject: [PATCH v4] 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
---
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/expected/partition_merge.out | 8 +-
src/test/regress/sql/generated_virtual.sql | 4 +-
src/test/regress/sql/partition_merge.sql | 6 +-
10 files changed, 194 insertions(+), 31 deletions(-)
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 61ff5ddc74c..fc1ca898842 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2405,9 +2405,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 bfd3ebc601e..fcc59a784f5 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -51,6 +51,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"
@@ -1296,6 +1298,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;
@@ -1815,7 +1819,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);
}
@@ -2476,9 +2486,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 2497ee7edc5..7e9152e4224 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -834,6 +834,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);
@@ -938,6 +944,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 793c76d4f82..5a93a74bcc9 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -425,8 +425,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.
@@ -446,6 +448,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
int natts = tupdesc->natts;
ExprState **ri_GeneratedExprs;
int ri_NumGeneratedNeeded;
+ ExprState **ri_VirtualGeneratedExprs;
+ int ri_NumVirtualGeneratedNeeded;
Bitmapset *updatedCols;
MemoryContext oldContext;
@@ -473,6 +477,8 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs = (ExprState **) palloc0(natts * sizeof(ExprState *));
ri_NumGeneratedNeeded = 0;
+ ri_VirtualGeneratedExprs = (ExprState **) palloc0(natts * sizeof(ExprState *));
+ ri_NumVirtualGeneratedNeeded = 0;
for (int i = 0; i < natts; i++)
{
@@ -508,6 +514,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)
@@ -524,14 +535,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;
}
@@ -539,9 +560,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);
@@ -637,6 +661,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.
@@ -945,6 +1048,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.
@@ -1070,6 +1181,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.
*
@@ -2178,6 +2297,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 63c067d5aae..502e4e14d64 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -573,6 +573,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 6dab60c937b..18cfd5ab46c 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);
@@ -638,7 +636,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
@@ -684,18 +682,18 @@ 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).
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL);
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
@@ -705,17 +703,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
-- not-null constraint with partitioned table
@@ -730,10 +728,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/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 925fe4f570a..82914be45e4 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1060,9 +1060,9 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
DROP TABLE t;
-- Test for generated columns (different order of columns in partitioned table
-- and partitions).
-CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + tableoid::int)) PARTITION BY RANGE (i);
-CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
-CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
+CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + 2)) PARTITION BY RANGE (i);
+CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + 2), i int);
+CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + 2), i int);
ALTER TABLE t ATTACH PARTITION tp_1 FOR VALUES FROM (-1) TO (10);
ALTER TABLE t ATTACH PARTITION tp_2 FOR VALUES FROM (10) TO (20);
ALTER TABLE t ADD CHECK (g > 0);
@@ -1073,7 +1073,7 @@ INSERT INTO t VALUES (16);
-- ERROR: new row for relation "tp_12" violates check constraint "t_i_check"
INSERT INTO t VALUES (0);
ERROR: new row for relation "tp_12" violates check constraint "t_i_check"
-DETAIL: Failing row contains (0, virtual).
+DETAIL: Failing row contains (0, 2).
-- Should be 3 rows: (5), (15), (16):
SELECT i FROM t ORDER BY i;
i
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index e750866d2d8..c8e6c749992 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);
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index a211fee2ad1..df4239718cb 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -762,9 +762,9 @@ DROP TABLE t;
-- Test for generated columns (different order of columns in partitioned table
-- and partitions).
-CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + tableoid::int)) PARTITION BY RANGE (i);
-CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
-CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + tableoid::int), i int);
+CREATE TABLE t (i int, g int GENERATED ALWAYS AS (i + 2)) PARTITION BY RANGE (i);
+CREATE TABLE tp_1 (g int GENERATED ALWAYS AS (i + 2), i int);
+CREATE TABLE tp_2 (g int GENERATED ALWAYS AS (i + 2), i int);
ALTER TABLE t ATTACH PARTITION tp_1 FOR VALUES FROM (-1) TO (10);
ALTER TABLE t ATTACH PARTITION tp_2 FOR VALUES FROM (10) TO (20);
ALTER TABLE t ADD CHECK (g > 0);
--
2.52.0
view thread (3+ messages)
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], [email protected], [email protected]
Subject: Re: Show expression of virtual columns in error messages
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