Hi,
I received an offlist review from Andrew and he found a behaviour
regression in COPY. With v7, COPY FROM no longer enforces a CHECK
constraint on a virtual generated column, while INSERT still does:
CREATE TABLE cpy (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL
CHECK (b < 10));
INSERT INTO cpy VALUES (100); -- fails, as it should
COPY cpy (a) FROM PROGRAM 'printf "100\n"';
On master the COPY fails with the check constraint violation, but with
v7 it succeeds and the table contains (100, 200). The reason is that v7
removed the expand_generated_columns_in_expr() call from ExecRelCheck()
and relied on ExecComputeVirtualGenerated() having filled the slot
before, but CopyFrom() never calls it, so the check expression sees
NULL for the virtual column and passes. He also noticed that a dump and
restore of the regression database fails with v7 on "gtest20c", which
has the same cause: the whole-row check (gtest20c IS NOT NULL) sees the
virtual column as NULL during COPY and rejects valid rows.
While fixing this I found some other issues with the v7 approach of
storing the computed values directly in the input slot. Depending on
whether the slot was already materialized, the computed values could end
up written to disk, and on UPDATE, v7 only computed the virtual columns
that depend on the updated columns, so a whole-row check failed on
UPDATE t SET c = 2 for a perfectly valid row, since the other virtual
columns were NULL in the slot.
The attached v8 changes the approach. The virtual generated columns are
now computed inside ExecConstraints() into a separate slot, and the
constraints are checked against that slot. The input slot is never
modified, so nothing reaches the disk, and since every caller goes
through ExecConstraints() this covers INSERT, UPDATE, ON CONFLICT,
MERGE, COPY FROM and logical replication apply. Following Tom's
suggestion, the values are computed even if there are no constraints, so
a row whose virtual column values cannot be computed is rejected on
write.
ExecBuildSlotValueDescription() shows "virtual" again by default, since
some callers (e.g. logical replication conflicts) build the description
from tuples read from disk, where the virtual columns are NULL. Only the
check and not-null violation messages, which use the computed slot, show
the actual values.
I've notice some consequences of computing the virtual columns on every
write that I think are worth sharing:
1. ALTER TABLE ... ADD COLUMN ... VIRTUAL and ALTER COLUMN ... SET
EXPRESSION don't evaluate the new expression against existing rows, so
a table can still have rows whose virtual column values cannot be
computed:
CREATE TABLE t1 (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO t1 VALUES (1000000000);
ALTER TABLE t1 ALTER COLUMN b SET EXPRESSION AS (a * 10);
pg_dump works fine, but restoring the dump fails with "integer out of
range" on the COPY and the table is restored empty. On master the
restore works and the error only happens on SELECT. The same applies to
the initial table synchronization of logical replication, as if the
subscriber table has a virtual column that cannot be computed for some
publisher row, the table sync fails and keeps retrying.
2. On logical replication apply, if a virtual column that only exists
on the subscriber cannot be computed, the apply worker now errors out,
even if there is no constraint on the table.
3. All virtual columns are computed at the start of ExecConstraints(),
so an error evaluating a generation expression now takes precedence
over a not-null violation on a regular column, a check violation, a
unique violation or a "no partition found" error. For example, a row
with a NULL in a NOT NULL column and an overflowing virtual column now
reports "integer out of range".
4. With INSERT ... ON CONFLICT, the error is raised even if the row
conflicts and would never be stored, as already happens with check
constraints.
Most of these consequences stem from virtual generated columns being
computed on the write path, which risks breaking existing workflows.
While I think that this is a valuable feature, I'm wondering if it's
still worth pursuing in this form. Thoughts?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 2ae1c05c2815c9a25621c6985e70295ad476dde7 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Fri, 28 Aug 2026 08:14:30 -0300
Subject: [PATCH v8] Compute virtual generated columns during INSERT/UPDATE
Previously, virtual generated column values were never computed during
INSERT/UPDATE. Instead, references to virtual columns in check
constraints were expanded to their generation expressions, and not-null
constraints on virtual columns were checked by evaluating a separate
"expression IS NOT NULL" test. This meant that a row could be stored
even if the values of its virtual columns could not be computed (e.g.
because of an overflow), and the error would only be raised when the row
was read.
This commit changes ExecConstraints() to compute the values of all
virtual generated columns of the new row into a separate slot, and to
check the constraints against that slot. This is done even if there are
no constraints, so that a row whose virtual column values cannot be
computed is rejected on write. The input slot is not modified, so
virtual generated columns are still stored as nulls. Since this is done
in ExecConstraints(), every caller is covered, including COPY FROM and
logical replication apply. Foreign tables are not affected, since
constraints are not checked for them.
This also has two benefits. Each generation expression is now evaluated
once per row, instead of once for each check constraint that references
it. And error messages for check and not-null constraint violations now
display the computed value instead of the "virtual" string.
Discussion:
https://www.postgresql.org/message-id/DG5DV8SED62G.2WFJB46D7WIT8%40gmail.com
---
doc/src/sgml/ddl.sgml | 7 +-
doc/src/sgml/ref/create_table.sgml | 5 +-
src/backend/executor/execMain.c | 177 ++++++++++++++----
src/include/nodes/execnodes.h | 7 +
.../regress/expected/generated_virtual.out | 40 ++--
src/test/regress/sql/generated_virtual.sql | 21 ++-
6 files changed, 205 insertions(+), 52 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index d78d986756e..6264b62382c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -357,9 +357,10 @@ 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
+ as if it were a normal column. A virtual generated column is also computed
+ when a row is inserted or updated, but it occupies no storage and is
+ recomputed every time 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).
</para>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index fef24d8f3a2..8795a9a7283 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -933,8 +933,9 @@ 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 a row is inserted or updated and every time it is 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/execMain.c b/src/backend/executor/execMain.c
index d7a8f36ca1d..4902e77127e 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -94,6 +94,14 @@ static void EvalPlanQualStart(EPQState *epqstate, Plan
*planTree);
static void ReportNotNullViolationError(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot,
EState *estate, int attnum);
+static TupleTableSlot *ExecComputeVirtualGenerated(ResultRelInfo
*resultRelInfo,
+
TupleTableSlot *slot,
+
EState *estate);
+static char *BuildSlotValueDescription(Oid reloid, TupleTableSlot *slot,
+
TupleDesc tupdesc,
+
Bitmapset *modifiedCols,
+ int
maxfieldlen,
+ bool
virtual_computed);
/* end of local decls */
@@ -1335,6 +1343,8 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_GenVirtualNotNullConstraintExprs = NULL;
resultRelInfo->ri_GeneratedExprsI = NULL;
resultRelInfo->ri_GeneratedExprsU = NULL;
+ resultRelInfo->ri_VirtualGeneratedExprs = NULL;
+ resultRelInfo->ri_VirtualGeneratedSlot = NULL;
resultRelInfo->ri_projectReturning = NULL;
resultRelInfo->ri_onConflictArbiterIndexes = NIL;
resultRelInfo->ri_onConflict = NULL;
@@ -1828,6 +1838,87 @@ ExecutePlan(QueryDesc *queryDesc,
}
+/*
+ * ExecComputeVirtualGenerated --- compute virtual generated columns of a tuple
+ *
+ * Returns a slot holding a copy of the tuple in 'slot' in which the virtual
+ * generated columns contain their computed values. 'slot' itself is not
+ * modified, since virtual generated columns are always stored as nulls.
+ *
+ * The returned slot's tuple descriptor doesn't mark any column as generated,
+ * so expressions evaluated against it can read the computed values directly
+ * instead of expanding the generation expressions. The values are allocated
+ * in the per-tuple memory context, so the slot is valid only until that
+ * context is reset.
+ */
+static TupleTableSlot *
+ExecComputeVirtualGenerated(ResultRelInfo *resultRelInfo,
+ TupleTableSlot *slot,
EState *estate)
+{
+ Relation rel = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ TupleTableSlot *vslot;
+ ExprContext *econtext;
+
+ Assert(tupdesc->constr && tupdesc->constr->has_generated_virtual);
+
+ /*
+ * If first time through for this result relation, build expression
+ * nodetrees for the generation expressions and the slot that will hold
+ * the computed values. Keep them in the per-query memory context so
+ * they'll survive throughout the query.
+ */
+ if (resultRelInfo->ri_VirtualGeneratedExprs == NULL)
+ {
+ MemoryContext oldContext;
+
+ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+ resultRelInfo->ri_VirtualGeneratedExprs =
palloc0_array(ExprState *, natts);
+ for (int i = 0; i < natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ resultRelInfo->ri_VirtualGeneratedExprs[i] =
+ ExecPrepareExpr((Expr *)
build_generation_expression(rel, i + 1),
+ estate);
+ }
+
+ /* CreateTupleDescCopy() resets attgenerated */
+ resultRelInfo->ri_VirtualGeneratedSlot =
+ ExecInitExtraTupleSlot(estate,
CreateTupleDescCopy(tupdesc),
+
&TTSOpsVirtual);
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ vslot = resultRelInfo->ri_VirtualGeneratedSlot;
+
+ /* Copy the input tuple, then fill in the virtual generated columns */
+ slot_getallattrs(slot);
+ ExecClearTuple(vslot);
+ memcpy(vslot->tts_values, slot->tts_values, natts * sizeof(Datum));
+ memcpy(vslot->tts_isnull, slot->tts_isnull, natts * sizeof(bool));
+
+ /* The generation expressions are evaluated against the input tuple */
+ econtext = GetPerTupleExprContext(estate);
+ econtext->ecxt_scantuple = slot;
+
+ for (int i = 0; i < natts; i++)
+ {
+ ExprState *genexpr =
resultRelInfo->ri_VirtualGeneratedExprs[i];
+
+ if (genexpr)
+ vslot->tts_values[i] =
ExecEvalExprSwitchContext(genexpr, econtext,
+
&vslot->tts_isnull[i]);
+ }
+
+ ExecStoreVirtualTuple(vslot);
+ vslot->tts_tableOid = slot->tts_tableOid;
+
+ return vslot;
+}
+
/*
* ExecRelCheck --- check that tuple meets check constraints for result
relation
*
@@ -1869,8 +1960,12 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
if (!check[i].ccenforced)
continue;
+ /*
+ * No need to expand virtual generated columns here:
the caller
+ * passes a slot in which they have already been
computed by
+ * ExecComputeVirtualGenerated().
+ */
checkconstr = stringToNode(check[i].ccbin);
- checkconstr = (Expr *)
expand_generated_columns_in_expr((Node *) checkconstr, rel, 1);
resultRelInfo->ri_CheckConstraintExprs[i] =
ExecPrepareExpr(checkconstr, estate);
}
@@ -2043,15 +2138,22 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
TupleDesc tupdesc = RelationGetDescr(rel);
TupleConstr *constr = tupdesc->constr;
Bitmapset *modifiedCols;
- List *notnull_virtual_attrs = NIL;
Assert(constr); /* we should not be called
otherwise */
+ /*
+ * Compute virtual generated columns, so that constraints can check
their
+ * values and error messages can show them. We do this even if there
are
+ * no constraints, to make sure that the values can be computed,
otherwise
+ * we could store a row that cannot be read back. From here on, 'slot'
is a
+ * copy of the input tuple with those columns filled in; the input slot
is
+ * not modified, since virtual generated columns must not be stored.
+ */
+ if (constr->has_generated_virtual)
+ slot = ExecComputeVirtualGenerated(resultRelInfo, slot, estate);
+
/*
* Verify not-null constraints.
- *
- * Not-null constraints on virtual generated columns are collected and
- * checked separately below.
*/
if (constr->has_not_null)
{
@@ -2059,26 +2161,11 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attnum -
1);
- if (att->attnotnull && att->attgenerated ==
ATTRIBUTE_GENERATED_VIRTUAL)
- notnull_virtual_attrs =
lappend_int(notnull_virtual_attrs, attnum);
- else if (att->attnotnull && slot_attisnull(slot,
attnum))
+ if (att->attnotnull && slot_attisnull(slot, attnum))
ReportNotNullViolationError(resultRelInfo,
slot, estate, attnum);
}
}
- /*
- * Verify not-null constraints on virtual generated column, if any.
- */
- if (notnull_virtual_attrs)
- {
- AttrNumber attnum;
-
- attnum = ExecRelGenVirtualNotNull(resultRelInfo, slot, estate,
-
notnull_virtual_attrs);
- if (attnum != InvalidAttrNumber)
- ReportNotNullViolationError(resultRelInfo, slot,
estate, attnum);
- }
-
/*
* Verify check constraints.
*/
@@ -2123,11 +2210,12 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
else
modifiedCols =
bms_union(ExecGetInsertedCols(resultRelInfo, estate),
ExecGetUpdatedCols(resultRelInfo, estate));
- val_desc =
ExecBuildSlotValueDescription(RelationGetRelid(rel),
-
slot,
-
tupdesc,
-
modifiedCols,
-
64);
+ val_desc =
BuildSlotValueDescription(RelationGetRelid(rel),
+
slot,
+
tupdesc,
+
modifiedCols,
+
64,
+
true);
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg("new row for relation \"%s\"
violates check constraint \"%s\"",
@@ -2213,6 +2301,9 @@ ExecRelGenVirtualNotNull(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot,
/*
* Report a violation of a not-null constraint that was already detected.
+ *
+ * 'slot' must have its virtual generated columns, if any, computed by
+ * ExecComputeVirtualGenerated().
*/
static void
ReportNotNullViolationError(ResultRelInfo *resultRelInfo, TupleTableSlot *slot,
@@ -2260,11 +2351,12 @@ ReportNotNullViolationError(ResultRelInfo
*resultRelInfo, TupleTableSlot *slot,
modifiedCols = bms_union(ExecGetInsertedCols(resultRelInfo,
estate),
ExecGetUpdatedCols(resultRelInfo, estate));
- val_desc = ExecBuildSlotValueDescription(RelationGetRelid(rel),
-
slot,
-
tupdesc,
-
modifiedCols,
-
64);
+ val_desc = BuildSlotValueDescription(RelationGetRelid(rel),
+
slot,
+
tupdesc,
+
modifiedCols,
+
64,
+
true);
ereport(ERROR,
errcode(ERRCODE_NOT_NULL_VIOLATION),
errmsg("null value in column \"%s\" of relation \"%s\"
violates not-null constraint",
@@ -2445,6 +2537,9 @@ ExecWithCheckOptions(WCOKind kind, ResultRelInfo
*resultRelInfo,
* BuildIndexValueDescription, if the user has access to view a subset of the
* column involved, that subset will be returned with a key identifying which
* columns they are.
+ *
+ * Virtual generated columns are shown as "virtual", since their values are
+ * not stored in the tuple.
*/
char *
ExecBuildSlotValueDescription(Oid reloid,
@@ -2452,6 +2547,23 @@ ExecBuildSlotValueDescription(Oid reloid,
TupleDesc tupdesc,
Bitmapset
*modifiedCols,
int maxfieldlen)
+{
+ return BuildSlotValueDescription(reloid, slot, tupdesc, modifiedCols,
+
maxfieldlen, false);
+}
+
+/*
+ * Workhorse for ExecBuildSlotValueDescription. If virtual_computed is true,
+ * the slot is one returned by ExecComputeVirtualGenerated() (or converted from
+ * one), so the values of virtual generated columns are shown.
+ */
+static char *
+BuildSlotValueDescription(Oid reloid,
+ TupleTableSlot *slot,
+ TupleDesc tupdesc,
+ Bitmapset *modifiedCols,
+ int maxfieldlen,
+ bool virtual_computed)
{
StringInfoData buf;
StringInfoData collist;
@@ -2531,7 +2643,8 @@ ExecBuildSlotValueDescription(Oid reloid,
if (table_perm || column_perm)
{
- if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
+ !virtual_computed)
val = "virtual";
else if (slot->tts_isnull[i])
val = "null";
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e3cea52e7df..0b7cdec90e1 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -584,6 +584,13 @@ typedef struct ResultRelInfo
int ri_NumGeneratedNeededI;
int ri_NumGeneratedNeededU;
+ /*
+ * Array of virtual generated columns ExprStates, and a slot holding the
+ * new tuple with those columns computed, used by ExecConstraints().
+ */
+ ExprState **ri_VirtualGeneratedExprs;
+ TupleTableSlot *ri_VirtualGeneratedSlot;
+
/* 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 b2f8e16e63b..37e0bb5313d 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,11 @@ 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).
+COPY gtest20 (a) FROM stdin; -- violates constraint
+ERROR: new row for relation "gtest20" violates check constraint
"gtest20_b_check"
+DETAIL: Failing row contains (30, 60).
+CONTEXT: COPY gtest20, line 1: "30"
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,9 +695,23 @@ 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).
+COPY gtest20c FROM stdin; -- ok
+COPY gtest20c FROM stdin; -- fails
+ERROR: new row for relation "gtest20c" violates check constraint
"whole_row_check"
+DETAIL: Failing row contains (null, null).
+CONTEXT: COPY gtest20c, line 1: "\N"
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
+-- UPDATE that doesn't change the columns a virtual column depends on
+CREATE TABLE gtest20e (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c
int);
+ALTER TABLE gtest20e ADD CONSTRAINT whole_row_check CHECK (gtest20e IS NOT
NULL);
+ALTER TABLE gtest20e ADD CONSTRAINT b_check CHECK (b < 10);
+INSERT INTO gtest20e (a, c) VALUES (1, 1);
+UPDATE gtest20e SET c = 2; -- ok
+UPDATE gtest20e SET a = 10; -- violates constraint
+ERROR: new row for relation "gtest20e" violates check constraint "b_check"
+DETAIL: Failing row contains (10, 20, 2).
-- index with whole-row reference needs rebuild
CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20d VALUES (1), (1);
@@ -708,12 +724,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 +739,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 +806,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 c4c4f3d5ef1..9ef81756856 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);
@@ -323,6 +321,9 @@ DROP USER regress_user11;
CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
VIRTUAL CHECK (b < 50));
INSERT INTO gtest20 (a) VALUES (10); -- ok
INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
+COPY gtest20 (a) FROM stdin; -- violates constraint
+30
+\.
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates
constraint
ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
@@ -351,8 +352,22 @@ CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a
* 2) VIRTUAL);
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
+COPY gtest20c FROM stdin; -- ok
+2
+\.
+COPY gtest20c FROM stdin; -- fails
+\N
+\.
ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); --
violates constraint
+-- UPDATE that doesn't change the columns a virtual column depends on
+CREATE TABLE gtest20e (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c
int);
+ALTER TABLE gtest20e ADD CONSTRAINT whole_row_check CHECK (gtest20e IS NOT
NULL);
+ALTER TABLE gtest20e ADD CONSTRAINT b_check CHECK (b < 10);
+INSERT INTO gtest20e (a, c) VALUES (1, 1);
+UPDATE gtest20e SET c = 2; -- ok
+UPDATE gtest20e SET a = 10; -- violates constraint
+
-- index with whole-row reference needs rebuild
CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20d VALUES (1), (1);
--
2.50.1 (Apple Git-155)
