hi. Virtual generated columns committed, https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b
This patch is for implementing not null constraints on virtual generated columns. NOT NULL constraints on virtual generated columns mean that if we INSERT a row into the table and the evaluation of the generated expression results in a null value, an ERRCODE_NOT_NULL_VIOLATION error will be reported. main gotcha is in ExecConstraints, expand the generated expression and convert a not null constraint to a check constraint and evaluate it.
From 945ee799beca62e76f128660aef658ef61a11a8a Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Mon, 10 Feb 2025 21:20:02 +0800 Subject: [PATCH v1 1/1] support virtual generated column not null constraint 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. discussion: https://postgr.es/m/ --- src/backend/catalog/heap.c | 10 -- src/backend/commands/tablecmds.c | 8 - src/backend/executor/execMain.c | 152 ++++++++++++++++++ src/backend/parser/parse_utilcmd.c | 14 -- src/include/nodes/execnodes.h | 2 + .../regress/expected/generated_virtual.out | 27 ++-- src/test/regress/sql/generated_virtual.sql | 12 +- 7 files changed, 172 insertions(+), 53 deletions(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 956f196fc9..2d42761c91 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2564,11 +2564,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 @@ -2884,11 +2879,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/tablecmds.c b/src/backend/commands/tablecmds.c index 5823fce934..d672d1b060 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7803,14 +7803,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 39d80ccfba..70eb2370bb 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" @@ -1268,6 +1269,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_FdwState = NULL; resultRelInfo->ri_usesFdwDirectModify = false; resultRelInfo->ri_ConstraintExprs = NULL; + resultRelInfo->ri_VirGeneratedConstraintExprs = NULL; resultRelInfo->ri_GeneratedExprsI = NULL; resultRelInfo->ri_GeneratedExprsU = NULL; resultRelInfo->ri_projectReturning = NULL; @@ -1737,6 +1739,83 @@ ExecutePlan(QueryDesc *queryDesc, } +/* + * check whether the virtual generated column is null. + * Expand the generated expression and evaluate it. A return value of -1 + * indicates that the generated expression is not null, while a value greater + * than 0 signifies null. This is similar to ExecRelCheck. +*/ +static int +ExecRelCheckGenVirtualNotNull(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, + EState *estate, Bitmapset *gen_virtual_cols) +{ + Relation rel = resultRelInfo->ri_RelationDesc; + TupleDesc tupdesc = RelationGetDescr(rel); + ExprContext *econtext; + MemoryContext oldContext; + int i = 0; + int attidx = -1; + int *attnums; + attnums = (int *) palloc0(bms_num_members(gen_virtual_cols) * sizeof(int)); + + if (resultRelInfo->ri_VirGeneratedConstraintExprs == NULL) + { + oldContext = MemoryContextSwitchTo(estate->es_query_cxt); + resultRelInfo->ri_VirGeneratedConstraintExprs = + (ExprState **) palloc0(bms_num_members(gen_virtual_cols) * sizeof(ExprState *)); + + while ((attidx = bms_next_member(gen_virtual_cols, attidx)) >= 0) + { + Expr *checkconstr; + NullTest *nnulltest = makeNode(NullTest); + Form_pg_attribute attr = TupleDescAttr(tupdesc, attidx -1); + + nnulltest->arg = (Expr *) makeVar(1, + attr->attnum, + attr->atttypid, + attr->atttypmod, + attr->attcollation, + 0); + nnulltest->nulltesttype = IS_NOT_NULL; + nnulltest->argisrow = false; + nnulltest->location = -1; + + checkconstr = (Expr *) nnulltest; + checkconstr = (Expr *) expand_generated_columns_in_expr((Node *) checkconstr, rel, 1); + resultRelInfo->ri_VirGeneratedConstraintExprs[i] = ExecPrepareExpr(checkconstr, 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 check 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 < bms_num_members(gen_virtual_cols); i++) + { + ExprState *gen_virtualnn = resultRelInfo->ri_VirGeneratedConstraintExprs[i]; + + if (gen_virtualnn && !ExecCheck(gen_virtualnn, econtext)) + return attnums[i]; + } + + /* -1 result means no error */ + return -1; +} + /* * ExecRelCheck --- check that tuple meets constraints for result relation * @@ -1972,6 +2051,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo, Relation orig_rel = rel; TupleDesc orig_tupdesc = RelationGetDescr(rel); + if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + continue; /* * If the tuple has been routed, it's been converted to the * partition's rowtype, which might differ from the root @@ -2021,6 +2102,77 @@ ExecConstraints(ResultRelInfo *resultRelInfo, } } + /* check virtual generated column is not null or not */ + if (constr->has_not_null && constr->has_generated_virtual) + { + int attnum; + Bitmapset *vir_gencols = NULL; + char *val_desc = NULL; + Form_pg_attribute att; + + for (attnum = 1; attnum <= tupdesc->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; + + if(vir_gencols != NULL) + { + Relation orig_rel_nn = rel; + + attnum = ExecRelCheckGenVirtualNotNull(resultRelInfo, slot, estate, vir_gencols); + + if (attnum > 0) + { + /* null value on virtual generated column. now format the error message */ + if (resultRelInfo->ri_RootResultRelInfo) + { + ResultRelInfo *rootrel = resultRelInfo->ri_RootResultRelInfo; + TupleDesc old_tupdesc = RelationGetDescr(rel); + AttrMap *map; + + tupdesc = RelationGetDescr(rootrel->ri_RelationDesc); + /* a reverse map */ + map = build_attrmap_by_name_if_req(old_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); + + att = TupleDescAttr(tupdesc, attnum - 1); + + 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_nn)), + val_desc ? errdetail("Failing row contains %s.", val_desc) : 0, + errtablecol(orig_rel_nn, 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 eb7716cd84..ba309e47f0 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 e2d1dc1e06..1595fae215 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -548,6 +548,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_VirGeneratedConstraintExprs; /* * 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 35638812be..826c76b900 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -660,28 +660,25 @@ 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 +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 DROP TABLE gtest21ax; CREATE TABLE gtest21b (a int PRIMARY KEY, 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). 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 -- 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 @@ -689,7 +686,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: unique constraints on virtual generated columns are not supported --INSERT INTO gtest22b VALUES (2); --INSERT INTO gtest22b VALUES (2); -- indexes @@ -734,7 +731,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: unique constraints 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 3487081391..1ac92b2204 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -332,23 +332,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 --- 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 +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 DROP TABLE gtest21ax; CREATE TABLE gtest21b (a int PRIMARY KEY, 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 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 -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); -- 2.34.1
