hi.
attached patch is implement a TODO (foreign key on virtual generated
column) left on [1]
for foreign key on virtual generated column, we only support
ON UPDATE NO ACTION
ON UPDATE RESTRICT
ON DELETE CASCADE
ON DELETE NO ACTION
ON DELETE RESTRICT
demo:
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33), (131072, 44);
CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a
* 1) VIRTUAL REFERENCES gtest23a (x) ON DELETE CASCADE); --ok
INSERT INTO gtest23b VALUES (1); -- ok
INSERT INTO gtest23b VALUES (5); -- error
UPDATE gtest23b SET a = 5 WHERE a = 1; --error
DELETE FROM gtest23a WHERE x = 1; --ok
ALTER TABLE ALTER COLUMN SET EXPRESSION
ALTER TABLE ALTER COLUMN SET DATA TYPE
if foreign key on virtual generated column, the above two will not cause table
rewrite,but will do foreign key constraint validation.
[1]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b
From 6acff5606b6181442eac7a1128c879c378adcb05 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 27 May 2025 21:24:18 +0800
Subject: [PATCH v1 2/2] foreign key on virtual generated column
for virtual generated column, currently support
ON UPDATE NO ACTION
ON UPDATE RESTRICT
ON DELETE CASCADE
ON DELETE NO ACTION
ON DELETE RESTRICT
discussion: https://postgr.es/m/
---
src/backend/commands/copyfrom.c | 3 +-
src/backend/commands/tablecmds.c | 97 ++++++++-------
src/backend/executor/execReplication.c | 6 +-
src/backend/executor/execUtils.c | 2 +-
src/backend/executor/nodeModifyTable.c | 49 +++++---
src/backend/utils/adt/ri_triggers.c | 114 ++++++++++++++++++
src/include/executor/nodeModifyTable.h | 7 +-
.../regress/expected/generated_virtual.out | 67 ++++++++--
src/test/regress/sql/generated_virtual.sql | 40 ++++--
9 files changed, 304 insertions(+), 81 deletions(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 906b6581e11..1a545d78593 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1347,7 +1347,8 @@ CopyFrom(CopyFromState cstate)
if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, myslot,
- CMD_INSERT);
+ CMD_INSERT,
+ false);
/*
* If the target is a plain table, check the constraints of
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 54ad38247aa..252776c0005 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -659,7 +659,7 @@ static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
Oid objid, Relation rel, List *domname,
const char *conname);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
-static void TryReuseForeignKey(Oid oldId, Constraint *con);
+static void TryReuseForeignKey(Oid oldId, Constraint *con, Oid oldRelId);
static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
List *options, LOCKMODE lockmode);
static void change_owner_fix_column_acls(Oid relationOid,
@@ -8642,18 +8642,18 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
* this renders them pointless.
*/
RelationClearMissing(rel);
-
- /* make sure we don't conflict with later attribute modifications */
- CommandCounterIncrement();
-
- /*
- * Find everything that depends on the column (constraints, indexes,
- * etc), and record enough information to let us recreate the objects
- * after rewrite.
- */
- RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
}
+ /* make sure we don't conflict with later attribute modifications */
+ CommandCounterIncrement();
+
+ /*
+ * Find everything that depends on the column (constraints, indexes,
+ * etc), and record enough information to let us recreate the objects
+ * after rewrite.
+ */
+ RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+
/*
* Drop the dependency records of the GENERATED expression, in particular
* its INTERNAL dependency on the column, which would otherwise cause
@@ -10222,19 +10222,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
errmsg("invalid %s action for foreign key constraint containing generated column",
"ON DELETE")));
}
-
- /*
- * FKs on virtual columns are not supported. This would require
- * various additional support in ri_triggers.c, including special
- * handling in ri_NullCheck(), ri_KeysEqual(),
- * RI_FKey_fk_upd_check_required() (since all virtual columns appear
- * as NULL there). Also not really practical as long as you can't
- * index virtual columns.
- */
- if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("foreign key constraints on virtual generated columns are not supported")));
}
/*
@@ -15665,7 +15652,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
/* rewriting neither side of a FK */
if (con->contype == CONSTR_FOREIGN &&
!rewrite && tab->rewrite == 0)
- TryReuseForeignKey(oldId, con);
+ TryReuseForeignKey(oldId, con, oldRelId);
con->reset_default_tblspc = true;
cmd->subtype = AT_ReAddConstraint;
tab->subcmds[AT_PASS_OLD_CONSTR] =
@@ -15822,16 +15809,23 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
* Stash the old P-F equality operator into the Constraint node, for possible
* use by ATAddForeignKeyConstraint() in determining whether revalidation of
* this constraint can be skipped.
+ *
+ * oldId is the old (previous) foreign key pg_constraint oid.
+ * oldRelId: the relation where this foreign key constraint is on.
+ * revalidation can not be skipped if any foreign key attribute is virtual
+ * generated column.
*/
static void
-TryReuseForeignKey(Oid oldId, Constraint *con)
+TryReuseForeignKey(Oid oldId, Constraint *con, Oid oldRelId)
{
HeapTuple tup;
- Datum adatum;
- ArrayType *arr;
- Oid *rawarr;
int numkeys;
int i;
+ Relation rel;
+ Oid conpfeqop[INDEX_MAX_KEYS];
+ AttrNumber conkey[INDEX_MAX_KEYS];
+ AttrNumber confkey[INDEX_MAX_KEYS];
+ bool fkey_on_virtual_generated = false;
Assert(con->contype == CONSTR_FOREIGN);
Assert(con->old_conpfeqop == NIL); /* already prepared this node */
@@ -15840,20 +15834,41 @@ TryReuseForeignKey(Oid oldId, Constraint *con)
if (!HeapTupleIsValid(tup)) /* should not happen */
elog(ERROR, "cache lookup failed for constraint %u", oldId);
- adatum = SysCacheGetAttrNotNull(CONSTROID, tup,
- Anum_pg_constraint_conpfeqop);
- arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
- numkeys = ARR_DIMS(arr)[0];
- /* test follows the one in ri_FetchConstraintInfo() */
- if (ARR_NDIM(arr) != 1 ||
- ARR_HASNULL(arr) ||
- ARR_ELEMTYPE(arr) != OIDOID)
- elog(ERROR, "conpfeqop is not a 1-D Oid array");
- rawarr = (Oid *) ARR_DATA_PTR(arr);
+ DeconstructFkConstraintRow(tup,
+ &numkeys,
+ conkey,
+ confkey,
+ conpfeqop,
+ NULL, /* pp_eq_oprs */
+ NULL, /* ff_eq_oprs */
+ NULL, /* num_fk_del_set_cols */
+ NULL); /* fk_del_set_cols */
+
+ rel = table_open(oldRelId, NoLock);
+
+ if (rel->rd_att->constr &&
+ rel->rd_att->constr->has_generated_virtual)
+ {
+ TupleDesc tupleDesc = RelationGetDescr(rel);
+
+ for (i = 0; i < numkeys; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupleDesc, conkey[i] - 1);
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ fkey_on_virtual_generated = true;
+ break;
+ }
+ }
+ }
+ table_close(rel, NoLock);
/* stash a List of the operator Oids in our Constraint node */
- for (i = 0; i < numkeys; i++)
- con->old_conpfeqop = lappend_oid(con->old_conpfeqop, rawarr[i]);
+ if (!fkey_on_virtual_generated)
+ {
+ for (i = 0; i < numkeys; i++)
+ con->old_conpfeqop = lappend_oid(con->old_conpfeqop, conpfeqop[i]);
+ }
ReleaseSysCache(tup);
}
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index ca300ac0f00..ee1bda6612f 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -588,7 +588,8 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo,
if (rel->rd_att->constr &&
rel->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, slot,
- CMD_INSERT);
+ CMD_INSERT,
+ false);
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
@@ -685,7 +686,8 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
if (rel->rd_att->constr &&
rel->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, slot,
- CMD_UPDATE);
+ CMD_UPDATE,
+ false);
/* Check the constraints of the tuple */
if (rel->rd_att->constr)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index fdc65c2b42b..7d070f0e682 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -1404,7 +1404,7 @@ ExecGetExtraUpdatedCols(ResultRelInfo *relinfo, EState *estate)
{
/* Compute the info if we didn't already */
if (!relinfo->ri_extraUpdatedCols_valid)
- ExecInitGenerated(relinfo, estate, CMD_UPDATE);
+ ExecInitGenerated(relinfo, estate, CMD_UPDATE, false);
return relinfo->ri_extraUpdatedCols;
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 51e85f918a2..e1457587bda 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -415,7 +415,8 @@ 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 mainly used only for stored generated columns. However if
+ * compute_virtual is true, we do the same for virtual generated column.
*
* If cmdType == CMD_UPDATE, the ri_extraUpdatedCols field is filled too.
* This is used by both stored and virtual generated columns.
@@ -428,7 +429,8 @@ ExecCheckTIDVisible(EState *estate,
void
ExecInitGenerated(ResultRelInfo *resultRelInfo,
EState *estate,
- CmdType cmdtype)
+ CmdType cmdtype,
+ bool compute_virtual)
{
Relation rel = resultRelInfo->ri_RelationDesc;
TupleDesc tupdesc = RelationGetDescr(rel);
@@ -472,10 +474,15 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
Expr *expr;
/* Fetch the GENERATED AS expression tree */
- expr = (Expr *) build_column_default(rel, i + 1);
- if (expr == NULL)
- elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
- i + 1, RelationGetRelationName(rel));
+ if (attgenerated == ATTRIBUTE_GENERATED_STORED)
+ {
+ expr = (Expr *) build_column_default(rel, i + 1);
+ if (expr == NULL)
+ elog(ERROR, "no generation expression found for column number %d of table \"%s\"",
+ i + 1, RelationGetRelationName(rel));
+ }
+ else
+ expr = (Expr *) build_generation_expression(rel, i+1);
/*
* If it's an update with a known set of update target columns,
@@ -497,6 +504,11 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate);
ri_NumGeneratedNeeded++;
}
+ else if (compute_virtual)
+ {
+ ri_GeneratedExprs[i] = ExecPrepareExpr(expr, estate);
+ ri_NumGeneratedNeeded++;
+ }
/* If UPDATE, mark column in resultRelInfo->ri_extraUpdatedCols */
if (cmdtype == CMD_UPDATE)
@@ -538,11 +550,13 @@ ExecInitGenerated(ResultRelInfo *resultRelInfo,
/*
* Compute generated columns for a tuple.
- * we might support virtual generated column in future, currently not.
+ * If compute_virtual is true, we exclusively compute virtual generated columns.
+ * We do not compute stored and virtual generated columns simultaneously.
*/
void
ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate,
- TupleTableSlot *slot, CmdType cmdtype)
+ TupleTableSlot *slot, CmdType cmdtype,
+ bool compute_virtual)
{
Relation rel = resultRelInfo->ri_RelationDesc;
TupleDesc tupdesc = RelationGetDescr(rel);
@@ -554,7 +568,9 @@ 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
@@ -563,7 +579,7 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate,
if (cmdtype == CMD_UPDATE)
{
if (resultRelInfo->ri_GeneratedExprsU == NULL)
- ExecInitGenerated(resultRelInfo, estate, cmdtype);
+ ExecInitGenerated(resultRelInfo, estate, cmdtype, compute_virtual);
if (resultRelInfo->ri_NumGeneratedNeededU == 0)
return;
ri_GeneratedExprs = resultRelInfo->ri_GeneratedExprsU;
@@ -571,7 +587,7 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate,
else
{
if (resultRelInfo->ri_GeneratedExprsI == NULL)
- ExecInitGenerated(resultRelInfo, estate, cmdtype);
+ ExecInitGenerated(resultRelInfo, estate, cmdtype, compute_virtual);
/* Early exit is impossible given the prior Assert */
Assert(resultRelInfo->ri_NumGeneratedNeededI > 0);
ri_GeneratedExprs = resultRelInfo->ri_GeneratedExprsI;
@@ -594,7 +610,7 @@ ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate,
Datum val;
bool isnull;
- Assert(TupleDescAttr(tupdesc, i)->attgenerated == ATTRIBUTE_GENERATED_STORED);
+ Assert(TupleDescAttr(tupdesc, i)->attgenerated != '\0');
econtext->ecxt_scantuple = slot;
@@ -932,7 +948,8 @@ ExecInsert(ModifyTableContext *context,
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, slot,
- CMD_INSERT);
+ CMD_INSERT,
+ false);
/*
* If the FDW supports batching, and batching is requested, accumulate
@@ -1059,7 +1076,8 @@ ExecInsert(ModifyTableContext *context,
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, slot,
- CMD_INSERT);
+ CMD_INSERT,
+ false);
/*
* Check any RLS WITH CHECK policies.
@@ -2147,7 +2165,8 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo,
if (resultRelationDesc->rd_att->constr &&
resultRelationDesc->rd_att->constr->has_generated_stored)
ExecComputeGenerated(resultRelInfo, estate, slot,
- CMD_UPDATE);
+ CMD_UPDATE,
+ false);
}
/*
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 6239900fa28..907b421a0f0 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -33,6 +33,7 @@
#include "catalog/pg_proc.h"
#include "commands/trigger.h"
#include "executor/executor.h"
+#include "executor/nodeModifyTable.h"
#include "executor/spi.h"
#include "lib/ilist.h"
#include "miscadmin.h"
@@ -284,6 +285,57 @@ RI_FKey_check(TriggerData *trigdata)
fk_rel = trigdata->tg_relation;
pk_rel = table_open(riinfo->pk_relid, RowShareLock);
+ /*
+ * If a foreign key includes virtual generated columns, their generation
+ * expressions need to be computed first. This can guarantees ri_NullCheck
+ * can return correct result.
+ */
+ if (fk_rel->rd_att->constr &&
+ fk_rel->rd_att->constr->has_generated_virtual)
+ {
+ bool compute_virtual = false;
+ TupleDesc tupdesc = RelationGetDescr(fk_rel);
+
+ for (int i = 0; i < riinfo->nkeys; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, riinfo->fk_attnums[i] - 1);
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ compute_virtual = true;
+ break;
+ }
+ }
+
+ if (compute_virtual)
+ {
+ ResultRelInfo *rInfo = NULL;
+ EState *estate = NULL;
+ CmdType cmdtype;
+
+ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ cmdtype = CMD_UPDATE;
+ else
+ cmdtype = CMD_INSERT;
+
+ estate = CreateExecutorState();
+
+ rInfo = makeNode(ResultRelInfo);
+ InitResultRelInfo(rInfo,
+ fk_rel,
+ 0, /* dummy rangetable index */
+ NULL,
+ estate->es_instrument);
+
+ ExecComputeGenerated(rInfo,
+ estate,
+ newslot,
+ cmdtype,
+ true);
+
+ FreeExecutorState(estate);
+ }
+ }
+
switch (ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false))
{
case RI_KEYS_ALL_NULL:
@@ -1420,6 +1472,8 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
{
const RI_ConstraintInfo *riinfo;
int ri_nullcheck;
+ ResultRelInfo *rInfo = NULL;
+ EState *estate = NULL;
/*
* AfterTriggerSaveEvent() handles things such that this function is never
@@ -1429,8 +1483,58 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
riinfo = ri_FetchConstraintInfo(trigger, fk_rel, false);
+ /*
+ * if foreighn key contains virtual generated column, we compute its value
+ * for newslot and oldslot, after that using ri_KeysEqual to compare old and
+ * new key values.
+ */
+ if (fk_rel->rd_att->constr &&
+ fk_rel->rd_att->constr->has_generated_virtual)
+ {
+ bool compute_virtual = false;
+ TupleDesc tupdesc = RelationGetDescr(fk_rel);
+
+ for (int i = 0; i < riinfo->nkeys; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, riinfo->fk_attnums[i] - 1);
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ compute_virtual = true;
+ break;
+ }
+ }
+
+ if (compute_virtual)
+ {
+ MemoryContext oldcontext;
+
+ estate = CreateExecutorState();
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+ rInfo = makeNode(ResultRelInfo);
+ InitResultRelInfo(rInfo,
+ fk_rel,
+ 0, /* dummy rangetable index */
+ NULL,
+ estate->es_instrument);
+ MemoryContextSwitchTo(oldcontext);
+
+ ExecComputeGenerated(rInfo,
+ estate,
+ newslot,
+ CMD_UPDATE,
+ true);
+ }
+ }
ri_nullcheck = ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false);
+ /*
+ * if ri_nullcheck is not RI_KEYS_NONE_NULL, we don't need fire the RI
+ * trigger, free EState then.
+ */
+ if (ri_nullcheck != RI_KEYS_NONE_NULL && estate != NULL)
+ FreeExecutorState(estate);
+
/*
* If all new key values are NULL, the row satisfies the constraint, so no
* check is needed.
@@ -1490,6 +1594,16 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
if (slot_is_current_xact_tuple(oldslot))
return true;
+ if (rInfo != NULL)
+ {
+ ExecComputeGenerated(rInfo,
+ estate,
+ oldslot,
+ CMD_UPDATE,
+ true);
+ FreeExecutorState(estate);
+ }
+
/* If all old and new key values are equal, no check is needed */
if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false))
return false;
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index de374c46d3c..47ab8b7dcaf 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -17,10 +17,13 @@
extern void ExecInitGenerated(ResultRelInfo *resultRelInfo,
EState *estate,
- CmdType cmdtype);
+ CmdType cmdtype,
+ bool compute_virtual);
extern void ExecComputeGenerated(ResultRelInfo *resultRelInfo, EState *estate,
- TupleTableSlot *slot,CmdType cmdtype);
+ TupleTableSlot *slot,
+ CmdType cmdtype,
+ bool compute_virtual);
extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags);
extern void ExecEndModifyTable(ModifyTableState *node);
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 6300e7c1d96..e3f224e918e 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -767,21 +767,68 @@ CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
--RESET enable_seqscan;
--RESET enable_bitmapscan;
-- foreign keys
+CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) );
+CREATE TABLE FKTABLE ( ftest1 int, ftest2 int GENERATED ALWAYS AS (nullif(ftest1, 1)));
+INSERT INTO PKTABLE VALUES (1, 2), (2, 2);
+INSERT INTO FKTABLE VALUES (1), (2);
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; --error
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_ftest2_fkey"
+DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE; --ok
+DELETE FROM FKTABLE WHERE ftest1 = 1;
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; --ok
+DROP TABLE FKTABLE;
+DROP TABLE PKTABLE;
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
---INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
+INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33), (131072, 44);
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE SET DEFAULT); -- error
+ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE SET NULL); -- error
+ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
ERROR: invalid ON DELETE action for foreign key constraint containing generated column
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x));
-ERROR: foreign key constraints on virtual generated columns are not supported
---\d gtest23b
---INSERT INTO gtest23b VALUES (1); -- ok
---INSERT INTO gtest23b VALUES (5); -- error
---ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
---ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
---DROP TABLE gtest23b;
---DROP TABLE gtest23a;
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET DEFAULT); -- error
+ERROR: invalid ON DELETE action for foreign key constraint containing generated column
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE CASCADE); --ok
+\d gtest23b
+ Table "generated_virtual_tests.gtest23b"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (a * 2)
+Indexes:
+ "gtest23b_pkey" PRIMARY KEY, btree (a)
+Foreign-key constraints:
+ "gtest23b_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x) ON DELETE CASCADE
+
+INSERT INTO gtest23b VALUES (1); -- ok
+INSERT INTO gtest23b VALUES (5); -- error
+ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
+DETAIL: Key (b)=(10) is not present in table "gtest23a".
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
+DETAIL: Key (b)=(5) is not present in table "gtest23a".
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+INSERT INTO gtest23b VALUES (131072);
+\set SHOW_CONTEXT never
+ALTER TABLE gtest23b ALTER COLUMN b SET DATA TYPE smallint; -- error
+ERROR: smallint out of range
+\set SHOW_CONTEXT errors
+ALTER TABLE gtest23b ALTER COLUMN b SET DATA TYPE bigint; --ok
+UPDATE gtest23b SET a = 5 WHERE a = 1; --error
+ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
+DETAIL: Key (b)=(5) is not present in table "gtest23a".
+DELETE FROM gtest23b WHERE a = 1; --ok
+SELECT * FROM gtest23b ORDER BY a, b;
+ a | b
+--------+--------
+ 131072 | 131072
+(1 row)
+
+DROP TABLE gtest23b;
+DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
ERROR: primary keys on virtual generated columns are not supported
--INSERT INTO gtest23p VALUES (1), (2), (3);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index b4eedeee2fb..3d7929c185e 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -419,22 +419,44 @@ CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
--RESET enable_bitmapscan;
-- foreign keys
+CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, PRIMARY KEY(ptest1, ptest2) );
+CREATE TABLE FKTABLE ( ftest1 int, ftest2 int GENERATED ALWAYS AS (nullif(ftest1, 1)));
+INSERT INTO PKTABLE VALUES (1, 2), (2, 2);
+INSERT INTO FKTABLE VALUES (1), (2);
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; --error
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE; --ok
+DELETE FROM FKTABLE WHERE ftest1 = 1;
+ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL; --ok
+DROP TABLE FKTABLE;
+DROP TABLE PKTABLE;
+
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
---INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
+INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33), (131072, 44);
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE SET DEFAULT); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE SET NULL); -- error
CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
+CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET DEFAULT); -- error
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE CASCADE); --ok
+\d gtest23b
-CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x));
---\d gtest23b
+INSERT INTO gtest23b VALUES (1); -- ok
+INSERT INTO gtest23b VALUES (5); -- error
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+INSERT INTO gtest23b VALUES (131072);
+\set SHOW_CONTEXT never
+ALTER TABLE gtest23b ALTER COLUMN b SET DATA TYPE smallint; -- error
+\set SHOW_CONTEXT errors
+ALTER TABLE gtest23b ALTER COLUMN b SET DATA TYPE bigint; --ok
---INSERT INTO gtest23b VALUES (1); -- ok
---INSERT INTO gtest23b VALUES (5); -- error
---ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
---ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+UPDATE gtest23b SET a = 5 WHERE a = 1; --error
+DELETE FROM gtest23b WHERE a = 1; --ok
+SELECT * FROM gtest23b ORDER BY a, b;
---DROP TABLE gtest23b;
---DROP TABLE gtest23a;
+DROP TABLE gtest23b;
+DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
--INSERT INTO gtest23p VALUES (1), (2), (3);
--
2.34.1
From 80bbffed6d2d92dbc7cef13efd70e67bca3c8cf8 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 v1 1/2] 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 53ddd25c42d..ca300ac0f00 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -587,8 +587,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)
@@ -684,8 +684,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 2bc89bf84dc..51e85f918a2 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -537,12 +537,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);
@@ -931,8 +931,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
@@ -1058,8 +1058,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.
@@ -2146,8 +2146,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
