From ea676b927f0d74c1dde6237cd2f5b613af44053d Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 6 Mar 2025 12:01:31 +0800 Subject: [PATCH v2 1/3] not null for virtual generated column now we can add not null constraint on virtual generated column. not null constraint on virtual generated column make sure evaulation of the expanded generated expression does not yield null. the virtual generated columns does not store value, the storage is null. discussion: https://postgr.es/m/CACJufxHArQysbDkWFmvK+D1TPHQWWTxWN15cMuUaTYX3xhQXgg@mail.gmail.com --- src/backend/catalog/heap.c | 10 - src/backend/commands/indexcmds.c | 10 +- src/backend/commands/tablecmds.c | 8 - src/backend/executor/execMain.c | 234 ++++++++++++++---- src/backend/parser/parse_utilcmd.c | 14 -- src/include/nodes/execnodes.h | 2 + .../regress/expected/generated_virtual.out | 54 ++-- src/test/regress/sql/generated_virtual.sql | 31 ++- 8 files changed, 247 insertions(+), 116 deletions(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index bd3554c0bfd..b807ab66668 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2615,11 +2615,6 @@ AddRelationNewConstraints(Relation rel, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot add not-null constraint on system column \"%s\"", strVal(linitial(cdef->keys)))); - /* TODO: see transformColumnDefinition() */ - if (get_attgenerated(RelationGetRelid(rel), colnum) == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("not-null constraints are not supported on virtual generated columns")); /* * If the column already has a not-null constraint, we don't want @@ -2935,11 +2930,6 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot add not-null constraint on system column \"%s\"", strVal(linitial(constr->keys)))); - /* TODO: see transformColumnDefinition() */ - if (get_attgenerated(RelationGetRelid(rel), attnum) == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("not-null constraints are not supported on virtual generated columns")); /* * A column can only have one not-null constraint, so discard any diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 32ff3ca9a28..e690487eb3e 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1126,10 +1126,12 @@ DefineIndex(Oid tableId, if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - stmt->isconstraint ? - errmsg("unique constraints on virtual generated columns are not supported") : - errmsg("indexes on virtual generated columns are not supported"))); + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + stmt->primary ? + errmsg("primary key on virtual generated columns are not supported") : + stmt->isconstraint ? + errmsg("unique constraints on virtual generated columns are not supported") : + errmsg("indexes on virtual generated columns are not supported")); } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 59156a1c1f6..c89f1cb9936 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7824,14 +7824,6 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, errmsg("cannot alter system column \"%s\"", colName))); - /* TODO: see transformColumnDefinition() */ - if (TupleDescAttr(RelationGetDescr(rel), attnum - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("not-null constraints are not supported on virtual generated columns"), - errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.", - colName, RelationGetRelationName(rel)))); - /* See if there's already a constraint */ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); if (HeapTupleIsValid(tuple)) diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 0493b7d5365..5f0b0cc3b8e 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -51,6 +51,7 @@ #include "foreign/fdwapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" +#include "nodes/makefuncs.h" #include "nodes/queryjumble.h" #include "parser/parse_relation.h" #include "pgstat.h" @@ -1372,6 +1373,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_FdwState = NULL; resultRelInfo->ri_usesFdwDirectModify = false; resultRelInfo->ri_ConstraintExprs = NULL; + resultRelInfo->ri_GeneratedNotNullExprs = NULL; resultRelInfo->ri_GeneratedExprsI = NULL; resultRelInfo->ri_GeneratedExprsU = NULL; resultRelInfo->ri_projectReturning = NULL; @@ -1841,6 +1843,78 @@ ExecutePlan(QueryDesc *queryDesc, } +/* + * we warp "col IS NOT NULL" to a NullTest node. NullTest->arg is the virtual + * generated expression. + * A return value of -1 means virtual generated not null constraint satisfied, + * return value > 0 means not null violation happend in that attribute number. + * + * gen_virtual_cols is the attribute numbers of virtual generated column. +*/ +static int +ExecRelCheckGenVirtualNotNull(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, + EState *estate, Bitmapset *gen_virtual_cols) +{ + Relation rel = resultRelInfo->ri_RelationDesc; + ExprContext *econtext; + MemoryContext oldContext; + int i = 0; + int attidx = -1; + int *attnums; + int cnt = bms_num_members(gen_virtual_cols); + + attnums = (int *) palloc0(cnt * sizeof(int)); + if (resultRelInfo->ri_GeneratedNotNullExprs == NULL) + { + oldContext = MemoryContextSwitchTo(estate->es_query_cxt); + resultRelInfo->ri_GeneratedNotNullExprs = + (ExprState **) palloc0(cnt * sizeof(ExprState *)); + + while ((attidx = bms_next_member(gen_virtual_cols, attidx)) >= 0) + { + Expr *expr; + NullTest *nnulltest; + + nnulltest = makeNode(NullTest); + nnulltest->arg = (Expr *) build_generation_expression(rel, attidx); + nnulltest->nulltesttype = IS_NOT_NULL; + nnulltest->argisrow = false; + nnulltest->location = -1; + + expr = (Expr *) nnulltest; + resultRelInfo->ri_GeneratedNotNullExprs[i] = ExecPrepareExpr(expr, estate); + attnums[i++] = attidx; + } + MemoryContextSwitchTo(oldContext); + } + else + { + while ((attidx = bms_next_member(gen_virtual_cols, attidx)) >= 0) + attnums[i++] = attidx; + } + + /* + * We will use the EState's per-tuple context for evaluating virtual + * generated column not null constraint expressions (creating it if it's not + * already there). + */ + econtext = GetPerTupleExprContext(estate); + + /* Arrange for econtext's scan tuple to be the tuple under test */ + econtext->ecxt_scantuple = slot; + + /* And evaluate the check constraints for virtual generated column */ + for (i = 0; i < cnt; i++) + { + ExprState *exprstate = resultRelInfo->ri_GeneratedNotNullExprs[i]; + if (exprstate && !ExecCheck(exprstate, econtext)) + return attnums[i]; + } + + /* -1 result means no error */ + return -1; +} + /* * ExecRelCheck --- check that tuple meets constraints for result relation * @@ -2039,6 +2113,70 @@ ExecPartitionCheckEmitError(ResultRelInfo *resultRelInfo, errtable(resultRelInfo->ri_RelationDesc))); } +/* not null constraint violation happened, now format the error message */ +static void +NotNullViolationErrorReport(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, + EState *estate, int attrChk) +{ + Bitmapset *modifiedCols; + char *val_desc; + + Relation rel = resultRelInfo->ri_RelationDesc; + Relation orig_rel = rel; + + TupleDesc tupdesc = RelationGetDescr(rel); + TupleDesc orig_tupdesc = RelationGetDescr(rel); + Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1); + + Assert(attrChk > 0); + + /* + * If the tuple has been routed, it's been converted to the + * partition's rowtype, which might differ from the root + * table's. We must convert it back to the root table's + * rowtype so that val_desc shown error message matches the + * input tuple. + */ + if (resultRelInfo->ri_RootResultRelInfo) + { + ResultRelInfo *rootrel = resultRelInfo->ri_RootResultRelInfo; + AttrMap *map; + + tupdesc = RelationGetDescr(rootrel->ri_RelationDesc); + /* a reverse map */ + map = build_attrmap_by_name_if_req(orig_tupdesc, + tupdesc, + false); + + /* + * Partition-specific slot's tupdesc can't be changed, so + * allocate a new one. + */ + if (map != NULL) + slot = execute_attr_map_slot(map, slot, + MakeTupleTableSlot(tupdesc, &TTSOpsVirtual)); + modifiedCols = bms_union(ExecGetInsertedCols(rootrel, estate), + ExecGetUpdatedCols(rootrel, estate)); + rel = rootrel->ri_RelationDesc; + } + else + modifiedCols = bms_union(ExecGetInsertedCols(resultRelInfo, estate), + ExecGetUpdatedCols(resultRelInfo, estate)); + + val_desc = ExecBuildSlotValueDescription(RelationGetRelid(rel), + slot, + tupdesc, + modifiedCols, + 64); + ereport(ERROR, + errcode(ERRCODE_NOT_NULL_VIOLATION), + errmsg("null value in column \"%s\" of relation \"%s\" violates not-null constraint", + NameStr(att->attname), + RelationGetRelationName(orig_rel)), + val_desc ? errdetail("Failing row contains %s.", val_desc) : 0, + errtablecol(orig_rel, attrChk)); +} + /* * ExecConstraints - check constraints of the tuple in 'slot' * @@ -2058,73 +2196,63 @@ ExecConstraints(ResultRelInfo *resultRelInfo, TupleDesc tupdesc = RelationGetDescr(rel); TupleConstr *constr = tupdesc->constr; Bitmapset *modifiedCols; + bool virtual_notnull = false; + Form_pg_attribute att; + int natts; + int attnum; Assert(constr); /* we should not be called otherwise */ + natts = tupdesc->natts; if (constr->has_not_null) { - int natts = tupdesc->natts; - int attrChk; - - for (attrChk = 1; attrChk <= natts; attrChk++) + for (attnum = 1; attnum <= natts; attnum++) { - Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1); + att = TupleDescAttr(tupdesc, attnum - 1); - if (att->attnotnull && slot_attisnull(slot, attrChk)) + if (att->attnotnull && slot_attisnull(slot, attnum)) { - char *val_desc; - Relation orig_rel = rel; - TupleDesc orig_tupdesc = RelationGetDescr(rel); - - /* - * If the tuple has been routed, it's been converted to the - * partition's rowtype, which might differ from the root - * table's. We must convert it back to the root table's - * rowtype so that val_desc shown error message matches the - * input tuple. - */ - if (resultRelInfo->ri_RootResultRelInfo) + /* virtual generated column not null constraint handled below */ + if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) { - ResultRelInfo *rootrel = resultRelInfo->ri_RootResultRelInfo; - AttrMap *map; - - tupdesc = RelationGetDescr(rootrel->ri_RelationDesc); - /* a reverse map */ - map = build_attrmap_by_name_if_req(orig_tupdesc, - tupdesc, - false); - - /* - * Partition-specific slot's tupdesc can't be changed, so - * allocate a new one. - */ - if (map != NULL) - slot = execute_attr_map_slot(map, slot, - MakeTupleTableSlot(tupdesc, &TTSOpsVirtual)); - modifiedCols = bms_union(ExecGetInsertedCols(rootrel, estate), - ExecGetUpdatedCols(rootrel, estate)); - rel = rootrel->ri_RelationDesc; + if (!virtual_notnull) + virtual_notnull = true; + continue; } - else - modifiedCols = bms_union(ExecGetInsertedCols(resultRelInfo, estate), - ExecGetUpdatedCols(resultRelInfo, estate)); - val_desc = ExecBuildSlotValueDescription(RelationGetRelid(rel), - slot, - tupdesc, - modifiedCols, - 64); - - ereport(ERROR, - (errcode(ERRCODE_NOT_NULL_VIOLATION), - errmsg("null value in column \"%s\" of relation \"%s\" violates not-null constraint", - NameStr(att->attname), - RelationGetRelationName(orig_rel)), - val_desc ? errdetail("Failing row contains %s.", val_desc) : 0, - errtablecol(orig_rel, attrChk))); + NotNullViolationErrorReport(resultRelInfo, slot, estate, attnum); } } } + /* check virtual generated column not null constraint */ + if (virtual_notnull) + { + Bitmapset *vir_gencols = NULL; + + Assert(constr->has_not_null); + Assert(constr->has_generated_virtual); + + for (attnum = 1; attnum <= natts; attnum++) + { + att = TupleDescAttr(tupdesc, attnum - 1); + if (att->attnotnull && att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + vir_gencols = bms_add_member(vir_gencols, att->attnum); + } + attnum = -1; + Assert(vir_gencols != NULL); + + attnum = ExecRelCheckGenVirtualNotNull(resultRelInfo, slot, estate, vir_gencols); + + /* constraint evaulation return falsem do error report, also make an Assert */ + if (attnum > 0) + { + att = TupleDescAttr(tupdesc, attnum - 1); + Assert(att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL); + + NotNullViolationErrorReport(resultRelInfo, slot, estate, attnum); + } + } + if (rel->rd_rel->relchecks > 0) { const char *failed; diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index abbe1bb45a3..7e9a5f7411b 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -988,20 +988,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); - - /* - * TODO: Straightforward not-null constraints won't work on virtual - * generated columns, because there is no support for expanding the - * column when the constraint is checked. Maybe we could convert the - * not-null constraint into a full check constraint, so that the - * generation expression can be expanded at check time. - */ - if (column->is_not_null && column->generated == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("not-null constraints are not supported on virtual generated columns"), - parser_errposition(cxt->pstate, - constraint->location))); } /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index a323fa98bbb..1c72243468c 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -549,6 +549,8 @@ typedef struct ResultRelInfo /* array of constraint-checking expr states */ ExprState **ri_ConstraintExprs; + /* array of virtual generated not null constraint-checking expr states */ + ExprState **ri_GeneratedNotNullExprs; /* * Arrays of stored generated columns ExprStates for INSERT/UPDATE/MERGE. */ diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 7ef05f45be7..3bd5130587c 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -664,28 +664,46 @@ 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). --- not-null constraints (currently not supported) CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL); -ERROR: not-null constraints are not supported on virtual generated columns -LINE 1: ... 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 +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). -- 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); -- error -ERROR: not-null constraints are not supported on virtual generated columns +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). +INSERT INTO gtest21ax (a) VALUES (1); --ok +DROP TABLE gtest21ax; CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL); -ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b; -- error -ERROR: not-null constraints are not supported on virtual generated columns +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). DROP TABLE gtest21ax; -CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL); +CREATE TABLE gtest21b (a int, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL); ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL; -ERROR: not-null constraints are not supported on virtual generated columns -DETAIL: Column "b" of relation "gtest21b" is a virtual generated column. ---INSERT INTO gtest21b (a) VALUES (1); -- ok ---INSERT INTO gtest21b (a) VALUES (0); -- violates constraint +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). +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). ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; ---INSERT INTO gtest21b (a) VALUES (0); -- ok now +INSERT INTO gtest21b (a) VALUES (0); -- ok now +--not null constraint with partitioned table case. +CREATE TABLE gtestnn_parent(f1 int, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL not null) PARTITION BY RANGE (f1); +CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5); +CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default; +--check the error message. +INSERT INTO gtestnn_parent VALUES (1, NULL); +ERROR: null value in column "f3" of relation "gtestnn_child" violates not-null constraint +DETAIL: Failing row contains (1, null, virtual). +INSERT INTO gtestnn_parent VALUES (5, NULL); +ERROR: null value in column "f3" of relation "gtestnn_childdef" violates not-null constraint +DETAIL: Failing row contains (5, null, virtual). -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); ERROR: unique constraints on virtual generated columns are not supported @@ -693,7 +711,7 @@ ERROR: unique constraints on virtual generated columns are not supported --INSERT INTO gtest22a VALUES (3); --INSERT INTO gtest22a VALUES (4); CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b)); -ERROR: not-null constraints are not supported on virtual generated columns +ERROR: primary key on virtual generated columns are not supported --INSERT INTO gtest22b VALUES (2); --INSERT INTO gtest22b VALUES (2); -- indexes @@ -738,7 +756,7 @@ ERROR: foreign key constraints on virtual generated columns are not supported --DROP TABLE gtest23b; --DROP TABLE gtest23a; CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y)); -ERROR: not-null constraints are not supported on virtual generated columns +ERROR: primary key on virtual generated columns are not supported --INSERT INTO gtest23p VALUES (1), (2), (3); CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y)); ERROR: relation "gtest23p" does not exist diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index dab8c92ef99..f436e8f2313 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -335,23 +335,36 @@ 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 --- not-null constraints (currently not supported) 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 +INSERT INTO gtest21a (a) VALUES (1); -- ok +INSERT INTO gtest21a (a) VALUES (0); -- violates constraint -- 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); -- error +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 +INSERT INTO gtest21ax (a) VALUES (1); --ok +DROP TABLE gtest21ax; CREATE TABLE gtest21ax (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL); -ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b; -- error +ALTER TABLE gtest21ax ADD CONSTRAINT cc NOT NULL b; +INSERT INTO gtest21ax (a) VALUES (0); -- violates constraint DROP TABLE gtest21ax; -CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL); +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 (0); -- violates constraint +INSERT INTO gtest21b (a) VALUES (1); -- ok +INSERT INTO gtest21b (a) VALUES (2), (0); -- violates constraint +INSERT INTO gtest21b (a) VALUES (NULL); -- error ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL; ---INSERT INTO gtest21b (a) VALUES (0); -- ok now +INSERT INTO gtest21b (a) VALUES (0); -- ok now + +--not null constraint with partitioned table case. +CREATE TABLE gtestnn_parent(f1 int, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL not null) PARTITION BY RANGE (f1); +CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5); +CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default; +--check the error message. +INSERT INTO gtestnn_parent VALUES (1, NULL); +INSERT INTO gtestnn_parent VALUES (5, NULL); + -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); -- 2.34.1