On Fri, Mar 28, 2025 at 10:06 PM Peter Eisentraut <pe...@eisentraut.org> wrote: > > On 24.03.25 04:26, jian he wrote: > > rebase, and some minor code comments change. > > I have committed this. > In an earlier thread, I also posted a patch for supporting virtual generated columns over domain type. The code is somehow similar, so I post the remaining patch to this thread.
v7-0001 we need to compute the generation expression for the domain with constraints, thus rename ExecComputeStoredGenerated to ExecComputeGenerated. v7-0002 soft error variant of ExecPrepareExpr, ExecInitExpr. for soft error processing of CoerceToDomain. we don't want error messages like "value for domain d2 violates check constraint "d2_check"" while validating existing domain data, we want something like: +ERROR: column "b" of table "gtest24" contains values that violate the new constraint v7-0003 supports virtual generation columns over domain type. If the domain has constraints then we need to compute the virtual generation expression, that happens mainly within ExecComputeGenerated. if a virtual generation column type is domain_with_constraint, then ALTER DOMAIN ADD CONSTRAINTS need to revalidate these virtual generation expressions again. so in validateDomainCheckConstraint, validateDomainNotNullConstraint We need to fetch the generation expression (build_generation_expression), compile the generation expression (ExecPrepareExprSafe), and evaluate it (ExecEvalExprSwitchContext). I also posted patch summary earlier at [1] [1] https://postgr.es/m/cacjufxht4r1oabzequvjb6dhrig7k-qsr6lee53q_nli9pf...@mail.gmail.com
From 346dbce94c008a1ec9447ca65c7b7fcd0f8428d6 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Mon, 31 Mar 2025 15:02:46 +0800 Subject: [PATCH v7 3/3] domain over virtual generated column. domains that don't have constraints work just fine. domain constraints check happen within ExecComputeGenerated. we compute the virtual generated column in ExecComputeGenerated and discarded the value. if value cannot coerce to domain then we will error out. domain_with_constaint can be element type of range, multirange, array, composite. to be bulletproof, if this column type is not type of TYPTYPE_BASE or it's an array type, then we do actually compute the generated expression. This can be have negative performance for INSERTs The following two query shows in pg_catalog, most of the system type is TYPTYPE_BASE. So I guess this compromise is fine. select count(*),typtype from pg_type where typnamespace = 11 group by 2; select typname, typrelid, pc.reltype, pc.oid, pt.oid from pg_type pt join pg_class pc on pc.oid = pt.typrelid where pt.typnamespace = 11 and pt.typtype = 'c' and pc.reltype = 0; ALTER DOMAIN ADD CONSTRAINT variant is supported. DOMAIN with default values are supported. but virtual generated column already have generated expression, so domain default expression doesn't matter. ALTER TABLE ADD VIRTUAL GENERATED COLUMN. need table scan to verify new column generation expression value satisfied the domain constraints. but no need table rewrite! discussion: https://postgr.es/m/cacjufxharqysbdkwfmvk+d1tphqwwtxwn15cmuuatyx3xhq...@mail.gmail.com --- src/backend/catalog/heap.c | 13 +- src/backend/commands/copyfrom.c | 5 +- src/backend/commands/tablecmds.c | 48 +++- src/backend/commands/typecmds.c | 209 +++++++++++++++--- src/backend/executor/nodeModifyTable.c | 88 ++++++-- src/include/catalog/heap.h | 1 - src/test/regress/expected/fast_default.out | 4 + .../regress/expected/generated_virtual.out | 65 +++++- src/test/regress/sql/fast_default.sql | 4 + src/test/regress/sql/generated_virtual.sql | 47 +++- 10 files changed, 398 insertions(+), 86 deletions(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index b807ab66668..5519690d85c 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -508,7 +508,7 @@ CheckAttributeNamesTypes(TupleDesc tupdesc, char relkind, TupleDescAttr(tupdesc, i)->atttypid, TupleDescAttr(tupdesc, i)->attcollation, NIL, /* assume we're creating a new rowtype */ - flags | (TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL ? CHKATYPE_IS_VIRTUAL : 0)); + flags); } } @@ -583,17 +583,6 @@ CheckAttributeType(const char *attname, } else if (att_typtype == TYPTYPE_DOMAIN) { - /* - * Prevent virtual generated columns from having a domain type. We - * would have to enforce domain constraints when columns underlying - * the generated column change. This could possibly be implemented, - * but it's not. - */ - if (flags & CHKATYPE_IS_VIRTUAL) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("virtual generated column \"%s\" cannot have a domain type", attname)); - /* * If it's a domain, recurse to check its base type. */ diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 906b6581e11..0086e4b2936 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1343,9 +1343,10 @@ CopyFrom(CopyFromState cstate) } else { - /* Compute stored generated columns */ + /* Compute generated columns */ if (resultRelInfo->ri_RelationDesc->rd_att->constr && - resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored) + (resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored || + resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_virtual)) ExecComputeGenerated(resultRelInfo, estate, myslot, CMD_INSERT); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 10624353b0a..4acae8d5568 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -5989,7 +5989,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, * rebuild data. */ if (tab->constraints != NIL || tab->verify_new_notnull || - tab->partition_constraint != NULL) + tab->newvals || tab->partition_constraint != NULL) ATRewriteTable(tab, InvalidOid); /* @@ -6109,6 +6109,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) BulkInsertState bistate; int ti_options; ExprState *partqualstate = NULL; + List *domain_virtual_attrs = NIL; /* * Open the relation(s). We have surely already locked the existing @@ -6184,6 +6185,16 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) /* expr already planned */ ex->exprstate = ExecInitExpr((Expr *) ex->expr, NULL); + if (tab->rewrite == 0 && ex->is_generated) + { + Form_pg_attribute attr = TupleDescAttr(newTupDesc, ex->attnum - 1); + if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && + DomainHasConstraints(attr->atttypid)) + { + Assert(!attr->attisdropped); + domain_virtual_attrs = lappend_int(domain_virtual_attrs, attr->attnum); + } + } } notnull_attrs = notnull_virtual_attrs = NIL; @@ -6215,6 +6226,13 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) needscan = true; } + /* + * We need verify domain constraints on the virtual generated column are + * satisfied, which requires table scan. + */ + if (domain_virtual_attrs != NIL) + needscan = true; + if (newrel || needscan) { ExprContext *econtext; @@ -6473,6 +6491,34 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) } } + /* + * domain_virtual_attrs is list of newly added columns that is virtual + * generated column over domain with constraints. we need to check + * whether these domain constraints are satisfied. + */ + if (domain_virtual_attrs != NIL) + { + Assert(tab->rewrite == 0); + + foreach(l, tab->newvals) + { + NewColumnValue *ex = lfirst(l); + + if (!ex->is_generated) + continue; + + if(list_member_int(domain_virtual_attrs, ex->attnum) && + !ExecCheck(ex->exprstate, econtext)) + { + Form_pg_attribute attr = TupleDescAttr(newTupDesc, ex->attnum - 1); + ereport(ERROR, + errcode(ERRCODE_CHECK_VIOLATION), + errmsg("value for domain %s is violated by some row", + format_type_be(attr->atttypid))); + } + } + } + if (partqualstate && !ExecCheck(partqualstate, econtext)) { if (tab->validate_default) diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 3cb3ca1cca1..59c1dda5618 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -65,6 +65,7 @@ #include "parser/parse_expr.h" #include "parser/parse_func.h" #include "parser/parse_type.h" +#include "rewrite/rewriteHandler.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/inval.h" @@ -2772,6 +2773,8 @@ AlterDomainNotNull(List *names, bool notNull) typTup->typbasetype, typTup->typtypmod, constr, NameStr(typTup->typname), NULL); + CommandCounterIncrement(); + validateDomainNotNullConstraint(domainoid); } else @@ -2974,7 +2977,12 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, * to. */ if (!constr->skip_validation) + { + /* need CCI, so we have fresh domain constraint info */ + CommandCounterIncrement(); + validateDomainCheckConstraint(domainoid, ccbin); + } /* * We must send out an sinval message for the domain, to ensure that @@ -2997,7 +3005,12 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, constr, NameStr(typTup->typname), constrAddr); if (!constr->skip_validation) + { + /* need CCI, so we have fresh domain constraint info */ + CommandCounterIncrement(); + validateDomainNotNullConstraint(domainoid); + } typTup->typnotnull = true; CatalogTupleUpdate(typrel, &tup->t_self, tup); @@ -3121,6 +3134,13 @@ validateDomainNotNullConstraint(Oid domainoid) List *rels; ListCell *rt; + EState *estate; + ExprContext *econtext; + + /* we need EState for domain over virtual generated column */ + estate = CreateExecutorState(); + econtext = GetPerTupleExprContext(estate); + /* Fetch relation list with attributes based on this domain */ /* ShareLock is sufficient to prevent concurrent data changes */ @@ -3134,6 +3154,47 @@ validateDomainNotNullConstraint(Oid domainoid) TupleTableSlot *slot; TableScanDesc scan; Snapshot snapshot; + Form_pg_attribute attr; + ExprState **ri_GeneratedExprs = NULL; + int attnum; + int count = 0; + + /* cacahe virtual generated columns handling */ + if (tupdesc->constr && tupdesc->constr->has_generated_virtual) + { + ri_GeneratedExprs = (ExprState **) palloc0(rtc->natts * sizeof(ExprState *)); + + /* + * We implement this by building a NullTest node for each virtual + * generated column, which we cache in ri_GeneratedExprs, and + * running those through ExecCheck() + */ + for (int i = 0; i < rtc->natts; i++) + { + attnum = rtc->atts[i]; + attr = TupleDescAttr(tupdesc, attnum - 1); + + if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + { + NullTest *nnulltest; + + nnulltest = makeNode(NullTest); + nnulltest->arg = (Expr *) build_generation_expression(testrel, attnum);; + nnulltest->nulltesttype = IS_NOT_NULL; + nnulltest->argisrow = false; + nnulltest->location = -1; + + ri_GeneratedExprs[i] = ExecPrepareExpr((Expr *) nnulltest, estate); + count++; + } + } + } + if (count == 0 && ri_GeneratedExprs != NULL) + { + pfree(ri_GeneratedExprs); + ri_GeneratedExprs = NULL; + } + /* Scan all tuples in this relation */ snapshot = RegisterSnapshot(GetLatestSnapshot()); @@ -3146,8 +3207,8 @@ validateDomainNotNullConstraint(Oid domainoid) /* Test attributes that are of the domain */ for (i = 0; i < rtc->natts; i++) { - int attnum = rtc->atts[i]; - Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1); + attnum = rtc->atts[i]; + attr = TupleDescAttr(tupdesc, attnum - 1); if (slot_attisnull(slot, attnum)) { @@ -3158,14 +3219,30 @@ validateDomainNotNullConstraint(Oid domainoid) * only executes in an ALTER DOMAIN command, the client * should already know which domain is in question. */ - ereport(ERROR, - (errcode(ERRCODE_NOT_NULL_VIOLATION), - errmsg("column \"%s\" of table \"%s\" contains null values", - NameStr(attr->attname), - RelationGetRelationName(testrel)), - errtablecol(testrel, attnum))); + if (attr->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL) + ereport(ERROR, + (errcode(ERRCODE_NOT_NULL_VIOLATION), + errmsg("column \"%s\" of table \"%s\" contains null values", + NameStr(attr->attname), + RelationGetRelationName(testrel)), + errtablecol(testrel, attnum))); + else + { + econtext->ecxt_scantuple = slot; + Assert(ri_GeneratedExprs[i] != NULL); + + if (!ExecCheck(ri_GeneratedExprs[i] , econtext)) + ereport(ERROR, + errcode(ERRCODE_NOT_NULL_VIOLATION), + errmsg("column \"%s\" of table \"%s\" contains null values", + NameStr(attr->attname), + RelationGetRelationName(testrel)), + errtablecol(testrel, attnum)); + } } } + + ResetExprContext(econtext); } ExecDropSingleTupleTableSlot(slot); table_endscan(scan); @@ -3174,6 +3251,8 @@ validateDomainNotNullConstraint(Oid domainoid) /* Close each rel after processing, but keep lock */ table_close(testrel, NoLock); } + + FreeExecutorState(estate); } /* @@ -3210,6 +3289,41 @@ validateDomainCheckConstraint(Oid domainoid, const char *ccbin) TupleTableSlot *slot; TableScanDesc scan; Snapshot snapshot; + ExprState **ri_GeneratedExprs = NULL; + Form_pg_attribute attr; + int attnum; + int count = 0; + + /* cacahe virtual generated columns handling */ + if (tupdesc->constr && tupdesc->constr->has_generated_virtual) + { + ri_GeneratedExprs = (ExprState **) palloc0(rtc->natts * sizeof(ExprState *)); + for (int i = 0; i < rtc->natts; i++) + { + Expr *defexpr; + + attnum = rtc->atts[i]; + attr = TupleDescAttr(tupdesc, attnum - 1); + if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + { + defexpr = (Expr *) build_generation_expression(testrel, attnum); + + /* + * we need evaluate defexpr error soft way, otherwise + * the evaulation failure error would be "value for domain + * violate check constraints", which is not helpful error + * message while validating domain check constraint. + */ + ri_GeneratedExprs[i] = ExecPrepareExprSafe(defexpr, estate); + count++; + } + } + } + if (count == 0 && ri_GeneratedExprs != NULL) + { + pfree(ri_GeneratedExprs); + ri_GeneratedExprs = NULL; + } /* Scan all tuples in this relation */ snapshot = RegisterSnapshot(GetLatestSnapshot()); @@ -3222,37 +3336,68 @@ validateDomainCheckConstraint(Oid domainoid, const char *ccbin) /* Test attributes that are of the domain */ for (i = 0; i < rtc->natts; i++) { - int attnum = rtc->atts[i]; Datum d; bool isNull; Datum conResult; - Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1); - d = slot_getattr(slot, attnum, &isNull); + attnum = rtc->atts[i]; + attr = TupleDescAttr(tupdesc, attnum - 1); - econtext->domainValue_datum = d; - econtext->domainValue_isNull = isNull; - - conResult = ExecEvalExprSwitchContext(exprstate, - econtext, - &isNull); - - if (!isNull && !DatumGetBool(conResult)) + if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) { /* - * In principle the auxiliary information for this error - * should be errdomainconstraint(), but errtablecol() - * seems considerably more useful in practice. Since this - * code only executes in an ALTER DOMAIN command, the - * client should already know which domain is in question, - * and which constraint too. - */ - ereport(ERROR, - (errcode(ERRCODE_CHECK_VIOLATION), - errmsg("column \"%s\" of table \"%s\" contains values that violate the new constraint", - NameStr(attr->attname), - RelationGetRelationName(testrel)), - errtablecol(testrel, attnum))); + * we'll use the EState's per-tuple context for evaluating + * domain check constraint associated with virtual generated + * column 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; + + conResult = ExecEvalExprSwitchContext(ri_GeneratedExprs[i], + econtext, + &isNull); + if (SOFT_ERROR_OCCURRED(ri_GeneratedExprs[i]->escontext)) + { + isNull = true; + ereport(ERROR, + errcode(ERRCODE_CHECK_VIOLATION), + errmsg("column \"%s\" of table \"%s\" contains values that violate the new constraint", + NameStr(attr->attname), + RelationGetRelationName(testrel)), + errtablecol(testrel, attnum)); + } + } + else + { + d = slot_getattr(slot, attnum, &isNull); + + econtext->domainValue_datum = d; + econtext->domainValue_isNull = isNull; + + conResult = ExecEvalExprSwitchContext(exprstate, + econtext, + &isNull); + + if (!isNull && !DatumGetBool(conResult)) + { + /* + * In principle the auxiliary information for this error + * should be errdomainconstraint(), but errtablecol() + * seems considerably more useful in practice. Since this + * code only executes in an ALTER DOMAIN command, the + * client should already know which domain is in question, + * and which constraint too. + */ + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("column \"%s\" of table \"%s\" contains values that violate the new constraint", + NameStr(attr->attname), + RelationGetRelationName(testrel)), + errtablecol(testrel, attnum))); + } } } diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 5a581446c7d..7424dcf2742 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -67,6 +67,7 @@ #include "storage/lmgr.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -395,7 +396,7 @@ ExecCheckTIDVisible(EState *estate, * * 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. + * This is used for stored and virtual generated columns. * * If cmdType == CMD_UPDATE, the ri_extraUpdatedCols field is filled too. * This is used by both stored and virtual generated columns. @@ -477,6 +478,33 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo, ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate); ri_NumGeneratedNeeded++; } + else + { + /* + * Virtual generated column only need to be computed when the + * type is domain_with_constraint. However, + * domain_with_constraint can be the element type of range, + * composite, or array. + * + * To determine whether the true element type is + * domain_with_constraint, multiple lookup_type_cache calls seems + * doable. However, this would add a lot of code. So simplify + * it: only when the type is TYPTYPE_BASE and not an array type, + * computation is not needed. + */ + Oid typelem = InvalidOid; + Oid atttypid = TupleDescAttr(tupdesc, i)->atttypid; + char att_typtype = get_typtype(atttypid); + + if (att_typtype == TYPTYPE_BASE) + typelem = get_element_type(atttypid); + + if (att_typtype != TYPTYPE_BASE || OidIsValid(typelem)) + { + ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate); + ri_NumGeneratedNeeded++; + } + } /* If UPDATE, mark column in resultRelInfo->ri_extraUpdatedCols */ if (cmdtype == CMD_UPDATE) @@ -518,7 +546,9 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo, /* * Compute generated columns for a tuple. - * we might support virtual generated column in future, currently not. + * Generally, we don't need to compute virtual generated column, except for + * column type is domain with constraint. Since virtual generated column don't + * have storage, we don't need to store it. */ void ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, @@ -534,7 +564,8 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, bool *nulls; /* We should not be called unless this is true */ - Assert(tupdesc->constr && tupdesc->constr->has_generated_stored); + Assert(tupdesc->constr); + Assert(tupdesc->constr->has_generated_stored || tupdesc->constr->has_generated_virtual); /* * Initialize the expressions if we didn't already, and check whether we @@ -552,11 +583,14 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, { if (resultRelInfo->ri_GeneratedExprsI == NULL) ExecInitGenerated(resultRelInfo, estate, cmdtype); - /* Early exit is impossible given the prior Assert */ - Assert(resultRelInfo->ri_NumGeneratedNeededI > 0); + if (resultRelInfo->ri_NumGeneratedNeededI == 0) + return; ri_GeneratedExprs = resultRelInfo->ri_GeneratedExprsI; } + if (ri_GeneratedExprs != NULL) + Assert(resultRelInfo->ri_NumGeneratedNeededU > 0 || resultRelInfo->ri_NumGeneratedNeededI > 0); + oldContext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate)); values = palloc(sizeof(*values) * natts); @@ -567,28 +601,35 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, for (int i = 0; i < natts; i++) { - CompactAttribute *attr = TupleDescCompactAttr(tupdesc, i); + Form_pg_attribute attr = TupleDescAttr(tupdesc, i); if (ri_GeneratedExprs[i]) { Datum val; bool isnull; - Assert(TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED); - econtext->ecxt_scantuple = slot; val = ExecEvalExpr(ri_GeneratedExprs[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); + if (attr->attgenerated == ATTRIBUTE_GENERATED_STORED) + { + /* + * 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); - values[i] = val; - nulls[i] = isnull; + values[i] = val; + nulls[i] = isnull; + } + else + { + Assert(attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL); + values[i] = (Datum) 0; + nulls[i] = true; + } } else { @@ -907,10 +948,11 @@ ExecInsert(ModifyTableContext *context, slot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc); /* - * Compute stored generated columns + * Compute generated columns */ if (resultRelationDesc->rd_att->constr && - resultRelationDesc->rd_att->constr->has_generated_stored) + (resultRelationDesc->rd_att->constr->has_generated_stored || + resultRelationDesc->rd_att->constr->has_generated_virtual)) ExecComputeGenerated(resultRelInfo, estate, slot, CMD_INSERT); @@ -1034,10 +1076,11 @@ ExecInsert(ModifyTableContext *context, slot->tts_tableOid = RelationGetRelid(resultRelationDesc); /* - * Compute stored generated columns + * Compute generated columns */ if (resultRelationDesc->rd_att->constr && - resultRelationDesc->rd_att->constr->has_generated_stored) + (resultRelationDesc->rd_att->constr->has_generated_stored || + resultRelationDesc->rd_att->constr->has_generated_virtual)) ExecComputeGenerated(resultRelInfo, estate, slot, CMD_INSERT); @@ -2122,10 +2165,11 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo, slot->tts_tableOid = RelationGetRelid(resultRelationDesc); /* - * Compute stored generated columns + * Compute generated columns */ if (resultRelationDesc->rd_att->constr && - resultRelationDesc->rd_att->constr->has_generated_stored) + (resultRelationDesc->rd_att->constr->has_generated_stored || + resultRelationDesc->rd_att->constr->has_generated_virtual)) ExecComputeGenerated(resultRelInfo, estate, slot, CMD_UPDATE); } diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index dbd339e9df4..11c7f7afbfd 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -23,7 +23,6 @@ #define CHKATYPE_ANYARRAY 0x01 /* allow ANYARRAY */ #define CHKATYPE_ANYRECORD 0x02 /* allow RECORD and RECORD[] */ #define CHKATYPE_IS_PARTKEY 0x04 /* attname is part key # not column */ -#define CHKATYPE_IS_VIRTUAL 0x08 /* is virtual generated column */ typedef struct RawColumnDefault { diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index ccbcdf8403f..e1353560aba 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -70,6 +70,9 @@ NOTICE: rewriting table has_volatile for reason 4 -- stored generated columns need a rewrite ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored; NOTICE: rewriting table has_volatile for reason 2 +-- virtual generated columns over volatile domain constraint don't need a rewrite +CREATE DOMAIN d1 as INT CHECK(value < random(min=>12::int, max=>21)); +ALTER TABLE has_volatile ADD col8 int GENERATED ALWAYS AS (1 + id) VIRTUAL; -- Test a large sample of different datatypes CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1); SELECT set('t'); @@ -922,6 +925,7 @@ DROP FUNCTION set(name); DROP FUNCTION comp(); DROP TABLE m; DROP TABLE has_volatile; +DROP DOMAIN d1; DROP EVENT TRIGGER has_volatile_rewrite; DROP FUNCTION log_rewrite; DROP SCHEMA fast_default; diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 26bbe1e9c31..925c15caf5b 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -790,16 +790,63 @@ ERROR: relation "gtest23p" does not exist --INSERT INTO gtest23q VALUES (1, 2); -- ok --INSERT INTO gtest23q VALUES (2, 5); -- error -- domains -CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10); -CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL); -ERROR: virtual generated column "b" cannot have a domain type ---INSERT INTO gtest24 (a) VALUES (4); -- ok ---INSERT INTO gtest24 (a) VALUES (6); -- error +CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10) DEFAULT 12; +CREATE TABLE gtest24 (a int UNIQUE, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL); +INSERT INTO gtest24 (a, b) VALUES (4, default); -- ok +INSERT INTO gtest24 (a) VALUES (3), (NULL); -- ok +INSERT INTO gtest24 (a) VALUES (6); -- error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +UPDATE gtest24 SET a = 6; -- error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +COPY gtest24 FROM stdin; --error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +CONTEXT: COPY gtest24, line 1: "6" +SELECT * FROM gtest24; + a | b +---+--- + 4 | 8 + 3 | 6 + | +(3 rows) + +--ALTER TABLE ADD COLUM variant. +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 2) virtual not null; --error +ERROR: column "c" of relation "gtest24" contains null values +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 3) virtual check (c < 10); --error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 4) virtual; --table rewrite then error. +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 2) virtual; --ok +--alter domain add constraint variant. +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NULL); --error +ERROR: column "b" of table "gtest24" contains values that violate the new constraint +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NOT NULL); --error +ERROR: column "b" of table "gtest24" contains values that violate the new constraint +ALTER DOMAIN gtestdomain1 ADD NOT NULL; --error +ERROR: column "b" of table "gtest24" contains null values +DELETE FROM gtest24 WHERE a is NULL; +ALTER DOMAIN gtestdomain1 ADD NOT NULL; --ok +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NOT NULL); --ok +ALTER DOMAIN gtestdomain1 ADD CHECK (VALUE < 7); --error +ERROR: column "b" of table "gtest24" contains values that violate the new constraint +ALTER DOMAIN gtestdomain1 ADD CHECK (VALUE < 9); --ok CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1); -CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL); -ERROR: virtual generated column "b" cannot have a domain type ---INSERT INTO gtest24r (a) VALUES (4); -- ok ---INSERT INTO gtest24r (a) VALUES (6); -- error +CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 4)) VIRTUAL); +INSERT INTO gtest24r (a) VALUES (4); -- ok +INSERT INTO gtest24r (a) VALUES (6); -- error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +INSERT INTO gtest24r (a) VALUES (5); -- error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check2" +CREATE TABLE gtest24v ( + a jsonb, + b gtestdomain1[] GENERATED ALWAYS AS (JSON_QUERY(a, '$.a' returning gtestdomain1[] error on error)) VIRTUAL, + c gtestdomain1[] GENERATED ALWAYS AS (JSON_QUERY(a, '$.b' returning gtestdomain1[])) VIRTUAL not null); +insert into gtest24v select jsonb '{"a":[6,10], "b":[6,2]}'; --error +ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check" +insert into gtest24v select jsonb '{"a":[6,-1], "b":[6,10]}'; --error +ERROR: null value in column "c" of relation "gtest24v" violates not-null constraint +DETAIL: Failing row contains ({"a": [6, -1], "b": [6, 10]}, virtual, virtual). +insert into gtest24v select jsonb '{"a":[6,-1], "b":[6,2]}'; --ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) VIRTUAL); diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql index 068dd0bc8aa..ffec1e11011 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -77,6 +77,9 @@ ALTER TABLE has_volatile ALTER COLUMN col1 SET DATA TYPE float8, -- stored generated columns need a rewrite ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored; +-- virtual generated columns over volatile domain constraint don't need a rewrite +CREATE DOMAIN d1 as INT CHECK(value < random(min=>12::int, max=>21)); +ALTER TABLE has_volatile ADD col8 int GENERATED ALWAYS AS (1 + id) VIRTUAL; -- Test a large sample of different datatypes @@ -616,6 +619,7 @@ DROP FUNCTION set(name); DROP FUNCTION comp(); DROP TABLE m; DROP TABLE has_volatile; +DROP DOMAIN d1; DROP EVENT TRIGGER has_volatile_rewrite; DROP FUNCTION log_rewrite; DROP SCHEMA fast_default; diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index 13cfbd76859..383e20f67df 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -444,14 +444,47 @@ CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y)); --INSERT INTO gtest23q VALUES (2, 5); -- error -- domains -CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10); -CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL); ---INSERT INTO gtest24 (a) VALUES (4); -- ok ---INSERT INTO gtest24 (a) VALUES (6); -- error +CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10) DEFAULT 12; +CREATE TABLE gtest24 (a int UNIQUE, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL); +INSERT INTO gtest24 (a, b) VALUES (4, default); -- ok +INSERT INTO gtest24 (a) VALUES (3), (NULL); -- ok +INSERT INTO gtest24 (a) VALUES (6); -- error +UPDATE gtest24 SET a = 6; -- error +COPY gtest24 FROM stdin; --error +6 +\. + +SELECT * FROM gtest24; + +--ALTER TABLE ADD COLUM variant. +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 2) virtual not null; --error +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 3) virtual check (c < 10); --error +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 4) virtual; --table rewrite then error. +ALTER TABLE gtest24 ADD COLUMN c gtestdomain1 GENERATED ALWAYS AS (a * 2) virtual; --ok + +--alter domain add constraint variant. +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NULL); --error +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NOT NULL); --error +ALTER DOMAIN gtestdomain1 ADD NOT NULL; --error +DELETE FROM gtest24 WHERE a is NULL; +ALTER DOMAIN gtestdomain1 ADD NOT NULL; --ok +ALTER DOMAIN gtestdomain1 ADD CHECK (value IS NOT NULL); --ok +ALTER DOMAIN gtestdomain1 ADD CHECK (VALUE < 7); --error +ALTER DOMAIN gtestdomain1 ADD CHECK (VALUE < 9); --ok + CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1); -CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) VIRTUAL); ---INSERT INTO gtest24r (a) VALUES (4); -- ok ---INSERT INTO gtest24r (a) VALUES (6); -- error +CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 4)) VIRTUAL); +INSERT INTO gtest24r (a) VALUES (4); -- ok +INSERT INTO gtest24r (a) VALUES (6); -- error +INSERT INTO gtest24r (a) VALUES (5); -- error + +CREATE TABLE gtest24v ( + a jsonb, + b gtestdomain1[] GENERATED ALWAYS AS (JSON_QUERY(a, '$.a' returning gtestdomain1[] error on error)) VIRTUAL, + c gtestdomain1[] GENERATED ALWAYS AS (JSON_QUERY(a, '$.b' returning gtestdomain1[])) VIRTUAL not null); +insert into gtest24v select jsonb '{"a":[6,10], "b":[6,2]}'; --error +insert into gtest24v select jsonb '{"a":[6,-1], "b":[6,10]}'; --error +insert into gtest24v select jsonb '{"a":[6,-1], "b":[6,2]}'; --ok -- typed tables (currently not supported) CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint); -- 2.34.1
From c93dddb0c9e744fd4e8a329a6d28413137c6dd83 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Thu, 13 Mar 2025 15:05:41 +0800 Subject: [PATCH v7 2/3] soft error variant of ExecPrepareExpr, ExecInitExpr ExecPrepareExprSafe and ExecInitExprSafe. ExecPrepareExprSafe initialize for expression execution with soft error support. not all expression node support it. some like CoerceToDomain do support it. XXX more comments. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com --- src/backend/executor/execExpr.c | 63 +++++++++++++++++++++++++++++++++ src/include/executor/executor.h | 2 ++ 2 files changed, 65 insertions(+) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index f1569879b52..b8f5f647281 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -170,6 +170,46 @@ ExecInitExpr(Expr *node, PlanState *parent) return state; } +/* + * ExecInitExpr: soft error variant of ExecInitExpr. + * use it only for expression nodes support soft errors, not all expression + * nodes support it. +*/ +ExprState * +ExecInitExprSafe(Expr *node, PlanState *parent) +{ + ExprState *state; + ExprEvalStep scratch = {0}; + + /* Special case: NULL expression produces a NULL ExprState pointer */ + if (node == NULL) + return NULL; + + /* Initialize ExprState with empty step list */ + state = makeNode(ExprState); + state->expr = node; + state->parent = parent; + state->ext_params = NULL; + state->escontext = makeNode(ErrorSaveContext); + state->escontext->type = T_ErrorSaveContext; + state->escontext->error_occurred = false; + state->escontext->details_wanted = true; + + /* Insert setup steps as needed */ + ExecCreateExprSetupSteps(state, (Node *) node); + + /* Compile the expression proper */ + ExecInitExprRec(node, state, &state->resvalue, &state->resnull); + + /* Finally, append a DONE step */ + scratch.opcode = EEOP_DONE_RETURN; + ExprEvalPushStep(state, &scratch); + + ExecReadyExpr(state); + + return state; +} + /* * ExecInitExprWithParams: prepare a standalone expression tree for execution * @@ -778,6 +818,29 @@ ExecPrepareExpr(Expr *node, EState *estate) return result; } +/* + * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr. + * + * use it when expression node *support* soft error expression execution. + * ExecPrepareExpr comments apply to here too. + */ +ExprState * +ExecPrepareExprSafe(Expr *node, EState *estate) +{ + ExprState *result; + MemoryContext oldcontext; + + oldcontext = MemoryContextSwitchTo(estate->es_query_cxt); + + node = expression_planner(node); + + result = ExecInitExprSafe(node, NULL); + + MemoryContextSwitchTo(oldcontext); + + return result; +} + /* * ExecPrepareQual --- initialize for qual execution outside a normal * Plan tree context. diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index ae99407db89..a26160042ee 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -346,6 +346,7 @@ ExecProcNode(PlanState *node) * prototypes from functions in execExpr.c */ extern ExprState *ExecInitExpr(Expr *node, PlanState *parent); +extern ExprState *ExecInitExprSafe(Expr *node, PlanState *parent); extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params); extern ExprState *ExecInitQual(List *qual, PlanState *parent); extern ExprState *ExecInitCheck(List *qual, PlanState *parent); @@ -394,6 +395,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList, TupleTableSlot *slot, PlanState *parent); extern ExprState *ExecPrepareExpr(Expr *node, EState *estate); +extern ExprState *ExecPrepareExprSafe(Expr *node, EState *estate); extern ExprState *ExecPrepareQual(List *qual, EState *estate); extern ExprState *ExecPrepareCheck(List *qual, EState *estate); extern List *ExecPrepareExprList(List *nodes, EState *estate); -- 2.34.1
From 28a05de321046b578686cc4e4802bcd4243d5343 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Thu, 13 Mar 2025 20:15:46 +0800 Subject: [PATCH v7 1/3] rename ExecComputeStoredGenerated to ExecComputeGenerated to support virtual generated column over domain type, we actually need compute the virtual generated expression. we did it at ExecComputeGenerated for stored, we can use it for virtual. so do the rename. discussion: https://postgr.es/m/cacjufxharqysbdkwfmvk+d1tphqwwtxwn15cmuuatyx3xhq...@mail.gmail.com --- src/backend/commands/copyfrom.c | 4 ++-- src/backend/executor/execReplication.c | 8 ++++---- src/backend/executor/nodeModifyTable.c | 20 ++++++++++---------- src/include/executor/nodeModifyTable.h | 5 ++--- 4 files changed, 18 insertions(+), 19 deletions(-) diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index fbbbc09a97b..906b6581e11 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1346,8 +1346,8 @@ CopyFrom(CopyFromState cstate) /* Compute stored generated columns */ if (resultRelInfo->ri_RelationDesc->rd_att->constr && resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, myslot, - CMD_INSERT); + ExecComputeGenerated(resultRelInfo, estate, myslot, + CMD_INSERT); /* * If the target is a plain table, check the constraints of diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index ede89ea3cf9..40c078638ab 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -557,8 +557,8 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo, /* Compute stored generated columns */ if (rel->rd_att->constr && rel->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, slot, - CMD_INSERT); + ExecComputeGenerated(resultRelInfo, estate, slot, + CMD_INSERT); /* Check the constraints of the tuple */ if (rel->rd_att->constr) @@ -654,8 +654,8 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, /* Compute stored generated columns */ if (rel->rd_att->constr && rel->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, slot, - CMD_UPDATE); + ExecComputeGenerated(resultRelInfo, estate, slot, + CMD_UPDATE); /* Check the constraints of the tuple */ if (rel->rd_att->constr) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 309e27f8b5f..5a581446c7d 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -517,12 +517,12 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo, } /* - * Compute stored generated columns for a tuple + * Compute generated columns for a tuple. + * we might support virtual generated column in future, currently not. */ void -ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo, - EState *estate, TupleTableSlot *slot, - CmdType cmdtype) +ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, + TupleTableSlot *slot, CmdType cmdtype) { Relation rel = resultRelInfo->ri_RelationDesc; TupleDesc tupdesc = RelationGetDescr(rel); @@ -911,8 +911,8 @@ ExecInsert(ModifyTableContext *context, */ if (resultRelationDesc->rd_att->constr && resultRelationDesc->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, slot, - CMD_INSERT); + ExecComputeGenerated(resultRelInfo, estate, slot, + CMD_INSERT); /* * If the FDW supports batching, and batching is requested, accumulate @@ -1038,8 +1038,8 @@ ExecInsert(ModifyTableContext *context, */ if (resultRelationDesc->rd_att->constr && resultRelationDesc->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, slot, - CMD_INSERT); + ExecComputeGenerated(resultRelInfo, estate, slot, + CMD_INSERT); /* * Check any RLS WITH CHECK policies. @@ -2126,8 +2126,8 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo, */ if (resultRelationDesc->rd_att->constr && resultRelationDesc->rd_att->constr->has_generated_stored) - ExecComputeStoredGenerated(resultRelInfo, estate, slot, - CMD_UPDATE); + ExecComputeGenerated(resultRelInfo, estate, slot, + CMD_UPDATE); } /* diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h index bf3b592e28f..de374c46d3c 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -19,9 +19,8 @@ extern void ExecInitGenerated(ResultRelInfo *resultRelInfo, EState *estate, CmdType cmdtype); -extern void ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo, - EState *estate, TupleTableSlot *slot, - CmdType cmdtype); +extern void ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate, + TupleTableSlot *slot,CmdType cmdtype); extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags); extern void ExecEndModifyTable(ModifyTableState *node); -- 2.34.1
