public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Eisentraut <[email protected]>
To: Amul Sul <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Joel Jacobson <[email protected]>
Cc: Alexandra Wang <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: Suraj Kharage <[email protected]>
Subject: Re: NOT ENFORCED constraint feature
Date: Thu, 27 Mar 2025 13:58:26 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAAJ_b94PTcHzp2BqOxQZ7S-Zxp2hGV192a=4V8dTifjypDPpEw@mail.gmail.com>
<[email protected]>
<CAAJ_b97aHsJgWhAuRQi1JdWsjzd_ygWEjqQVq_Ddo8dyCnnwkw@mail.gmail.com>
<[email protected]>
On 25.03.25 17:48, Peter Eisentraut wrote:
> I have committed patches 0001 through 0003. I made some small changes:
> I will work through the remaining patches. It looks good to me so far.
For the time being, here are the remaining patches rebased.
I think you could squash these together at this point. This is
especially useful since 0003 overwrites part of the changes in 0002, so
it's better to see them all together.
Some details:
In CloneFkReferenced() and also in DetachPartitionFinalize(), you have
this change:
- fkconstraint->initially_valid = true;
+ fkconstraint->initially_valid = constrForm->convalidated;
I'm having a hard time understanding this. Is this an pre-existing
problem? What does this change do?
Most of the other stuff is mechanical and fits into existing structures,
so it seems ok.
Small cosmetic suggestion: write count(*) instead of count(1). This
fits better with existing practices.
The merge rules for inheritance and partitioning are still confusing. I
don't understand the behavior inh_check_constraint10 in the inherit.sql
test:
+-- the invalid state of the child constraint will be ignored here.
+alter table p1 add constraint inh_check_constraint10 check (f1 < 10)
not enforced;
+alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10)
not valid enforced;
Why is that correct? At least we should explain it here, or somewhere.
I'm also confused about the changes of the constraint names in the
foreign_key.sql test:
-ERROR: insert or update on table "fk_partitioned_fk_2" violates
foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates
foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
And then patch 0003 changes it again. This seems pretty random. We
should make sure that tests don't contain unrelated changes like that.
(Or at least it's not clear why they are related.)
There is also in 0002
+-- should be having two constraints
and then in 0003:
--- should be having two constraints
+-- should only have one constraint
So another reason for squashing these together, so we don't have
confusing intermediate states.
That said, is there a simpler way? Patch 0003 appears to add a lot of
complexity. Could we make this simpler by saying, if you have otherwise
matching constraints with different enforceability, make this an error.
Then users can themselves adjust the enforceability how they want to
make it match.
From d9183039dffefd8f8443608e0e0cb85ae6233bdc Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Mon, 7 Oct 2024 12:40:07 +0530
Subject: [PATCH v18.1 1/3] Remove hastriggers flag check before fetching FK
constraints.
With NOT ENFORCED, foreign key constraints will be created without
triggers. Therefore, the criteria for fetching foreign keys based on
the presence of triggers no longer apply.
----
NOTE: This patch is intended to reduce the diff noise from the main
patch and is not meant to be committed separately. It should be
squashed with the main patch that adds ENFORCED/NOT ENFORCED.
----
---
src/backend/utils/cache/relcache.c | 5 -
src/bin/pg_dump/pg_dump.c | 8 +-
src/bin/psql/describe.c | 216 ++++++++++++++---------------
3 files changed, 103 insertions(+), 126 deletions(-)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 9f54a9e72b7..e6721056536 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4666,11 +4666,6 @@ RelationGetFKeyList(Relation relation)
if (relation->rd_fkeyvalid)
return relation->rd_fkeylist;
- /* Fast path: non-partitioned tables without triggers can't have FKs */
- if (!relation->rd_rel->relhastriggers &&
- relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
- return NIL;
-
/*
* We build the list we intend to return (in the caller's context) while
* doing the scan. After successfully completing the scan, we copy that
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e41e645f649..dc9797aa251 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8016,13 +8016,7 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
{
TableInfo *tinfo = &tblinfo[i];
- /*
- * For partitioned tables, foreign keys have no triggers so they must
- * be included anyway in case some foreign keys are defined.
- */
- if ((!tinfo->hastriggers &&
- tinfo->relkind != RELKIND_PARTITIONED_TABLE) ||
- !(tinfo->dobj.dump & DUMP_COMPONENT_DEFINITION))
+ if (!(tinfo->dobj.dump & DUMP_COMPONENT_DEFINITION))
continue;
/* OK, we need info for this table */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index bf565afcc4e..e4a4c5071dd 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2550,136 +2550,124 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
- /*
- * Print foreign-key constraints (there are none if no triggers,
- * except if the table is partitioned, in which case the triggers
- * appear in the partitions)
- */
- if (tableinfo.hastriggers ||
- tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
+ /* Print foreign-key constraints */
+ if (pset.sversion >= 120000 &&
+ (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
{
- if (pset.sversion >= 120000 &&
- (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
- {
- /*
- * Put the constraints defined in this table first, followed
- * by the constraints defined in ancestor partitioned tables.
- */
- printfPQExpBuffer(&buf,
- "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
- " conname,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
- " conrelid::pg_catalog.regclass AS ontable\n"
- " FROM pg_catalog.pg_constraint,\n"
- " pg_catalog.pg_partition_ancestors('%s')\n"
- " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
- "ORDER BY sametable DESC, conname;",
- oid, oid);
- }
- else
- {
- printfPQExpBuffer(&buf,
- "SELECT true as sametable, conname,\n"
- " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
- " conrelid::pg_catalog.regclass AS ontable\n"
- "FROM pg_catalog.pg_constraint r\n"
- "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
- oid);
-
- if (pset.sversion >= 120000)
- appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
- appendPQExpBufferStr(&buf, "ORDER BY conname");
- }
+ /*
+ * Put the constraints defined in this table first, followed
+ * by the constraints defined in ancestor partitioned tables.
+ */
+ printfPQExpBuffer(&buf,
+ "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
+ " conname,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
+ " conrelid::pg_catalog.regclass AS ontable\n"
+ " FROM pg_catalog.pg_constraint,\n"
+ " pg_catalog.pg_partition_ancestors('%s')\n"
+ " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
+ "ORDER BY sametable DESC, conname;",
+ oid, oid);
+ }
+ else
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT true as sametable, conname,\n"
+ " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
+ " conrelid::pg_catalog.regclass AS ontable\n"
+ "FROM pg_catalog.pg_constraint r\n"
+ "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
+ oid);
- result = PSQLexec(buf.data);
- if (!result)
- goto error_return;
- else
- tuples = PQntuples(result);
+ if (pset.sversion >= 120000)
+ appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
+ appendPQExpBufferStr(&buf, "ORDER BY conname");
+ }
- if (tuples > 0)
- {
- int i_sametable = PQfnumber(result, "sametable"),
- i_conname = PQfnumber(result, "conname"),
- i_condef = PQfnumber(result, "condef"),
- i_ontable = PQfnumber(result, "ontable");
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
- printTableAddFooter(&cont, _("Foreign-key constraints:"));
- for (i = 0; i < tuples; i++)
- {
- /*
- * Print untranslated constraint name and definition. Use
- * a "TABLE tab" prefix when the constraint is defined in
- * a parent partitioned table.
- */
- if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
- printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
- PQgetvalue(result, i, i_ontable),
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
- else
- printfPQExpBuffer(&buf, " \"%s\" %s",
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
+ if (tuples > 0)
+ {
+ int i_sametable = PQfnumber(result, "sametable"),
+ i_conname = PQfnumber(result, "conname"),
+ i_condef = PQfnumber(result, "condef"),
+ i_ontable = PQfnumber(result, "ontable");
- printTableAddFooter(&cont, buf.data);
- }
+ printTableAddFooter(&cont, _("Foreign-key constraints:"));
+ for (i = 0; i < tuples; i++)
+ {
+ /*
+ * Print untranslated constraint name and definition. Use
+ * a "TABLE tab" prefix when the constraint is defined in
+ * a parent partitioned table.
+ */
+ if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
+ printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
+ PQgetvalue(result, i, i_ontable),
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
+ else
+ printfPQExpBuffer(&buf, " \"%s\" %s",
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
+
+ printTableAddFooter(&cont, buf.data);
}
- PQclear(result);
}
+ PQclear(result);
/* print incoming foreign-key references */
- if (tableinfo.hastriggers ||
- tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
+ if (pset.sversion >= 120000)
{
- if (pset.sversion >= 120000)
- {
- printfPQExpBuffer(&buf,
- "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
- " FROM pg_catalog.pg_constraint c\n"
- " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
- " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
- " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
- "ORDER BY conname;",
- oid, oid);
- }
- else
- {
- printfPQExpBuffer(&buf,
- "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
- " FROM pg_catalog.pg_constraint\n"
- " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
- "ORDER BY conname;",
- oid);
- }
+ printfPQExpBuffer(&buf,
+ "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
+ " FROM pg_catalog.pg_constraint c\n"
+ " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
+ " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
+ " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
+ "ORDER BY conname;",
+ oid, oid);
+ }
+ else
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
+ " FROM pg_catalog.pg_constraint\n"
+ " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
+ "ORDER BY conname;",
+ oid);
+ }
- result = PSQLexec(buf.data);
- if (!result)
- goto error_return;
- else
- tuples = PQntuples(result);
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
- if (tuples > 0)
- {
- int i_conname = PQfnumber(result, "conname"),
- i_ontable = PQfnumber(result, "ontable"),
- i_condef = PQfnumber(result, "condef");
+ if (tuples > 0)
+ {
+ int i_conname = PQfnumber(result, "conname"),
+ i_ontable = PQfnumber(result, "ontable"),
+ i_condef = PQfnumber(result, "condef");
- printTableAddFooter(&cont, _("Referenced by:"));
- for (i = 0; i < tuples; i++)
- {
- printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
- PQgetvalue(result, i, i_ontable),
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
+ printTableAddFooter(&cont, _("Referenced by:"));
+ for (i = 0; i < tuples; i++)
+ {
+ printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
+ PQgetvalue(result, i, i_ontable),
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
- printTableAddFooter(&cont, buf.data);
- }
+ printTableAddFooter(&cont, buf.data);
}
- PQclear(result);
}
+ PQclear(result);
/* print any row-level policies */
if (pset.sversion >= 90500)
--
2.49.0
From 6b09fb343378698657c0cecc6a85b85276b1f27d Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Fri, 7 Feb 2025 09:41:20 +0530
Subject: [PATCH v18.1 2/3] Add support for NOT ENFORCED in foreign key
constraints.
Typically, when a foreign key (FK) constraint is created on a table,
action and check triggers are added to maintain data integrity. With
this patch, if a constraint is marked as NOT ENFORCED, integrity
checks are no longer required, making these triggers unnecessary.
Consequently, when creating a NOT ENFORCED FK constraint, triggers
will not be created, and the constraint will be marked as NOT VALID.
Similarly, if an existing FK constraint is changed to NOT ENFORCED,
the associated triggers will be dropped, and the constraint will also
be marked as NOT VALID. Conversely, if a NOT ENFORCED FK constraint is
changed to ENFORCED, the necessary triggers will be created, and the
will be changed to VALID by performing necessary validation.
----
NOTE: In this patch, the tryAttachPartitionForeignKey() function will
not merge the constraint if the enforcibility differs. This will be
addressed in the next patch.
----
---
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/ref/alter_table.sgml | 5 +-
doc/src/sgml/ref/create_table.sgml | 2 +-
src/backend/catalog/pg_constraint.c | 5 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 457 ++++++++++++++++++----
src/backend/parser/gram.y | 15 +-
src/backend/parser/parse_utilcmd.c | 14 +-
src/backend/utils/cache/relcache.c | 1 +
src/include/nodes/parsenodes.h | 2 +
src/include/utils/rel.h | 3 +
src/test/regress/expected/constraints.out | 8 +-
src/test/regress/expected/foreign_key.out | 161 +++++++-
src/test/regress/expected/inherit.out | 81 ++--
src/test/regress/sql/foreign_key.sql | 107 ++++-
src/test/regress/sql/inherit.sql | 7 +
16 files changed, 725 insertions(+), 147 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..014bb815665 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2620,7 +2620,7 @@ <title><structname>pg_constraint</structname> Columns</title>
</para>
<para>
Is the constraint enforced?
- Currently, can be false only for CHECK constraints
+ Currently, can be false only for foreign keys and CHECK constraints
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 11d1bc7dbe1..ece438f0075 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,7 +58,7 @@
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ INHERIT | NO INHERIT ]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
@@ -589,7 +589,8 @@ <title>Description</title>
This form validates a foreign key or check constraint that was
previously created as <literal>NOT VALID</literal>, by scanning the
table to ensure there are no rows for which the constraint is not
- satisfied. Nothing happens if the constraint is already marked valid.
+ satisfied. If the constraint is not enforced, an error is thrown.
+ Nothing happens if the constraint is already marked valid.
(See <xref linkend="sql-altertable-notes"/> below for an explanation
of the usefulness of this command.)
</para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e5c034d724e..4a41b2f5530 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1409,7 +1409,7 @@ <title>Parameters</title>
</para>
<para>
- This is currently only supported for <literal>CHECK</literal>
+ This is currently only supported for foreign key and <literal>CHECK</literal>
constraints.
</para>
</listitem>
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index ac80652baf2..0467e7442ff 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -100,8 +100,9 @@ CreateConstraintEntry(const char *constraintName,
ObjectAddresses *addrs_auto;
ObjectAddresses *addrs_normal;
- /* Only CHECK constraint can be not enforced */
- Assert(isEnforced || constraintType == CONSTRAINT_CHECK);
+ /* Only CHECK or FOREIGN KEY constraint can be not enforced */
+ Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
+ constraintType == CONSTRAINT_FOREIGN);
/* NOT ENFORCED constraint must be NOT VALID */
Assert(isEnforced || !isValidated);
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 2f250d2c57b..ebe85337c28 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -281,7 +281,7 @@ F461 Named character sets NO
F471 Scalar subquery values YES
F481 Expanded NULL predicate YES
F491 Constraint management YES
-F492 Optional table constraint enforcement NO check constraints only
+F492 Optional table constraint enforcement YES except not-null constraints
F501 Features and conformance views YES
F501 Features and conformance views 01 SQL_FEATURES view YES
F501 Features and conformance views 02 SQL_SIZING view YES
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index afb25007613..6b56d59e77d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -395,6 +395,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
bool recurse, LOCKMODE lockmode);
+static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -405,6 +413,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
bool deferrable, bool initdeferred,
List **otherrelids);
+static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -10561,7 +10577,7 @@ addFkConstraint(addFkConstraintSides fkside,
CONSTRAINT_FOREIGN,
fkconstraint->deferrable,
fkconstraint->initdeferred,
- true, /* Is Enforced */
+ fkconstraint->is_enforced,
fkconstraint->initially_valid,
parentConstr,
RelationGetRelid(rel),
@@ -10679,21 +10695,23 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
Oid parentDelTrigger, Oid parentUpdTrigger,
bool with_period)
{
- Oid deleteTriggerOid,
- updateTriggerOid;
+ Oid deleteTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
Assert(CheckRelationLockedByMe(pkrel, ShareRowExclusiveLock, true));
Assert(CheckRelationLockedByMe(rel, ShareRowExclusiveLock, true));
/*
- * Create the action triggers that enforce the constraint.
+ * Create action triggers to enforce the constraint, or skip them if the
+ * constraint is NOT ENFORCED.
*/
- createForeignKeyActionTriggers(RelationGetRelid(rel),
- RelationGetRelid(pkrel),
- fkconstraint,
- parentConstr, indexOid,
- parentDelTrigger, parentUpdTrigger,
- &deleteTriggerOid, &updateTriggerOid);
+ if (fkconstraint->is_enforced)
+ createForeignKeyActionTriggers(RelationGetRelid(rel),
+ RelationGetRelid(pkrel),
+ fkconstraint,
+ parentConstr, indexOid,
+ parentDelTrigger, parentUpdTrigger,
+ &deleteTriggerOid, &updateTriggerOid);
/*
* If the referenced table is partitioned, recurse on ourselves to handle
@@ -10814,8 +10832,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
Oid parentInsTrigger, Oid parentUpdTrigger,
bool with_period)
{
- Oid insertTriggerOid,
- updateTriggerOid;
+ Oid insertTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
Assert(OidIsValid(parentConstr));
Assert(CheckRelationLockedByMe(rel, ShareRowExclusiveLock, true));
@@ -10827,29 +10845,32 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
errmsg("foreign key constraints are not supported on foreign tables")));
/*
- * Add the check triggers to it and, if necessary, schedule it to be
- * checked in Phase 3.
+ * Add check triggers if the constraint is ENFORCED, and if needed,
+ * schedule them to be checked in Phase 3.
*
* If the relation is partitioned, drill down to do it to its partitions.
*/
- createForeignKeyCheckTriggers(RelationGetRelid(rel),
- RelationGetRelid(pkrel),
- fkconstraint,
- parentConstr,
- indexOid,
- parentInsTrigger, parentUpdTrigger,
- &insertTriggerOid, &updateTriggerOid);
+ if (fkconstraint->is_enforced)
+ createForeignKeyCheckTriggers(RelationGetRelid(rel),
+ RelationGetRelid(pkrel),
+ fkconstraint,
+ parentConstr,
+ indexOid,
+ parentInsTrigger, parentUpdTrigger,
+ &insertTriggerOid, &updateTriggerOid);
if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/*
* Tell Phase 3 to check that the constraint is satisfied by existing
- * rows. We can skip this during table creation, when requested
- * explicitly by specifying NOT VALID in an ADD FOREIGN KEY command,
- * and when we're recreating a constraint following a SET DATA TYPE
- * operation that did not impugn its validity.
+ * rows. We can skip this during table creation, when constraint is
+ * specified as NOT ENFORCED, or when requested explicitly by
+ * specifying NOT VALID in an ADD FOREIGN KEY command, and when we're
+ * recreating a constraint following a SET DATA TYPE operation that
+ * did not impugn its validity.
*/
- if (wqueue && !old_check_ok && !fkconstraint->skip_validation)
+ if (wqueue && !old_check_ok && !fkconstraint->skip_validation &&
+ fkconstraint->is_enforced)
{
NewConstraint *newcon;
AlteredTableInfo *tab;
@@ -11080,8 +11101,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
AttrNumber confdelsetcols[INDEX_MAX_KEYS];
Constraint *fkconstraint;
ObjectAddress address;
- Oid deleteTriggerOid,
- updateTriggerOid;
+ Oid deleteTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constrOid));
if (!HeapTupleIsValid(tuple))
@@ -11141,8 +11162,9 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
fkconstraint->fk_del_set_cols = NIL;
fkconstraint->old_conpfeqop = NIL;
fkconstraint->old_pktable_oid = InvalidOid;
+ fkconstraint->is_enforced = constrForm->conenforced;
fkconstraint->skip_validation = false;
- fkconstraint->initially_valid = true;
+ fkconstraint->initially_valid = constrForm->convalidated;
/* set up colnames that are used to generate the constraint name */
for (int i = 0; i < numfks; i++)
@@ -11170,9 +11192,10 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
* parent OIDs for similar triggers that will be created on the
* partition in addFkRecurseReferenced().
*/
- GetForeignKeyActionTriggers(trigrel, constrOid,
- constrForm->confrelid, constrForm->conrelid,
- &deleteTriggerOid, &updateTriggerOid);
+ if (constrForm->conenforced)
+ GetForeignKeyActionTriggers(trigrel, constrOid,
+ constrForm->confrelid, constrForm->conrelid,
+ &deleteTriggerOid, &updateTriggerOid);
/* Add this constraint ... */
address = addFkConstraint(addFkReferencedSide,
@@ -11305,8 +11328,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
Oid indexOid;
ObjectAddress address;
ListCell *lc;
- Oid insertTriggerOid,
- updateTriggerOid;
+ Oid insertTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
bool with_period;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid));
@@ -11338,17 +11361,18 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
mapped_conkey[i] = attmap->attnums[conkey[i] - 1];
/*
- * Get the "check" triggers belonging to the constraint to pass as
- * parent OIDs for similar triggers that will be created on the
- * partition in addFkRecurseReferencing(). They are also passed to
- * tryAttachPartitionForeignKey() below to simply assign as parents to
- * the partition's existing "check" triggers, that is, if the
- * corresponding constraints is deemed attachable to the parent
- * constraint.
+ * Get the "check" triggers belonging to the constraint, if it is
+ * ENFORCED, to pass as parent OIDs for similar triggers that will be
+ * created on the partition in addFkRecurseReferencing(). They are
+ * also passed to tryAttachPartitionForeignKey() below to simply
+ * assign as parents to the partition's existing "check" triggers,
+ * that is, if the corresponding constraints is deemed attachable to
+ * the parent constraint.
*/
- GetForeignKeyCheckTriggers(trigrel, constrForm->oid,
- constrForm->confrelid, constrForm->conrelid,
- &insertTriggerOid, &updateTriggerOid);
+ if (constrForm->conenforced)
+ GetForeignKeyCheckTriggers(trigrel, constrForm->oid,
+ constrForm->confrelid, constrForm->conrelid,
+ &insertTriggerOid, &updateTriggerOid);
/*
* Before creating a new constraint, see whether any existing FKs are
@@ -11401,6 +11425,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
fkconstraint->fk_del_set_cols = NIL;
fkconstraint->old_conpfeqop = NIL;
fkconstraint->old_pktable_oid = InvalidOid;
+ fkconstraint->is_enforced = constrForm->conenforced;
fkconstraint->skip_validation = false;
fkconstraint->initially_valid = constrForm->convalidated;
for (int i = 0; i < numfks; i++)
@@ -11518,6 +11543,7 @@ tryAttachPartitionForeignKey(List **wqueue,
if (OidIsValid(partConstr->conparentid) ||
partConstr->condeferrable != parentConstr->condeferrable ||
partConstr->condeferred != parentConstr->condeferred ||
+ partConstr->conenforced != parentConstr->conenforced ||
partConstr->confupdtype != parentConstr->confupdtype ||
partConstr->confdeltype != parentConstr->confdeltype ||
partConstr->confmatchtype != parentConstr->confmatchtype)
@@ -11561,8 +11587,7 @@ AttachPartitionForeignKey(List **wqueue,
bool queueValidation;
Oid partConstrFrelid;
Oid partConstrRelid;
- Oid insertTriggerOid,
- updateTriggerOid;
+ bool parentConstrIsEnforced;
/* Fetch the parent constraint tuple */
parentConstrTup = SearchSysCache1(CONSTROID,
@@ -11570,6 +11595,7 @@ AttachPartitionForeignKey(List **wqueue,
if (!HeapTupleIsValid(parentConstrTup))
elog(ERROR, "cache lookup failed for constraint %u", parentConstrOid);
parentConstr = (Form_pg_constraint) GETSTRUCT(parentConstrTup);
+ parentConstrIsEnforced = parentConstr->conenforced;
/* Fetch the child constraint tuple */
partcontup = SearchSysCache1(CONSTROID,
@@ -11619,17 +11645,24 @@ AttachPartitionForeignKey(List **wqueue,
/*
* Like the constraint, attach partition's "check" triggers to the
- * corresponding parent triggers.
+ * corresponding parent triggers if the constraint is ENFORCED.
+ * NOT ENFORCED constraints do not have these triggers.
*/
- GetForeignKeyCheckTriggers(trigrel,
- partConstrOid, partConstrFrelid, partConstrRelid,
- &insertTriggerOid, &updateTriggerOid);
- Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));
- TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,
- RelationGetRelid(partition));
- Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));
- TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,
- RelationGetRelid(partition));
+ if (parentConstrIsEnforced)
+ {
+ Oid insertTriggerOid,
+ updateTriggerOid;
+
+ GetForeignKeyCheckTriggers(trigrel,
+ partConstrOid, partConstrFrelid, partConstrRelid,
+ &insertTriggerOid, &updateTriggerOid);
+ Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));
+ TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,
+ RelationGetRelid(partition));
+ Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));
+ TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,
+ RelationGetRelid(partition));
+ }
/*
* We updated this pg_constraint row above to set its parent; validating
@@ -11743,6 +11776,10 @@ RemoveInheritedConstraint(Relation conrel, Relation trigrel, Oid conoid,
*
* The subroutine for tryAttachPartitionForeignKey handles the deletion of
* action triggers for the foreign key constraint.
+ *
+ * If valid confrelid and conrelid values are not provided, the respective
+ * trigger check will be skipped, and the trigger will be considered for
+ * removal.
*/
static void
DropForeignKeyConstraintTriggers(Relation trigrel, Oid conoid, Oid confrelid,
@@ -11763,11 +11800,28 @@ DropForeignKeyConstraintTriggers(Relation trigrel, Oid conoid, Oid confrelid,
Form_pg_trigger trgform = (Form_pg_trigger) GETSTRUCT(trigtup);
ObjectAddress trigger;
- if (trgform->tgconstrrelid != conrelid)
+ /* Invalid if trigger is not for a referential integrity constraint */
+ if (!OidIsValid(trgform->tgconstrrelid))
continue;
- if (trgform->tgrelid != confrelid)
+ if (OidIsValid(conrelid) && trgform->tgconstrrelid != conrelid)
+ continue;
+ if (OidIsValid(confrelid) && trgform->tgrelid != confrelid)
continue;
+ /* We should be droping trigger related to foreign key constraint */
+ Assert(trgform->tgfoid == F_RI_FKEY_CHECK_INS ||
+ trgform->tgfoid == F_RI_FKEY_CHECK_UPD ||
+ trgform->tgfoid == F_RI_FKEY_CASCADE_DEL ||
+ trgform->tgfoid == F_RI_FKEY_CASCADE_UPD ||
+ trgform->tgfoid == F_RI_FKEY_RESTRICT_DEL ||
+ trgform->tgfoid == F_RI_FKEY_RESTRICT_UPD ||
+ trgform->tgfoid == F_RI_FKEY_SETNULL_DEL ||
+ trgform->tgfoid == F_RI_FKEY_SETNULL_UPD ||
+ trgform->tgfoid == F_RI_FKEY_SETDEFAULT_DEL ||
+ trgform->tgfoid == F_RI_FKEY_SETDEFAULT_UPD ||
+ trgform->tgfoid == F_RI_FKEY_NOACTION_DEL ||
+ trgform->tgfoid == F_RI_FKEY_NOACTION_UPD);
+
/*
* The constraint is originally set up to contain this trigger as an
* implementation object, so there's a dependency record that links
@@ -11979,6 +12033,11 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
+ cmdcon->conname, RelationGetRelationName(rel))));
if (cmdcon->alterInheritability &&
currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
@@ -12058,7 +12117,7 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
/*
* A subroutine of ATExecAlterConstraint that calls the respective routines for
- * altering constraint attributes.
+ * altering constraint's enforceability, deferrability or inheritability.
*/
static bool
ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
@@ -12066,16 +12125,35 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
HeapTuple contuple, bool recurse,
LOCKMODE lockmode)
{
+ Form_pg_constraint currcon;
bool changed = false;
List *otherrelids = NIL;
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
/*
- * Do the catalog work for the deferrability change, recurse if necessary.
- */
- if (cmdcon->alterDeferrability &&
- ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
- contuple, recurse, &otherrelids,
- lockmode))
+ * Do the catalog work for the enforceability or deferrability change,
+ * recurse if necessary.
+ *
+ * Note that even if deferrability is requested to be altered along with
+ * enforceability, we don't need to explicitly update multiple entries in
+ * pg_trigger related to deferrability.
+ *
+ * Modifying enforceability involves either creating or dropping the
+ * trigger, during which the deferrability setting will be adjusted
+ * automatically.
+ */
+ if (cmdcon->alterEnforceability &&
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+ currcon->conrelid, currcon->confrelid,
+ contuple, lockmode, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid))
+ changed = true;
+
+ else if (cmdcon->alterDeferrability &&
+ ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
+ contuple, recurse, &otherrelids,
+ lockmode))
{
/*
* AlterConstrUpdateConstraintEntry already invalidated relcache for
@@ -12100,6 +12178,151 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
return changed;
}
+/*
+ * Returns true if the constraint's enforceability is altered.
+ *
+ * Depending on whether the constraint is being set to ENFORCED or NOT
+ * ENFORCED, it creates or drops the trigger accordingly.
+ *
+ * Note that we must recurse even when trying to change a constraint to not
+ * enforced if it is already not enforced, in case descendant constraints
+ * might be enforced and need to be changed to not enforced. Conversely, we
+ * should do nothing if a constraint is being set to enforced and is already
+ * enforced, as descendant constraints cannot be different in that case.
+ */
+static bool
+ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
+{
+ Form_pg_constraint currcon;
+ Oid conoid;
+ Relation rel;
+ bool changed = false;
+
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ Assert(cmdcon->alterEnforceability);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ conoid = currcon->oid;
+
+ /* Should be foreign key constraint */
+ Assert(currcon->contype == CONSTRAINT_FOREIGN);
+
+ rel = table_open(currcon->conrelid, lockmode);
+
+ if (currcon->conenforced != cmdcon->is_enforced)
+ {
+ AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+ changed = true;
+ }
+
+ /* Drop triggers */
+ if (!cmdcon->is_enforced)
+ {
+ /*
+ * When setting a constraint to NOT ENFORCED, the constraint triggers
+ * need to be dropped. Therefore, we must process the child relations
+ * first, followed by the parent, to account for dependencies.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid);
+
+ /* Drop all the triggers */
+ DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
+ }
+ else if (changed) /* Create triggers */
+ {
+ Oid ReferencedDelTriggerOid = InvalidOid,
+ ReferencedUpdTriggerOid = InvalidOid,
+ ReferencingInsTriggerOid = InvalidOid,
+ ReferencingUpdTriggerOid = InvalidOid;
+
+ /* Prepare the minimal information required for trigger creation. */
+ Constraint *fkconstraint = makeNode(Constraint);
+
+ fkconstraint->conname = pstrdup(NameStr(currcon->conname));
+ fkconstraint->fk_matchtype = currcon->confmatchtype;
+ fkconstraint->fk_upd_action = currcon->confupdtype;
+ fkconstraint->fk_del_action = currcon->confdeltype;
+
+ /* Create referenced triggers */
+ if (currcon->conrelid == fkrelid)
+ createForeignKeyActionTriggers(currcon->conrelid,
+ currcon->confrelid,
+ fkconstraint,
+ conoid,
+ currcon->conindid,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ &ReferencedDelTriggerOid,
+ &ReferencedUpdTriggerOid);
+
+ /* Create referencing triggers */
+ if (currcon->confrelid == pkrelid)
+ createForeignKeyCheckTriggers(currcon->conrelid,
+ pkrelid,
+ fkconstraint,
+ conoid,
+ currcon->conindid,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger,
+ &ReferencingInsTriggerOid,
+ &ReferencingUpdTriggerOid);
+
+ /*
+ * Tell Phase 3 to check that the constraint is satisfied by existing
+ * rows.
+ */
+ if (rel->rd_rel->relkind == RELKIND_RELATION)
+ {
+ AlteredTableInfo *tab;
+ NewConstraint *newcon;
+
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = fkconstraint->conname;
+ newcon->contype = CONSTR_FOREIGN;
+ newcon->refrelid = currcon->confrelid;
+ newcon->refindid = currcon->conindid;
+ newcon->conid = currcon->oid;
+ newcon->qual = (Node *) fkconstraint;
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
+ /*
+ * If the table at either end of the constraint is partitioned, we
+ * need to recurse and create triggers for each constraint that is a
+ * child of this one.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode, ReferencedDelTriggerOid,
+ ReferencedUpdTriggerOid,
+ ReferencingInsTriggerOid,
+ ReferencingUpdTriggerOid);
+ }
+
+ table_close(rel, NoLock);
+
+ return changed;
+}
+
/*
* Returns true if the constraint's deferrability is altered.
*
@@ -12304,6 +12527,55 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
systable_endscan(tgscan);
}
+/*
+ * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
+ * the specified constraint.
+ *
+ * Note that this doesn't handle recursion the normal way, viz. by scanning the
+ * list of child relations and recursing; instead it uses the conparentid
+ * relationships. This may need to be reconsidered.
+ *
+ * The arguments to this function have the same meaning as the arguments to
+ * ATExecAlterConstrEnforceability.
+ */
+static void
+AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
+{
+ Form_pg_constraint currcon;
+ Oid conoid;
+ ScanKeyData pkey;
+ SysScanDesc pscan;
+ HeapTuple childtup;
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ conoid = currcon->oid;
+
+ ScanKeyInit(&pkey,
+ Anum_pg_constraint_conparentid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conoid));
+
+ pscan = systable_beginscan(conrel, ConstraintParentIndexId,
+ true, NULL, 1, &pkey);
+
+ while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+ pkrelid, childtup, lockmode,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger);
+
+ systable_endscan(pscan);
+}
+
/*
* Invokes ATExecAlterConstrDeferrability for each constraint that is a child of
* the specified constraint.
@@ -12364,11 +12636,25 @@ AlterConstrUpdateConstraintEntry(ATAlterConstraint *cmdcon, Relation conrel,
HeapTuple copyTuple;
Form_pg_constraint copy_con;
- Assert(cmdcon->alterDeferrability || cmdcon->alterInheritability);
+ Assert(cmdcon->alterEnforceability || cmdcon->alterDeferrability ||
+ cmdcon->alterInheritability);
copyTuple = heap_copytuple(contuple);
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ if (cmdcon->alterEnforceability)
+ {
+ copy_con->conenforced = cmdcon->is_enforced;
+
+ /*
+ * NB: The convalidated status is irrelevant when the constraint is
+ * set to NOT ENFORCED, but for consistency, it should still be set
+ * appropriately. Similarly, if the constraint is later changed to
+ * ENFORCED, validation will be performed during phase 3, so it makes
+ * sense to mark it as valid in that case.
+ */
+ copy_con->convalidated = cmdcon->is_enforced;
+ }
if (cmdcon->alterDeferrability)
{
copy_con->condeferrable = cmdcon->deferrable;
@@ -17088,9 +17374,9 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
NameStr(child_con->conname), RelationGetRelationName(child_rel))));
/*
- * A non-enforced child constraint cannot be merged with an
- * enforced parent constraint. However, the reverse is allowed,
- * where the child constraint is enforced.
+ * A NOT ENFORCED child constraint cannot be merged with an
+ * ENFORCED parent constraint. However, the reverse is allowed,
+ * where the child constraint is ENFORCED.
*/
if (parent_con->conenforced && !child_con->conenforced)
ereport(ERROR,
@@ -20461,8 +20747,6 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
Form_pg_constraint conform;
- Oid insertTriggerOid,
- updateTriggerOid;
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
if (!HeapTupleIsValid(contup))
@@ -20489,17 +20773,25 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
/*
* Also, look up the partition's "check" triggers corresponding to the
- * constraint being detached and detach them from the parent triggers.
+ * ENFORCED constraint being detached and detach them from the parent
+ * triggers. NOT ENFORCED constraints do not have these triggers;
+ * therefore, this step is not needed.
*/
- GetForeignKeyCheckTriggers(trigrel,
- fk->conoid, fk->confrelid, fk->conrelid,
- &insertTriggerOid, &updateTriggerOid);
- Assert(OidIsValid(insertTriggerOid));
- TriggerSetParentTrigger(trigrel, insertTriggerOid, InvalidOid,
- RelationGetRelid(partRel));
- Assert(OidIsValid(updateTriggerOid));
- TriggerSetParentTrigger(trigrel, updateTriggerOid, InvalidOid,
- RelationGetRelid(partRel));
+ if (fk->conenforced)
+ {
+ Oid insertTriggerOid,
+ updateTriggerOid;
+
+ GetForeignKeyCheckTriggers(trigrel,
+ fk->conoid, fk->confrelid, fk->conrelid,
+ &insertTriggerOid, &updateTriggerOid);
+ Assert(OidIsValid(insertTriggerOid));
+ TriggerSetParentTrigger(trigrel, insertTriggerOid, InvalidOid,
+ RelationGetRelid(partRel));
+ Assert(OidIsValid(updateTriggerOid));
+ TriggerSetParentTrigger(trigrel, updateTriggerOid, InvalidOid,
+ RelationGetRelid(partRel));
+ }
/*
* Lastly, create the action triggers on the referenced table, using
@@ -20539,8 +20831,9 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
fkconstraint->conname = pstrdup(NameStr(conform->conname));
fkconstraint->deferrable = conform->condeferrable;
fkconstraint->initdeferred = conform->condeferred;
+ fkconstraint->is_enforced = conform->conenforced;
fkconstraint->skip_validation = true;
- fkconstraint->initially_valid = true;
+ fkconstraint->initially_valid = conform->convalidated;
/* a few irrelevant fields omitted here */
fkconstraint->pktable = NULL;
fkconstraint->fk_attrs = NIL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0fc502a3a40..c982f856559 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2665,12 +2665,17 @@ alter_table_cmd:
if ($4 & (CAS_DEFERRABLE | CAS_NOT_DEFERRABLE |
CAS_INITIALLY_DEFERRED | CAS_INITIALLY_IMMEDIATE))
c->alterDeferrability = true;
+ if ($4 & (CAS_NOT_ENFORCED | CAS_ENFORCED))
+ c->alterEnforceability = true;
if ($4 & CAS_NO_INHERIT)
c->alterInheritability = true;
processCASbits($4, @4, "FOREIGN KEY",
- &c->deferrable,
- &c->initdeferred,
- NULL, NULL, &c->noinherit, yyscanner);
+ &c->deferrable,
+ &c->initdeferred,
+ &c->is_enforced,
+ NULL,
+ &c->noinherit,
+ yyscanner);
$$ = (Node *) n;
}
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT */
@@ -4334,8 +4339,8 @@ ConstraintElem:
n->fk_del_set_cols = ($11)->deleteAction->cols;
processCASbits($12, @12, "FOREIGN KEY",
&n->deferrable, &n->initdeferred,
- NULL, &n->skip_validation, NULL,
- yyscanner);
+ &n->is_enforced, &n->skip_validation,
+ NULL, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 896a7f2c59b..50f430a4e1a 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2976,8 +2976,10 @@ transformFKConstraints(CreateStmtContext *cxt,
/*
* If CREATE TABLE or adding a column with NULL default, we can safely
- * skip validation of FK constraints, and nonetheless mark them valid.
- * (This will override any user-supplied NOT VALID flag.)
+ * skip validation of FK constraints, and mark them as valid based on the
+ * constraint enforcement flag, since NOT ENFORCED constraints must always
+ * be marked as NOT VALID. (This will override any user-supplied NOT VALID
+ * flag.)
*/
if (skipValidation)
{
@@ -2986,7 +2988,7 @@ transformFKConstraints(CreateStmtContext *cxt,
Constraint *constraint = (Constraint *) lfirst(fkclist);
constraint->skip_validation = true;
- constraint->initially_valid = true;
+ constraint->initially_valid = constraint->is_enforced;
}
}
@@ -3981,7 +3983,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_ENFORCED:
if (lastprimarycon == NULL ||
- lastprimarycon->contype != CONSTR_CHECK)
+ (lastprimarycon->contype != CONSTR_CHECK &&
+ lastprimarycon->contype != CONSTR_FOREIGN))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced ENFORCED clause"),
@@ -3997,7 +4000,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_NOT_ENFORCED:
if (lastprimarycon == NULL ||
- lastprimarycon->contype != CONSTR_CHECK)
+ (lastprimarycon->contype != CONSTR_CHECK &&
+ lastprimarycon->contype != CONSTR_FOREIGN))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced NOT ENFORCED clause"),
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index e6721056536..18a14ae186e 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4697,6 +4697,7 @@ RelationGetFKeyList(Relation relation)
info->conoid = constraint->oid;
info->conrelid = constraint->conrelid;
info->confrelid = constraint->confrelid;
+ info->conenforced = constraint->conenforced;
DeconstructFkConstraintRow(htup, &info->nkeys,
info->conkey,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index df331b1c0d9..00fefa9483a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2495,6 +2495,8 @@ typedef struct ATAlterConstraint
{
NodeTag type;
char *conname; /* Constraint name */
+ bool alterEnforceability; /* changing enforceability properties? */
+ bool is_enforced; /* ENFORCED? */
bool alterDeferrability; /* changing deferrability properties? */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index d94fddd7cef..b552359915f 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -284,6 +284,9 @@ typedef struct ForeignKeyCacheInfo
/* number of columns in the foreign key */
int nkeys;
+ /* Is enforced ? */
+ bool conenforced;
+
/*
* these arrays each have nkeys valid entries:
*/
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..a719d2f74e9 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -745,13 +745,9 @@ ERROR: misplaced NOT ENFORCED clause
LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
^
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked ENFORCED
-LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
- ^
+ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked NOT ENFORCED
-LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC...
- ^
+ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
DROP TABLE unique_tbl;
--
-- EXCLUDE constraints
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 7f678349a8e..1d0a4548732 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1,12 +1,43 @@
--
-- FOREIGN KEY
--
--- MATCH FULL
+-- NOT ENFORCED
--
-- First test, check and cascade
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
-CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED,
+ ftest2 int );
+-- Inserting into the foreign key table will not result in an error, even if
+-- there is no matching key in the referenced table.
+INSERT INTO FKTABLE VALUES (1, 2);
+INSERT INTO FKTABLE VALUES (2, 3);
+-- Check FKTABLE
+SELECT * FROM FKTABLE;
+ ftest1 | ftest2
+--------+--------
+ 1 | 2
+ 2 | 3
+(2 rows)
+
+-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered,
+-- as it was previously in a valid state.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
+DETAIL: Key (ftest1)=(1) is not present in table "pktable".
+-- Remove any existing rows that violate the constraint, then attempt to change
+-- it.
+TRUNCATE FKTABLE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+-- Any further inserts will fail due to the enforcement.
+INSERT INTO FKTABLE VALUES (3, 4);
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
+DETAIL: Key (ftest1)=(3) is not present in table "pktable".
+--
+-- MATCH FULL
+--
+-- First test, check and cascade
+--
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
INSERT INTO PKTABLE VALUES (2, 'Test2');
@@ -351,6 +382,43 @@ INSERT INTO FKTABLE VALUES (1, NULL);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
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.
+-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa
+ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | f | f
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | f | f
+(1 row)
+
+-- Enforceability also changes the validate state, as data validation will be
+-- performed during this transformation.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | t | t
+(1 row)
+
+-- Can change enforceability and deferrability together
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ f | f | f | f
+(1 row)
+
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- MATCH SIMPLE
@@ -1276,6 +1344,13 @@ INSERT INTO fktable VALUES (0, 20);
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
DETAIL: Key (fk)=(20) is not present in table "pktable".
COMMIT;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+BEGIN;
+-- doesn't match FK, no error.
+UPDATE pktable SET id = 10 WHERE id = 5;
+-- doesn't match PK, no error.
+INSERT INTO fktable VALUES (0, 20);
+ROLLBACK;
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- illegal options
@@ -1289,6 +1364,14 @@ ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
^
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED;
+ERROR: conflicting constraint properties
+LINE 1: ...fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORC...
+ ^
+CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED);
+ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed
+LINE 1: ...ABLE fktable2 (fk int references pktable ENFORCED NOT ENFORC...
+ ^
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row. This was broken by subtransaction-related
-- changes in 8.0.
@@ -1586,10 +1669,13 @@ ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1;
CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int);
ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b)
+ REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
@@ -1612,10 +1698,10 @@ INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
@@ -1665,6 +1751,37 @@ Indexes:
Referenced by:
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+-- Check the exsting FK trigger
+CREATE TEMP TABLE tmp_trg_info AS SELECT tgtype, tgrelid::regclass, tgconstraint
+FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+SELECT count(1) FROM tmp_trg_info;
+ count
+-------
+ 14
+(1 row)
+
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+-- No triggers
+SELECT count(1) FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+ count
+-------
+ 0
+(1 row)
+
+-- Changing it back to ENFORCED will recreate the necessary triggers.
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+-- Should be exactly the same number of triggers found as before
+SELECT COUNT(1) FROM pg_trigger pgt JOIN tmp_trg_info tt
+ON (pgt.tgtype = tt.tgtype AND pgt.tgrelid = tt.tgrelid AND pgt.tgconstraint = tt.tgconstraint);
+ count
+-------
+ 14
+(1 row)
+
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
@@ -1962,6 +2079,40 @@ Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
Foreign-key constraints:
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
+BEGIN;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should be having two constraints
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ "fk_partitioned_fk_2_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+DROP TABLE fk_partitioned_fk_2;
+ROLLBACK;
+BEGIN;
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
+
+ROLLBACK;
DROP TABLE fk_partitioned_fk_2;
CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 4d07d0bd79b..caab1164fdb 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1332,6 +1332,13 @@ NOTICE: merging constraint "inh_check_constraint5" with inherited definition
alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced;
alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced;
NOTICE: merging constraint "inh_check_constraint6" with inherited definition
+alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced;
+alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced;
+NOTICE: merging constraint "inh_check_constraint9" with inherited definition
+-- the invalid state of the child constraint will be ignored here.
+alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced;
+alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced;
+NOTICE: merging constraint "inh_check_constraint10" with inherited definition
create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1);
NOTICE: merging column "f1" with inherited definition
NOTICE: merging constraint "inh_check_constraint4" with inherited definition
@@ -1356,39 +1363,47 @@ ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constrain
select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced, convalidated
from pg_constraint where conname like 'inh\_check\_constraint%'
order by 1, 2;
- relname | conname | conislocal | coninhcount | conenforced | convalidated
----------+-----------------------+------------+-------------+-------------+--------------
- p1 | inh_check_constraint1 | t | 0 | t | t
- p1 | inh_check_constraint2 | t | 0 | t | t
- p1 | inh_check_constraint3 | t | 0 | f | f
- p1 | inh_check_constraint4 | t | 0 | f | f
- p1 | inh_check_constraint5 | t | 0 | f | f
- p1 | inh_check_constraint6 | t | 0 | f | f
- p1 | inh_check_constraint8 | t | 0 | t | t
- p1_c1 | inh_check_constraint1 | t | 1 | t | t
- p1_c1 | inh_check_constraint2 | t | 1 | t | t
- p1_c1 | inh_check_constraint3 | t | 1 | f | f
- p1_c1 | inh_check_constraint4 | t | 1 | f | f
- p1_c1 | inh_check_constraint5 | t | 1 | t | t
- p1_c1 | inh_check_constraint6 | t | 1 | t | t
- p1_c1 | inh_check_constraint7 | t | 0 | f | f
- p1_c1 | inh_check_constraint8 | f | 1 | t | t
- p1_c2 | inh_check_constraint1 | f | 1 | t | t
- p1_c2 | inh_check_constraint2 | f | 1 | t | t
- p1_c2 | inh_check_constraint3 | f | 1 | f | f
- p1_c2 | inh_check_constraint4 | t | 1 | t | t
- p1_c2 | inh_check_constraint5 | f | 1 | f | f
- p1_c2 | inh_check_constraint6 | f | 1 | f | f
- p1_c2 | inh_check_constraint8 | f | 1 | t | t
- p1_c3 | inh_check_constraint1 | f | 2 | t | t
- p1_c3 | inh_check_constraint2 | f | 2 | t | t
- p1_c3 | inh_check_constraint3 | f | 2 | f | f
- p1_c3 | inh_check_constraint4 | f | 2 | f | f
- p1_c3 | inh_check_constraint5 | f | 2 | t | t
- p1_c3 | inh_check_constraint6 | f | 2 | t | t
- p1_c3 | inh_check_constraint7 | f | 1 | f | f
- p1_c3 | inh_check_constraint8 | f | 2 | t | t
-(30 rows)
+ relname | conname | conislocal | coninhcount | conenforced | convalidated
+---------+------------------------+------------+-------------+-------------+--------------
+ p1 | inh_check_constraint1 | t | 0 | t | t
+ p1 | inh_check_constraint10 | t | 0 | f | f
+ p1 | inh_check_constraint2 | t | 0 | t | t
+ p1 | inh_check_constraint3 | t | 0 | f | f
+ p1 | inh_check_constraint4 | t | 0 | f | f
+ p1 | inh_check_constraint5 | t | 0 | f | f
+ p1 | inh_check_constraint6 | t | 0 | f | f
+ p1 | inh_check_constraint8 | t | 0 | t | t
+ p1 | inh_check_constraint9 | t | 0 | f | f
+ p1_c1 | inh_check_constraint1 | t | 1 | t | t
+ p1_c1 | inh_check_constraint10 | t | 1 | t | t
+ p1_c1 | inh_check_constraint2 | t | 1 | t | t
+ p1_c1 | inh_check_constraint3 | t | 1 | f | f
+ p1_c1 | inh_check_constraint4 | t | 1 | f | f
+ p1_c1 | inh_check_constraint5 | t | 1 | t | t
+ p1_c1 | inh_check_constraint6 | t | 1 | t | t
+ p1_c1 | inh_check_constraint7 | t | 0 | f | f
+ p1_c1 | inh_check_constraint8 | f | 1 | t | t
+ p1_c1 | inh_check_constraint9 | t | 1 | t | f
+ p1_c2 | inh_check_constraint1 | f | 1 | t | t
+ p1_c2 | inh_check_constraint10 | f | 1 | f | f
+ p1_c2 | inh_check_constraint2 | f | 1 | t | t
+ p1_c2 | inh_check_constraint3 | f | 1 | f | f
+ p1_c2 | inh_check_constraint4 | t | 1 | t | t
+ p1_c2 | inh_check_constraint5 | f | 1 | f | f
+ p1_c2 | inh_check_constraint6 | f | 1 | f | f
+ p1_c2 | inh_check_constraint8 | f | 1 | t | t
+ p1_c2 | inh_check_constraint9 | f | 1 | f | f
+ p1_c3 | inh_check_constraint1 | f | 2 | t | t
+ p1_c3 | inh_check_constraint10 | f | 2 | t | t
+ p1_c3 | inh_check_constraint2 | f | 2 | t | t
+ p1_c3 | inh_check_constraint3 | f | 2 | f | f
+ p1_c3 | inh_check_constraint4 | f | 2 | f | f
+ p1_c3 | inh_check_constraint5 | f | 2 | t | t
+ p1_c3 | inh_check_constraint6 | f | 2 | t | t
+ p1_c3 | inh_check_constraint7 | f | 1 | f | f
+ p1_c3 | inh_check_constraint8 | f | 2 | t | t
+ p1_c3 | inh_check_constraint9 | f | 2 | t | t
+(38 rows)
drop table p1 cascade;
NOTICE: drop cascades to 3 other objects
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 44945b0453a..7e5be0702da 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2,13 +2,39 @@
-- FOREIGN KEY
--
--- MATCH FULL
+-- NOT ENFORCED
--
-- First test, check and cascade
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
-CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED,
+ ftest2 int );
+
+-- Inserting into the foreign key table will not result in an error, even if
+-- there is no matching key in the referenced table.
+INSERT INTO FKTABLE VALUES (1, 2);
+INSERT INTO FKTABLE VALUES (2, 3);
+
+-- Check FKTABLE
+SELECT * FROM FKTABLE;
+
+-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered,
+-- as it was previously in a valid state.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+
+-- Remove any existing rows that violate the constraint, then attempt to change
+-- it.
+TRUNCATE FKTABLE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+-- Any further inserts will fail due to the enforcement.
+INSERT INTO FKTABLE VALUES (3, 4);
+
+--
+-- MATCH FULL
+--
+-- First test, check and cascade
+--
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
INSERT INTO PKTABLE VALUES (2, 'Test2');
@@ -230,6 +256,27 @@ CREATE TABLE FKTABLE ( ftest1 int, ftest2 int );
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
+-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa
+ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+-- Enforceability also changes the validate state, as data validation will be
+-- performed during this transformation.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+-- Can change enforceability and deferrability together
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
@@ -968,12 +1015,25 @@ CREATE TEMP TABLE fktable (
COMMIT;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+
+BEGIN;
+
+-- doesn't match FK, no error.
+UPDATE pktable SET id = 10 WHERE id = 5;
+-- doesn't match PK, no error.
+INSERT INTO fktable VALUES (0, 20);
+
+ROLLBACK;
+
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- illegal options
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT;
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED;
+CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED);
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row. This was broken by subtransaction-related
@@ -1184,11 +1244,13 @@ CREATE TABLE fk_partitioned_fk (b int, fdrop1 int, a int) PARTITION BY RANGE (a,
CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int);
ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b)
+ REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
-
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
@@ -1234,6 +1296,27 @@ CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES W
UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500;
-- check psql behavior
\d fk_notpartitioned_pk
+
+-- Check the exsting FK trigger
+CREATE TEMP TABLE tmp_trg_info AS SELECT tgtype, tgrelid::regclass, tgconstraint
+FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+SELECT count(1) FROM tmp_trg_info;
+
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+-- No triggers
+SELECT count(1) FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+
+-- Changing it back to ENFORCED will recreate the necessary triggers.
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+
+-- Should be exactly the same number of triggers found as before
+SELECT COUNT(1) FROM pg_trigger pgt JOIN tmp_trg_info tt
+ON (pgt.tgtype = tt.tgtype AND pgt.tgrelid = tt.tgrelid AND pgt.tgconstraint = tt.tgconstraint);
+
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
@@ -1441,6 +1524,22 @@ CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
\d fk_partitioned_fk_2
DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
+BEGIN;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should be having two constraints
+\d fk_partitioned_fk_2
+DROP TABLE fk_partitioned_fk_2;
+ROLLBACK;
+BEGIN;
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ROLLBACK;
+DROP TABLE fk_partitioned_fk_2;
+
CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 941189761fd..5f0b2617464 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -481,6 +481,13 @@ CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints)
alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced;
alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced;
+alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced;
+alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced;
+
+-- the invalid state of the child constraint will be ignored here.
+alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced;
+alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced;
+
create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1);
-- but reverse is not allowed
--
2.49.0
From c80eabbaf55edb4c44729499e9c9cd16596f1ddf Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Mon, 10 Feb 2025 10:59:28 +0530
Subject: [PATCH v18.1 3/3] Merge the parent and child constraints with
differing enforcibility.
If an ENFORCED parent constraint is attached to a NOT ENFORCED child
constraint, the child constraint will be made ENFORCED, with
validation applied if the parent constraint is validated as well.
Otherwise, a new ENFORCED constraint (with validation, if the parent
constraint is validated) would need to be created on the child table,
which would be unnecessary if a similar constraint already exists and
can be attached.
On the other hand, having a NOT ENFORCED parent constraint with an
ENFORCED child constraint does not cause any issues, and no changes
are required.
----
NOTE: This patch is intended to reduce the diff noise from the main
patch and is not meant to be committed separately. It should be
squashed with the main patch that adds ENFORCED/NOT ENFORCED.
----
---
src/backend/commands/tablecmds.c | 164 ++++++++++++++++++++--
src/test/regress/expected/foreign_key.out | 77 ++++++++--
src/test/regress/sql/foreign_key.sql | 37 ++++-
3 files changed, 253 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b56d59e77d..e2707132771 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11543,7 +11543,6 @@ tryAttachPartitionForeignKey(List **wqueue,
if (OidIsValid(partConstr->conparentid) ||
partConstr->condeferrable != parentConstr->condeferrable ||
partConstr->condeferred != parentConstr->condeferred ||
- partConstr->conenforced != parentConstr->conenforced ||
partConstr->confupdtype != parentConstr->confupdtype ||
partConstr->confdeltype != parentConstr->confdeltype ||
partConstr->confmatchtype != parentConstr->confmatchtype)
@@ -11588,6 +11587,8 @@ AttachPartitionForeignKey(List **wqueue,
Oid partConstrFrelid;
Oid partConstrRelid;
bool parentConstrIsEnforced;
+ bool partConstrIsEnforced;
+ bool partConstrParentIsSet;
/* Fetch the parent constraint tuple */
parentConstrTup = SearchSysCache1(CONSTROID,
@@ -11603,13 +11604,47 @@ AttachPartitionForeignKey(List **wqueue,
if (!HeapTupleIsValid(partcontup))
elog(ERROR, "cache lookup failed for constraint %u", partConstrOid);
partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
+ partConstrIsEnforced = partConstr->conenforced;
partConstrFrelid = partConstr->confrelid;
partConstrRelid = partConstr->conrelid;
+ /*
+ * The case where the parent constraint is NOT ENFORCED and the child
+ * constraint is ENFORCED is acceptable because the not enforced parent
+ * constraint lacks triggers, eliminating any redundancy issues with the
+ * enforced child constraint. In this scenario, the child constraint
+ * remains enforced, and its trigger is retained, ensuring that
+ * referential integrity checks for the child continue as before, even
+ * with the parent constraint not enforced. The relationship between the
+ * two constraints is preserved by setting the parent constraint, which
+ * allows us to locate the child constraint. This becomes important if the
+ * parent constraint is later changed to enforced, at which point the
+ * necessary trigger will be created for the parent, and any redundancy
+ * from these triggers will be appropriately handled.
+ */
+ if (!parentConstrIsEnforced && partConstrIsEnforced)
+ {
+ ReleaseSysCache(partcontup);
+ ReleaseSysCache(parentConstrTup);
+
+ ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
+ RelationGetRelid(partition));
+ CommandCounterIncrement();
+
+ return;
+ }
+
/*
* If the referenced table is partitioned, then the partition we're
* attaching now has extra pg_constraint rows and action triggers that are
* no longer needed. Remove those.
+ *
+ * Note that this must be done beforehand, particularly in situations
+ * where we might decide to change the constraint to an ENFORCED state
+ * which will create the required triggers and add the child constraint to
+ * the validation queue. To avoid generating unnecessary triggers and
+ * adding them to the validation queue, it is crucial to eliminate any
+ * redundant constraints beforehand.
*/
if (get_rel_relkind(partConstrFrelid) == RELKIND_PARTITIONED_TABLE)
{
@@ -11628,6 +11663,53 @@ AttachPartitionForeignKey(List **wqueue,
*/
queueValidation = parentConstr->convalidated && !partConstr->convalidated;
+ /*
+ * The case where the parent constraint is ENFORCED and the child
+ * constraint is NOT ENFORCED is not acceptable, as it would violate
+ * referential integrity. In such cases, the child constraint will first
+ * be enforced before merging it with the enforced parent constraint.
+ * Subsequently, removing action triggers, setting up constraint triggers,
+ * and handling check triggers for the parent will be managed in the usual
+ * manner, similar to how two enforced constraints are merged.
+ */
+ if (parentConstrIsEnforced && !partConstrIsEnforced)
+ {
+ ATAlterConstraint *cmdcon = makeNode(ATAlterConstraint);
+ Relation conrel;
+
+ cmdcon->conname = NameStr(partConstr->conname);
+ cmdcon->deferrable = partConstr->condeferrable;
+ cmdcon->initdeferred = partConstr->condeferred;
+ cmdcon->alterEnforceability = true;
+ cmdcon->is_enforced = true;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, trigrel,
+ partConstr->conrelid,
+ partConstr->confrelid,
+ partcontup, AccessExclusiveLock,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid);
+
+ table_close(conrel, RowExclusiveLock);
+
+ CommandCounterIncrement();
+
+ /*
+ * No further validation is needed, as changing the constraint to
+ * enforced will implicitly trigger the same validation.
+ */
+ queueValidation = false;
+ }
+
+ /*
+ * The constraint parent shouldn't be set beforehand, or if it's already
+ * set, it should be the specified parent.
+ */
+ partConstrParentIsSet = OidIsValid(partConstr->conparentid);
+ Assert(!partConstrParentIsSet || partConstr->conparentid == parentConstrOid);
+
ReleaseSysCache(partcontup);
ReleaseSysCache(parentConstrTup);
@@ -11640,8 +11722,10 @@ AttachPartitionForeignKey(List **wqueue,
DropForeignKeyConstraintTriggers(trigrel, partConstrOid, partConstrFrelid,
partConstrRelid);
- ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
- RelationGetRelid(partition));
+ /* Skip if the parent is already set */
+ if (!partConstrParentIsSet)
+ ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
+ RelationGetRelid(partition));
/*
* Like the constraint, attach partition's "check" triggers to the
@@ -12241,6 +12325,17 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
/* Drop all the triggers */
DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
+
+ /*
+ * If the referenced table is partitioned, the child constraint we're
+ * changing to NOT ENFORCED may have additional pg_constraint rows and
+ * action triggers that remain untouched while this child constraint
+ * is attached to the NOT ENFORCED parent. These must now be removed.
+ * For more details, see AttachPartitionForeignKey().
+ */
+ if (OidIsValid(currcon->conparentid) &&
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ RemoveInheritedConstraint(conrel, tgrel, currcon->conrelid, conoid);
}
else if (changed) /* Create triggers */
{
@@ -12566,13 +12661,40 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
true, NULL, 1, &pkey);
while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
- ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
- pkrelid, childtup, lockmode,
- ReferencedParentDelTrigger,
- ReferencedParentUpdTrigger,
- ReferencingParentInsTrigger,
- ReferencingParentUpdTrigger);
+ {
+ Form_pg_constraint childcon;
+
+ childcon = (Form_pg_constraint) GETSTRUCT(childtup);
+
+ /*
+ * When the parent constraint is modified to be ENFORCED, and the
+ * child constraint is attached to the parent constraint (which is
+ * already ENFORCED), some constraints and action triggers on the
+ * child table may become redundant and need to be removed.
+ */
+ if (cmdcon->is_enforced && childcon->conenforced)
+ {
+ if (currcon->confrelid == pkrelid)
+ {
+ Relation rel = table_open(childcon->conrelid, lockmode);
+
+ AttachPartitionForeignKey(wqueue, rel, childcon->oid,
+ conoid,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger,
+ tgrel);
+ table_close(rel, NoLock);
+ }
+ }
+ else
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+ pkrelid, childtup, lockmode,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger);
+ }
systable_endscan(pscan);
}
@@ -20746,7 +20868,9 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
{
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
+ HeapTuple parentContup;
Form_pg_constraint conform;
+ Oid parentConstrIsEnforced;
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
if (!HeapTupleIsValid(contup))
@@ -20765,12 +20889,34 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
continue;
}
+ /* Get the enforcibility of the parent constraint */
+ parentContup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(conform->conparentid));
+ if (!HeapTupleIsValid(parentContup))
+ elog(ERROR, "cache lookup failed for constraint %u",
+ conform->conparentid);
+ parentConstrIsEnforced =
+ ((Form_pg_constraint) GETSTRUCT(parentContup))->conenforced;
+ ReleaseSysCache(parentContup);
+
/*
* The constraint on this table must be marked no longer a child of
* the parent's constraint, as do its check triggers.
*/
ConstraintSetParentConstraint(fk->conoid, InvalidOid, InvalidOid);
+ /*
+ * Unsetting the parent is sufficient when the parent constraint is
+ * NOT ENFORCED and the child constraint is ENFORCED, as we link them
+ * by setting the constraint parent, while leaving the rest unchanged.
+ * For more details, see AttachPartitionForeignKey().
+ */
+ if (!parentConstrIsEnforced && fk->conenforced)
+ {
+ ReleaseSysCache(contup);
+ continue;
+ }
+
/*
* Also, look up the partition's "check" triggers corresponding to the
* ENFORCED constraint being detached and detach them from the parent
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 1d0a4548732..e4e45d0668a 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1673,7 +1673,6 @@ ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN
REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
-ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
@@ -1682,8 +1681,65 @@ ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
DROP COLUMN fdrop3, DROP COLUMN fdrop4;
CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 1);
+-- Merge the not-enforced parent constraint with the enforced and not-enforced child constraints
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+-- Merge the non-enforced parent constraint with both the enforced and
+-- non-enforced child constraints, where the referenced table is partitioned.
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+-- Merge the enforced parent constraint with the enforced and not-enforced child constraints.
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | f | f | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | f | f | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | t | t | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | t | t | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
+ALTER TABLE fk_partitioned_fk_3 DETACH PARTITION fk_partitioned_fk_3_0;
+ALTER TABLE fk_partitioned_fk_3 ATTACH PARTITION fk_partitioned_fk_3_0 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | f | f | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | f | f | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | t | t | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | t | t | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
+ALTER TABLE fk_partitioned_fk_3 ALTER CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 NOT ENFORCED;
+-- Merging an enforced parent constraint (validated) with a not-enforced child
+-- constraint will implicitly change the child constraint to enforced and apply
+-- the validation as well.
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
FOR VALUES FROM (2000,2000) TO (3000,3000);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | t | t | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | t | t | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | f | f | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | f | f | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | f | f | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | f | f | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
-- Creating a foreign key with ONLY on a partitioned table referencing
-- a non-partitioned table fails.
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
@@ -1698,22 +1754,22 @@ INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_b_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_b_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
-- but if we insert the values that make them valid, then they work
INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
@@ -1724,7 +1780,7 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-- this update fails because there is no referenced row
UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501;
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
-- but we can fix it thusly:
INSERT INTO fk_notpartitioned_pk (a,b) VALUES (2502, 2503);
@@ -1867,8 +1923,6 @@ WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid::regclass:
DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
-CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
-CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
CREATE TABLE fk_notpartitioned_fk (b int, a int);
ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
-- Constraint will be invalid.
@@ -2084,7 +2138,7 @@ CREATE TABLE fk_partitioned_fk_2 (b int, a int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
BEGIN;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
--- should be having two constraints
+-- should only have one constraint
\d fk_partitioned_fk_2
Table "public.fk_partitioned_fk_2"
Column | Type | Collation | Nullable | Default
@@ -2093,7 +2147,6 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN
a | integer | | |
Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
Foreign-key constraints:
- "fk_partitioned_fk_2_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
DROP TABLE fk_partitioned_fk_2;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 7e5be0702da..fa51c7110ef 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1248,18 +1248,49 @@ CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b i
REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
-ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
DROP COLUMN fdrop3, DROP COLUMN fdrop4;
CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 1);
+-- Merge the not-enforced parent constraint with the enforced and not-enforced child constraints
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+
+-- Merge the non-enforced parent constraint with both the enforced and
+-- non-enforced child constraints, where the referenced table is partitioned.
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+-- Merge the enforced parent constraint with the enforced and not-enforced child constraints.
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
+ALTER TABLE fk_partitioned_fk_3 DETACH PARTITION fk_partitioned_fk_3_0;
+ALTER TABLE fk_partitioned_fk_3 ATTACH PARTITION fk_partitioned_fk_3_0 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
+
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
+ALTER TABLE fk_partitioned_fk_3 ALTER CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 NOT ENFORCED;
+
+-- Merging an enforced parent constraint (validated) with a not-enforced child
+-- constraint will implicitly change the child constraint to enforced and apply
+-- the validation as well.
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
FOR VALUES FROM (2000,2000) TO (3000,3000);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
-- Creating a foreign key with ONLY on a partitioned table referencing
-- a non-partitioned table fails.
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
@@ -1386,8 +1417,6 @@ CREATE TABLE fk_partitioned_fk_3_1 (a int, b int);
DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
-CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
-CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
CREATE TABLE fk_notpartitioned_fk (b int, a int);
ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
@@ -1528,7 +1557,7 @@ CREATE TABLE fk_partitioned_fk_2 (b int, a int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
BEGIN;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
--- should be having two constraints
+-- should only have one constraint
\d fk_partitioned_fk_2
DROP TABLE fk_partitioned_fk_2;
ROLLBACK;
--
2.49.0
Attachments:
[text/plain] v18.1-0001-Remove-hastriggers-flag-check-before-fetching-.patch (11.0K, ../[email protected]/2-v18.1-0001-Remove-hastriggers-flag-check-before-fetching-.patch)
download | inline diff:
From d9183039dffefd8f8443608e0e0cb85ae6233bdc Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Mon, 7 Oct 2024 12:40:07 +0530
Subject: [PATCH v18.1 1/3] Remove hastriggers flag check before fetching FK
constraints.
With NOT ENFORCED, foreign key constraints will be created without
triggers. Therefore, the criteria for fetching foreign keys based on
the presence of triggers no longer apply.
----
NOTE: This patch is intended to reduce the diff noise from the main
patch and is not meant to be committed separately. It should be
squashed with the main patch that adds ENFORCED/NOT ENFORCED.
----
---
src/backend/utils/cache/relcache.c | 5 -
src/bin/pg_dump/pg_dump.c | 8 +-
src/bin/psql/describe.c | 216 ++++++++++++++---------------
3 files changed, 103 insertions(+), 126 deletions(-)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 9f54a9e72b7..e6721056536 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4666,11 +4666,6 @@ RelationGetFKeyList(Relation relation)
if (relation->rd_fkeyvalid)
return relation->rd_fkeylist;
- /* Fast path: non-partitioned tables without triggers can't have FKs */
- if (!relation->rd_rel->relhastriggers &&
- relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
- return NIL;
-
/*
* We build the list we intend to return (in the caller's context) while
* doing the scan. After successfully completing the scan, we copy that
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e41e645f649..dc9797aa251 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8016,13 +8016,7 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
{
TableInfo *tinfo = &tblinfo[i];
- /*
- * For partitioned tables, foreign keys have no triggers so they must
- * be included anyway in case some foreign keys are defined.
- */
- if ((!tinfo->hastriggers &&
- tinfo->relkind != RELKIND_PARTITIONED_TABLE) ||
- !(tinfo->dobj.dump & DUMP_COMPONENT_DEFINITION))
+ if (!(tinfo->dobj.dump & DUMP_COMPONENT_DEFINITION))
continue;
/* OK, we need info for this table */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index bf565afcc4e..e4a4c5071dd 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2550,136 +2550,124 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
- /*
- * Print foreign-key constraints (there are none if no triggers,
- * except if the table is partitioned, in which case the triggers
- * appear in the partitions)
- */
- if (tableinfo.hastriggers ||
- tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
+ /* Print foreign-key constraints */
+ if (pset.sversion >= 120000 &&
+ (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
{
- if (pset.sversion >= 120000 &&
- (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
- {
- /*
- * Put the constraints defined in this table first, followed
- * by the constraints defined in ancestor partitioned tables.
- */
- printfPQExpBuffer(&buf,
- "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
- " conname,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
- " conrelid::pg_catalog.regclass AS ontable\n"
- " FROM pg_catalog.pg_constraint,\n"
- " pg_catalog.pg_partition_ancestors('%s')\n"
- " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
- "ORDER BY sametable DESC, conname;",
- oid, oid);
- }
- else
- {
- printfPQExpBuffer(&buf,
- "SELECT true as sametable, conname,\n"
- " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
- " conrelid::pg_catalog.regclass AS ontable\n"
- "FROM pg_catalog.pg_constraint r\n"
- "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
- oid);
-
- if (pset.sversion >= 120000)
- appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
- appendPQExpBufferStr(&buf, "ORDER BY conname");
- }
+ /*
+ * Put the constraints defined in this table first, followed
+ * by the constraints defined in ancestor partitioned tables.
+ */
+ printfPQExpBuffer(&buf,
+ "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
+ " conname,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
+ " conrelid::pg_catalog.regclass AS ontable\n"
+ " FROM pg_catalog.pg_constraint,\n"
+ " pg_catalog.pg_partition_ancestors('%s')\n"
+ " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
+ "ORDER BY sametable DESC, conname;",
+ oid, oid);
+ }
+ else
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT true as sametable, conname,\n"
+ " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
+ " conrelid::pg_catalog.regclass AS ontable\n"
+ "FROM pg_catalog.pg_constraint r\n"
+ "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
+ oid);
- result = PSQLexec(buf.data);
- if (!result)
- goto error_return;
- else
- tuples = PQntuples(result);
+ if (pset.sversion >= 120000)
+ appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
+ appendPQExpBufferStr(&buf, "ORDER BY conname");
+ }
- if (tuples > 0)
- {
- int i_sametable = PQfnumber(result, "sametable"),
- i_conname = PQfnumber(result, "conname"),
- i_condef = PQfnumber(result, "condef"),
- i_ontable = PQfnumber(result, "ontable");
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
- printTableAddFooter(&cont, _("Foreign-key constraints:"));
- for (i = 0; i < tuples; i++)
- {
- /*
- * Print untranslated constraint name and definition. Use
- * a "TABLE tab" prefix when the constraint is defined in
- * a parent partitioned table.
- */
- if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
- printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
- PQgetvalue(result, i, i_ontable),
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
- else
- printfPQExpBuffer(&buf, " \"%s\" %s",
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
+ if (tuples > 0)
+ {
+ int i_sametable = PQfnumber(result, "sametable"),
+ i_conname = PQfnumber(result, "conname"),
+ i_condef = PQfnumber(result, "condef"),
+ i_ontable = PQfnumber(result, "ontable");
- printTableAddFooter(&cont, buf.data);
- }
+ printTableAddFooter(&cont, _("Foreign-key constraints:"));
+ for (i = 0; i < tuples; i++)
+ {
+ /*
+ * Print untranslated constraint name and definition. Use
+ * a "TABLE tab" prefix when the constraint is defined in
+ * a parent partitioned table.
+ */
+ if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
+ printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
+ PQgetvalue(result, i, i_ontable),
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
+ else
+ printfPQExpBuffer(&buf, " \"%s\" %s",
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
+
+ printTableAddFooter(&cont, buf.data);
}
- PQclear(result);
}
+ PQclear(result);
/* print incoming foreign-key references */
- if (tableinfo.hastriggers ||
- tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
+ if (pset.sversion >= 120000)
{
- if (pset.sversion >= 120000)
- {
- printfPQExpBuffer(&buf,
- "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
- " FROM pg_catalog.pg_constraint c\n"
- " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
- " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
- " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
- "ORDER BY conname;",
- oid, oid);
- }
- else
- {
- printfPQExpBuffer(&buf,
- "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
- " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
- " FROM pg_catalog.pg_constraint\n"
- " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
- "ORDER BY conname;",
- oid);
- }
+ printfPQExpBuffer(&buf,
+ "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
+ " FROM pg_catalog.pg_constraint c\n"
+ " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
+ " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
+ " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
+ "ORDER BY conname;",
+ oid, oid);
+ }
+ else
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
+ " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
+ " FROM pg_catalog.pg_constraint\n"
+ " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
+ "ORDER BY conname;",
+ oid);
+ }
- result = PSQLexec(buf.data);
- if (!result)
- goto error_return;
- else
- tuples = PQntuples(result);
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
- if (tuples > 0)
- {
- int i_conname = PQfnumber(result, "conname"),
- i_ontable = PQfnumber(result, "ontable"),
- i_condef = PQfnumber(result, "condef");
+ if (tuples > 0)
+ {
+ int i_conname = PQfnumber(result, "conname"),
+ i_ontable = PQfnumber(result, "ontable"),
+ i_condef = PQfnumber(result, "condef");
- printTableAddFooter(&cont, _("Referenced by:"));
- for (i = 0; i < tuples; i++)
- {
- printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
- PQgetvalue(result, i, i_ontable),
- PQgetvalue(result, i, i_conname),
- PQgetvalue(result, i, i_condef));
+ printTableAddFooter(&cont, _("Referenced by:"));
+ for (i = 0; i < tuples; i++)
+ {
+ printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
+ PQgetvalue(result, i, i_ontable),
+ PQgetvalue(result, i, i_conname),
+ PQgetvalue(result, i, i_condef));
- printTableAddFooter(&cont, buf.data);
- }
+ printTableAddFooter(&cont, buf.data);
}
- PQclear(result);
}
+ PQclear(result);
/* print any row-level policies */
if (pset.sversion >= 90500)
--
2.49.0
[text/plain] v18.1-0002-Add-support-for-NOT-ENFORCED-in-foreign-key-co.patch (67.0K, ../[email protected]/3-v18.1-0002-Add-support-for-NOT-ENFORCED-in-foreign-key-co.patch)
download | inline diff:
From 6b09fb343378698657c0cecc6a85b85276b1f27d Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Fri, 7 Feb 2025 09:41:20 +0530
Subject: [PATCH v18.1 2/3] Add support for NOT ENFORCED in foreign key
constraints.
Typically, when a foreign key (FK) constraint is created on a table,
action and check triggers are added to maintain data integrity. With
this patch, if a constraint is marked as NOT ENFORCED, integrity
checks are no longer required, making these triggers unnecessary.
Consequently, when creating a NOT ENFORCED FK constraint, triggers
will not be created, and the constraint will be marked as NOT VALID.
Similarly, if an existing FK constraint is changed to NOT ENFORCED,
the associated triggers will be dropped, and the constraint will also
be marked as NOT VALID. Conversely, if a NOT ENFORCED FK constraint is
changed to ENFORCED, the necessary triggers will be created, and the
will be changed to VALID by performing necessary validation.
----
NOTE: In this patch, the tryAttachPartitionForeignKey() function will
not merge the constraint if the enforcibility differs. This will be
addressed in the next patch.
----
---
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/ref/alter_table.sgml | 5 +-
doc/src/sgml/ref/create_table.sgml | 2 +-
src/backend/catalog/pg_constraint.c | 5 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 457 ++++++++++++++++++----
src/backend/parser/gram.y | 15 +-
src/backend/parser/parse_utilcmd.c | 14 +-
src/backend/utils/cache/relcache.c | 1 +
src/include/nodes/parsenodes.h | 2 +
src/include/utils/rel.h | 3 +
src/test/regress/expected/constraints.out | 8 +-
src/test/regress/expected/foreign_key.out | 161 +++++++-
src/test/regress/expected/inherit.out | 81 ++--
src/test/regress/sql/foreign_key.sql | 107 ++++-
src/test/regress/sql/inherit.sql | 7 +
16 files changed, 725 insertions(+), 147 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..014bb815665 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2620,7 +2620,7 @@ <title><structname>pg_constraint</structname> Columns</title>
</para>
<para>
Is the constraint enforced?
- Currently, can be false only for CHECK constraints
+ Currently, can be false only for foreign keys and CHECK constraints
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 11d1bc7dbe1..ece438f0075 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -58,7 +58,7 @@
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ INHERIT | NO INHERIT ]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
@@ -589,7 +589,8 @@ <title>Description</title>
This form validates a foreign key or check constraint that was
previously created as <literal>NOT VALID</literal>, by scanning the
table to ensure there are no rows for which the constraint is not
- satisfied. Nothing happens if the constraint is already marked valid.
+ satisfied. If the constraint is not enforced, an error is thrown.
+ Nothing happens if the constraint is already marked valid.
(See <xref linkend="sql-altertable-notes"/> below for an explanation
of the usefulness of this command.)
</para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e5c034d724e..4a41b2f5530 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1409,7 +1409,7 @@ <title>Parameters</title>
</para>
<para>
- This is currently only supported for <literal>CHECK</literal>
+ This is currently only supported for foreign key and <literal>CHECK</literal>
constraints.
</para>
</listitem>
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index ac80652baf2..0467e7442ff 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -100,8 +100,9 @@ CreateConstraintEntry(const char *constraintName,
ObjectAddresses *addrs_auto;
ObjectAddresses *addrs_normal;
- /* Only CHECK constraint can be not enforced */
- Assert(isEnforced || constraintType == CONSTRAINT_CHECK);
+ /* Only CHECK or FOREIGN KEY constraint can be not enforced */
+ Assert(isEnforced || constraintType == CONSTRAINT_CHECK ||
+ constraintType == CONSTRAINT_FOREIGN);
/* NOT ENFORCED constraint must be NOT VALID */
Assert(isEnforced || !isValidated);
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 2f250d2c57b..ebe85337c28 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -281,7 +281,7 @@ F461 Named character sets NO
F471 Scalar subquery values YES
F481 Expanded NULL predicate YES
F491 Constraint management YES
-F492 Optional table constraint enforcement NO check constraints only
+F492 Optional table constraint enforcement YES except not-null constraints
F501 Features and conformance views YES
F501 Features and conformance views 01 SQL_FEATURES view YES
F501 Features and conformance views 02 SQL_SIZING view YES
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index afb25007613..6b56d59e77d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -395,6 +395,14 @@ static ObjectAddress ATExecAlterConstraint(List **wqueue, Relation rel,
static bool ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon, Relation conrel,
Relation tgrel, Relation rel, HeapTuple contuple,
bool recurse, LOCKMODE lockmode);
+static bool ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static bool ATExecAlterConstrDeferrability(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -405,6 +413,14 @@ static bool ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cm
static void AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
bool deferrable, bool initdeferred,
List **otherrelids);
+static void AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger);
static void AlterConstrDeferrabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
Relation conrel, Relation tgrel, Relation rel,
HeapTuple contuple, bool recurse,
@@ -10561,7 +10577,7 @@ addFkConstraint(addFkConstraintSides fkside,
CONSTRAINT_FOREIGN,
fkconstraint->deferrable,
fkconstraint->initdeferred,
- true, /* Is Enforced */
+ fkconstraint->is_enforced,
fkconstraint->initially_valid,
parentConstr,
RelationGetRelid(rel),
@@ -10679,21 +10695,23 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
Oid parentDelTrigger, Oid parentUpdTrigger,
bool with_period)
{
- Oid deleteTriggerOid,
- updateTriggerOid;
+ Oid deleteTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
Assert(CheckRelationLockedByMe(pkrel, ShareRowExclusiveLock, true));
Assert(CheckRelationLockedByMe(rel, ShareRowExclusiveLock, true));
/*
- * Create the action triggers that enforce the constraint.
+ * Create action triggers to enforce the constraint, or skip them if the
+ * constraint is NOT ENFORCED.
*/
- createForeignKeyActionTriggers(RelationGetRelid(rel),
- RelationGetRelid(pkrel),
- fkconstraint,
- parentConstr, indexOid,
- parentDelTrigger, parentUpdTrigger,
- &deleteTriggerOid, &updateTriggerOid);
+ if (fkconstraint->is_enforced)
+ createForeignKeyActionTriggers(RelationGetRelid(rel),
+ RelationGetRelid(pkrel),
+ fkconstraint,
+ parentConstr, indexOid,
+ parentDelTrigger, parentUpdTrigger,
+ &deleteTriggerOid, &updateTriggerOid);
/*
* If the referenced table is partitioned, recurse on ourselves to handle
@@ -10814,8 +10832,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
Oid parentInsTrigger, Oid parentUpdTrigger,
bool with_period)
{
- Oid insertTriggerOid,
- updateTriggerOid;
+ Oid insertTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
Assert(OidIsValid(parentConstr));
Assert(CheckRelationLockedByMe(rel, ShareRowExclusiveLock, true));
@@ -10827,29 +10845,32 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
errmsg("foreign key constraints are not supported on foreign tables")));
/*
- * Add the check triggers to it and, if necessary, schedule it to be
- * checked in Phase 3.
+ * Add check triggers if the constraint is ENFORCED, and if needed,
+ * schedule them to be checked in Phase 3.
*
* If the relation is partitioned, drill down to do it to its partitions.
*/
- createForeignKeyCheckTriggers(RelationGetRelid(rel),
- RelationGetRelid(pkrel),
- fkconstraint,
- parentConstr,
- indexOid,
- parentInsTrigger, parentUpdTrigger,
- &insertTriggerOid, &updateTriggerOid);
+ if (fkconstraint->is_enforced)
+ createForeignKeyCheckTriggers(RelationGetRelid(rel),
+ RelationGetRelid(pkrel),
+ fkconstraint,
+ parentConstr,
+ indexOid,
+ parentInsTrigger, parentUpdTrigger,
+ &insertTriggerOid, &updateTriggerOid);
if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/*
* Tell Phase 3 to check that the constraint is satisfied by existing
- * rows. We can skip this during table creation, when requested
- * explicitly by specifying NOT VALID in an ADD FOREIGN KEY command,
- * and when we're recreating a constraint following a SET DATA TYPE
- * operation that did not impugn its validity.
+ * rows. We can skip this during table creation, when constraint is
+ * specified as NOT ENFORCED, or when requested explicitly by
+ * specifying NOT VALID in an ADD FOREIGN KEY command, and when we're
+ * recreating a constraint following a SET DATA TYPE operation that
+ * did not impugn its validity.
*/
- if (wqueue && !old_check_ok && !fkconstraint->skip_validation)
+ if (wqueue && !old_check_ok && !fkconstraint->skip_validation &&
+ fkconstraint->is_enforced)
{
NewConstraint *newcon;
AlteredTableInfo *tab;
@@ -11080,8 +11101,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
AttrNumber confdelsetcols[INDEX_MAX_KEYS];
Constraint *fkconstraint;
ObjectAddress address;
- Oid deleteTriggerOid,
- updateTriggerOid;
+ Oid deleteTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constrOid));
if (!HeapTupleIsValid(tuple))
@@ -11141,8 +11162,9 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
fkconstraint->fk_del_set_cols = NIL;
fkconstraint->old_conpfeqop = NIL;
fkconstraint->old_pktable_oid = InvalidOid;
+ fkconstraint->is_enforced = constrForm->conenforced;
fkconstraint->skip_validation = false;
- fkconstraint->initially_valid = true;
+ fkconstraint->initially_valid = constrForm->convalidated;
/* set up colnames that are used to generate the constraint name */
for (int i = 0; i < numfks; i++)
@@ -11170,9 +11192,10 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
* parent OIDs for similar triggers that will be created on the
* partition in addFkRecurseReferenced().
*/
- GetForeignKeyActionTriggers(trigrel, constrOid,
- constrForm->confrelid, constrForm->conrelid,
- &deleteTriggerOid, &updateTriggerOid);
+ if (constrForm->conenforced)
+ GetForeignKeyActionTriggers(trigrel, constrOid,
+ constrForm->confrelid, constrForm->conrelid,
+ &deleteTriggerOid, &updateTriggerOid);
/* Add this constraint ... */
address = addFkConstraint(addFkReferencedSide,
@@ -11305,8 +11328,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
Oid indexOid;
ObjectAddress address;
ListCell *lc;
- Oid insertTriggerOid,
- updateTriggerOid;
+ Oid insertTriggerOid = InvalidOid,
+ updateTriggerOid = InvalidOid;
bool with_period;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid));
@@ -11338,17 +11361,18 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
mapped_conkey[i] = attmap->attnums[conkey[i] - 1];
/*
- * Get the "check" triggers belonging to the constraint to pass as
- * parent OIDs for similar triggers that will be created on the
- * partition in addFkRecurseReferencing(). They are also passed to
- * tryAttachPartitionForeignKey() below to simply assign as parents to
- * the partition's existing "check" triggers, that is, if the
- * corresponding constraints is deemed attachable to the parent
- * constraint.
+ * Get the "check" triggers belonging to the constraint, if it is
+ * ENFORCED, to pass as parent OIDs for similar triggers that will be
+ * created on the partition in addFkRecurseReferencing(). They are
+ * also passed to tryAttachPartitionForeignKey() below to simply
+ * assign as parents to the partition's existing "check" triggers,
+ * that is, if the corresponding constraints is deemed attachable to
+ * the parent constraint.
*/
- GetForeignKeyCheckTriggers(trigrel, constrForm->oid,
- constrForm->confrelid, constrForm->conrelid,
- &insertTriggerOid, &updateTriggerOid);
+ if (constrForm->conenforced)
+ GetForeignKeyCheckTriggers(trigrel, constrForm->oid,
+ constrForm->confrelid, constrForm->conrelid,
+ &insertTriggerOid, &updateTriggerOid);
/*
* Before creating a new constraint, see whether any existing FKs are
@@ -11401,6 +11425,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
fkconstraint->fk_del_set_cols = NIL;
fkconstraint->old_conpfeqop = NIL;
fkconstraint->old_pktable_oid = InvalidOid;
+ fkconstraint->is_enforced = constrForm->conenforced;
fkconstraint->skip_validation = false;
fkconstraint->initially_valid = constrForm->convalidated;
for (int i = 0; i < numfks; i++)
@@ -11518,6 +11543,7 @@ tryAttachPartitionForeignKey(List **wqueue,
if (OidIsValid(partConstr->conparentid) ||
partConstr->condeferrable != parentConstr->condeferrable ||
partConstr->condeferred != parentConstr->condeferred ||
+ partConstr->conenforced != parentConstr->conenforced ||
partConstr->confupdtype != parentConstr->confupdtype ||
partConstr->confdeltype != parentConstr->confdeltype ||
partConstr->confmatchtype != parentConstr->confmatchtype)
@@ -11561,8 +11587,7 @@ AttachPartitionForeignKey(List **wqueue,
bool queueValidation;
Oid partConstrFrelid;
Oid partConstrRelid;
- Oid insertTriggerOid,
- updateTriggerOid;
+ bool parentConstrIsEnforced;
/* Fetch the parent constraint tuple */
parentConstrTup = SearchSysCache1(CONSTROID,
@@ -11570,6 +11595,7 @@ AttachPartitionForeignKey(List **wqueue,
if (!HeapTupleIsValid(parentConstrTup))
elog(ERROR, "cache lookup failed for constraint %u", parentConstrOid);
parentConstr = (Form_pg_constraint) GETSTRUCT(parentConstrTup);
+ parentConstrIsEnforced = parentConstr->conenforced;
/* Fetch the child constraint tuple */
partcontup = SearchSysCache1(CONSTROID,
@@ -11619,17 +11645,24 @@ AttachPartitionForeignKey(List **wqueue,
/*
* Like the constraint, attach partition's "check" triggers to the
- * corresponding parent triggers.
+ * corresponding parent triggers if the constraint is ENFORCED.
+ * NOT ENFORCED constraints do not have these triggers.
*/
- GetForeignKeyCheckTriggers(trigrel,
- partConstrOid, partConstrFrelid, partConstrRelid,
- &insertTriggerOid, &updateTriggerOid);
- Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));
- TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,
- RelationGetRelid(partition));
- Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));
- TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,
- RelationGetRelid(partition));
+ if (parentConstrIsEnforced)
+ {
+ Oid insertTriggerOid,
+ updateTriggerOid;
+
+ GetForeignKeyCheckTriggers(trigrel,
+ partConstrOid, partConstrFrelid, partConstrRelid,
+ &insertTriggerOid, &updateTriggerOid);
+ Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));
+ TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,
+ RelationGetRelid(partition));
+ Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));
+ TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,
+ RelationGetRelid(partition));
+ }
/*
* We updated this pg_constraint row above to set its parent; validating
@@ -11743,6 +11776,10 @@ RemoveInheritedConstraint(Relation conrel, Relation trigrel, Oid conoid,
*
* The subroutine for tryAttachPartitionForeignKey handles the deletion of
* action triggers for the foreign key constraint.
+ *
+ * If valid confrelid and conrelid values are not provided, the respective
+ * trigger check will be skipped, and the trigger will be considered for
+ * removal.
*/
static void
DropForeignKeyConstraintTriggers(Relation trigrel, Oid conoid, Oid confrelid,
@@ -11763,11 +11800,28 @@ DropForeignKeyConstraintTriggers(Relation trigrel, Oid conoid, Oid confrelid,
Form_pg_trigger trgform = (Form_pg_trigger) GETSTRUCT(trigtup);
ObjectAddress trigger;
- if (trgform->tgconstrrelid != conrelid)
+ /* Invalid if trigger is not for a referential integrity constraint */
+ if (!OidIsValid(trgform->tgconstrrelid))
continue;
- if (trgform->tgrelid != confrelid)
+ if (OidIsValid(conrelid) && trgform->tgconstrrelid != conrelid)
+ continue;
+ if (OidIsValid(confrelid) && trgform->tgrelid != confrelid)
continue;
+ /* We should be droping trigger related to foreign key constraint */
+ Assert(trgform->tgfoid == F_RI_FKEY_CHECK_INS ||
+ trgform->tgfoid == F_RI_FKEY_CHECK_UPD ||
+ trgform->tgfoid == F_RI_FKEY_CASCADE_DEL ||
+ trgform->tgfoid == F_RI_FKEY_CASCADE_UPD ||
+ trgform->tgfoid == F_RI_FKEY_RESTRICT_DEL ||
+ trgform->tgfoid == F_RI_FKEY_RESTRICT_UPD ||
+ trgform->tgfoid == F_RI_FKEY_SETNULL_DEL ||
+ trgform->tgfoid == F_RI_FKEY_SETNULL_UPD ||
+ trgform->tgfoid == F_RI_FKEY_SETDEFAULT_DEL ||
+ trgform->tgfoid == F_RI_FKEY_SETDEFAULT_UPD ||
+ trgform->tgfoid == F_RI_FKEY_NOACTION_DEL ||
+ trgform->tgfoid == F_RI_FKEY_NOACTION_UPD);
+
/*
* The constraint is originally set up to contain this trigger as an
* implementation object, so there's a dependency record that links
@@ -11979,6 +12033,11 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ if (cmdcon->alterEnforceability && currcon->contype != CONSTRAINT_FOREIGN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot alter enforceability of constraint \"%s\" of relation \"%s\"",
+ cmdcon->conname, RelationGetRelationName(rel))));
if (cmdcon->alterInheritability &&
currcon->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
@@ -12058,7 +12117,7 @@ ATExecAlterConstraint(List **wqueue, Relation rel, ATAlterConstraint *cmdcon,
/*
* A subroutine of ATExecAlterConstraint that calls the respective routines for
- * altering constraint attributes.
+ * altering constraint's enforceability, deferrability or inheritability.
*/
static bool
ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
@@ -12066,16 +12125,35 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
HeapTuple contuple, bool recurse,
LOCKMODE lockmode)
{
+ Form_pg_constraint currcon;
bool changed = false;
List *otherrelids = NIL;
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+
/*
- * Do the catalog work for the deferrability change, recurse if necessary.
- */
- if (cmdcon->alterDeferrability &&
- ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
- contuple, recurse, &otherrelids,
- lockmode))
+ * Do the catalog work for the enforceability or deferrability change,
+ * recurse if necessary.
+ *
+ * Note that even if deferrability is requested to be altered along with
+ * enforceability, we don't need to explicitly update multiple entries in
+ * pg_trigger related to deferrability.
+ *
+ * Modifying enforceability involves either creating or dropping the
+ * trigger, during which the deferrability setting will be adjusted
+ * automatically.
+ */
+ if (cmdcon->alterEnforceability &&
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel,
+ currcon->conrelid, currcon->confrelid,
+ contuple, lockmode, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid))
+ changed = true;
+
+ else if (cmdcon->alterDeferrability &&
+ ATExecAlterConstrDeferrability(wqueue, cmdcon, conrel, tgrel, rel,
+ contuple, recurse, &otherrelids,
+ lockmode))
{
/*
* AlterConstrUpdateConstraintEntry already invalidated relcache for
@@ -12100,6 +12178,151 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
return changed;
}
+/*
+ * Returns true if the constraint's enforceability is altered.
+ *
+ * Depending on whether the constraint is being set to ENFORCED or NOT
+ * ENFORCED, it creates or drops the trigger accordingly.
+ *
+ * Note that we must recurse even when trying to change a constraint to not
+ * enforced if it is already not enforced, in case descendant constraints
+ * might be enforced and need to be changed to not enforced. Conversely, we
+ * should do nothing if a constraint is being set to enforced and is already
+ * enforced, as descendant constraints cannot be different in that case.
+ */
+static bool
+ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
+{
+ Form_pg_constraint currcon;
+ Oid conoid;
+ Relation rel;
+ bool changed = false;
+
+ /* Since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ Assert(cmdcon->alterEnforceability);
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ conoid = currcon->oid;
+
+ /* Should be foreign key constraint */
+ Assert(currcon->contype == CONSTRAINT_FOREIGN);
+
+ rel = table_open(currcon->conrelid, lockmode);
+
+ if (currcon->conenforced != cmdcon->is_enforced)
+ {
+ AlterConstrUpdateConstraintEntry(cmdcon, conrel, contuple);
+ changed = true;
+ }
+
+ /* Drop triggers */
+ if (!cmdcon->is_enforced)
+ {
+ /*
+ * When setting a constraint to NOT ENFORCED, the constraint triggers
+ * need to be dropped. Therefore, we must process the child relations
+ * first, followed by the parent, to account for dependencies.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid);
+
+ /* Drop all the triggers */
+ DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
+ }
+ else if (changed) /* Create triggers */
+ {
+ Oid ReferencedDelTriggerOid = InvalidOid,
+ ReferencedUpdTriggerOid = InvalidOid,
+ ReferencingInsTriggerOid = InvalidOid,
+ ReferencingUpdTriggerOid = InvalidOid;
+
+ /* Prepare the minimal information required for trigger creation. */
+ Constraint *fkconstraint = makeNode(Constraint);
+
+ fkconstraint->conname = pstrdup(NameStr(currcon->conname));
+ fkconstraint->fk_matchtype = currcon->confmatchtype;
+ fkconstraint->fk_upd_action = currcon->confupdtype;
+ fkconstraint->fk_del_action = currcon->confdeltype;
+
+ /* Create referenced triggers */
+ if (currcon->conrelid == fkrelid)
+ createForeignKeyActionTriggers(currcon->conrelid,
+ currcon->confrelid,
+ fkconstraint,
+ conoid,
+ currcon->conindid,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ &ReferencedDelTriggerOid,
+ &ReferencedUpdTriggerOid);
+
+ /* Create referencing triggers */
+ if (currcon->confrelid == pkrelid)
+ createForeignKeyCheckTriggers(currcon->conrelid,
+ pkrelid,
+ fkconstraint,
+ conoid,
+ currcon->conindid,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger,
+ &ReferencingInsTriggerOid,
+ &ReferencingUpdTriggerOid);
+
+ /*
+ * Tell Phase 3 to check that the constraint is satisfied by existing
+ * rows.
+ */
+ if (rel->rd_rel->relkind == RELKIND_RELATION)
+ {
+ AlteredTableInfo *tab;
+ NewConstraint *newcon;
+
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = fkconstraint->conname;
+ newcon->contype = CONSTR_FOREIGN;
+ newcon->refrelid = currcon->confrelid;
+ newcon->refindid = currcon->conindid;
+ newcon->conid = currcon->oid;
+ newcon->qual = (Node *) fkconstraint;
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
+ /*
+ * If the table at either end of the constraint is partitioned, we
+ * need to recurse and create triggers for each constraint that is a
+ * child of this one.
+ */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ AlterConstrEnforceabilityRecurse(wqueue, cmdcon, conrel, tgrel,
+ fkrelid, pkrelid, contuple,
+ lockmode, ReferencedDelTriggerOid,
+ ReferencedUpdTriggerOid,
+ ReferencingInsTriggerOid,
+ ReferencingUpdTriggerOid);
+ }
+
+ table_close(rel, NoLock);
+
+ return changed;
+}
+
/*
* Returns true if the constraint's deferrability is altered.
*
@@ -12304,6 +12527,55 @@ AlterConstrTriggerDeferrability(Oid conoid, Relation tgrel, Relation rel,
systable_endscan(tgscan);
}
+/*
+ * Invokes ATExecAlterConstrEnforceability for each constraint that is a child of
+ * the specified constraint.
+ *
+ * Note that this doesn't handle recursion the normal way, viz. by scanning the
+ * list of child relations and recursing; instead it uses the conparentid
+ * relationships. This may need to be reconsidered.
+ *
+ * The arguments to this function have the same meaning as the arguments to
+ * ATExecAlterConstrEnforceability.
+ */
+static void
+AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
+ Relation conrel, Relation tgrel,
+ const Oid fkrelid, const Oid pkrelid,
+ HeapTuple contuple, LOCKMODE lockmode,
+ Oid ReferencedParentDelTrigger,
+ Oid ReferencedParentUpdTrigger,
+ Oid ReferencingParentInsTrigger,
+ Oid ReferencingParentUpdTrigger)
+{
+ Form_pg_constraint currcon;
+ Oid conoid;
+ ScanKeyData pkey;
+ SysScanDesc pscan;
+ HeapTuple childtup;
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ conoid = currcon->oid;
+
+ ScanKeyInit(&pkey,
+ Anum_pg_constraint_conparentid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conoid));
+
+ pscan = systable_beginscan(conrel, ConstraintParentIndexId,
+ true, NULL, 1, &pkey);
+
+ while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+ pkrelid, childtup, lockmode,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger);
+
+ systable_endscan(pscan);
+}
+
/*
* Invokes ATExecAlterConstrDeferrability for each constraint that is a child of
* the specified constraint.
@@ -12364,11 +12636,25 @@ AlterConstrUpdateConstraintEntry(ATAlterConstraint *cmdcon, Relation conrel,
HeapTuple copyTuple;
Form_pg_constraint copy_con;
- Assert(cmdcon->alterDeferrability || cmdcon->alterInheritability);
+ Assert(cmdcon->alterEnforceability || cmdcon->alterDeferrability ||
+ cmdcon->alterInheritability);
copyTuple = heap_copytuple(contuple);
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ if (cmdcon->alterEnforceability)
+ {
+ copy_con->conenforced = cmdcon->is_enforced;
+
+ /*
+ * NB: The convalidated status is irrelevant when the constraint is
+ * set to NOT ENFORCED, but for consistency, it should still be set
+ * appropriately. Similarly, if the constraint is later changed to
+ * ENFORCED, validation will be performed during phase 3, so it makes
+ * sense to mark it as valid in that case.
+ */
+ copy_con->convalidated = cmdcon->is_enforced;
+ }
if (cmdcon->alterDeferrability)
{
copy_con->condeferrable = cmdcon->deferrable;
@@ -17088,9 +17374,9 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
NameStr(child_con->conname), RelationGetRelationName(child_rel))));
/*
- * A non-enforced child constraint cannot be merged with an
- * enforced parent constraint. However, the reverse is allowed,
- * where the child constraint is enforced.
+ * A NOT ENFORCED child constraint cannot be merged with an
+ * ENFORCED parent constraint. However, the reverse is allowed,
+ * where the child constraint is ENFORCED.
*/
if (parent_con->conenforced && !child_con->conenforced)
ereport(ERROR,
@@ -20461,8 +20747,6 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
Form_pg_constraint conform;
- Oid insertTriggerOid,
- updateTriggerOid;
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
if (!HeapTupleIsValid(contup))
@@ -20489,17 +20773,25 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
/*
* Also, look up the partition's "check" triggers corresponding to the
- * constraint being detached and detach them from the parent triggers.
+ * ENFORCED constraint being detached and detach them from the parent
+ * triggers. NOT ENFORCED constraints do not have these triggers;
+ * therefore, this step is not needed.
*/
- GetForeignKeyCheckTriggers(trigrel,
- fk->conoid, fk->confrelid, fk->conrelid,
- &insertTriggerOid, &updateTriggerOid);
- Assert(OidIsValid(insertTriggerOid));
- TriggerSetParentTrigger(trigrel, insertTriggerOid, InvalidOid,
- RelationGetRelid(partRel));
- Assert(OidIsValid(updateTriggerOid));
- TriggerSetParentTrigger(trigrel, updateTriggerOid, InvalidOid,
- RelationGetRelid(partRel));
+ if (fk->conenforced)
+ {
+ Oid insertTriggerOid,
+ updateTriggerOid;
+
+ GetForeignKeyCheckTriggers(trigrel,
+ fk->conoid, fk->confrelid, fk->conrelid,
+ &insertTriggerOid, &updateTriggerOid);
+ Assert(OidIsValid(insertTriggerOid));
+ TriggerSetParentTrigger(trigrel, insertTriggerOid, InvalidOid,
+ RelationGetRelid(partRel));
+ Assert(OidIsValid(updateTriggerOid));
+ TriggerSetParentTrigger(trigrel, updateTriggerOid, InvalidOid,
+ RelationGetRelid(partRel));
+ }
/*
* Lastly, create the action triggers on the referenced table, using
@@ -20539,8 +20831,9 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
fkconstraint->conname = pstrdup(NameStr(conform->conname));
fkconstraint->deferrable = conform->condeferrable;
fkconstraint->initdeferred = conform->condeferred;
+ fkconstraint->is_enforced = conform->conenforced;
fkconstraint->skip_validation = true;
- fkconstraint->initially_valid = true;
+ fkconstraint->initially_valid = conform->convalidated;
/* a few irrelevant fields omitted here */
fkconstraint->pktable = NULL;
fkconstraint->fk_attrs = NIL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0fc502a3a40..c982f856559 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2665,12 +2665,17 @@ alter_table_cmd:
if ($4 & (CAS_DEFERRABLE | CAS_NOT_DEFERRABLE |
CAS_INITIALLY_DEFERRED | CAS_INITIALLY_IMMEDIATE))
c->alterDeferrability = true;
+ if ($4 & (CAS_NOT_ENFORCED | CAS_ENFORCED))
+ c->alterEnforceability = true;
if ($4 & CAS_NO_INHERIT)
c->alterInheritability = true;
processCASbits($4, @4, "FOREIGN KEY",
- &c->deferrable,
- &c->initdeferred,
- NULL, NULL, &c->noinherit, yyscanner);
+ &c->deferrable,
+ &c->initdeferred,
+ &c->is_enforced,
+ NULL,
+ &c->noinherit,
+ yyscanner);
$$ = (Node *) n;
}
/* ALTER TABLE <name> ALTER CONSTRAINT INHERIT */
@@ -4334,8 +4339,8 @@ ConstraintElem:
n->fk_del_set_cols = ($11)->deleteAction->cols;
processCASbits($12, @12, "FOREIGN KEY",
&n->deferrable, &n->initdeferred,
- NULL, &n->skip_validation, NULL,
- yyscanner);
+ &n->is_enforced, &n->skip_validation,
+ NULL, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 896a7f2c59b..50f430a4e1a 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2976,8 +2976,10 @@ transformFKConstraints(CreateStmtContext *cxt,
/*
* If CREATE TABLE or adding a column with NULL default, we can safely
- * skip validation of FK constraints, and nonetheless mark them valid.
- * (This will override any user-supplied NOT VALID flag.)
+ * skip validation of FK constraints, and mark them as valid based on the
+ * constraint enforcement flag, since NOT ENFORCED constraints must always
+ * be marked as NOT VALID. (This will override any user-supplied NOT VALID
+ * flag.)
*/
if (skipValidation)
{
@@ -2986,7 +2988,7 @@ transformFKConstraints(CreateStmtContext *cxt,
Constraint *constraint = (Constraint *) lfirst(fkclist);
constraint->skip_validation = true;
- constraint->initially_valid = true;
+ constraint->initially_valid = constraint->is_enforced;
}
}
@@ -3981,7 +3983,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_ENFORCED:
if (lastprimarycon == NULL ||
- lastprimarycon->contype != CONSTR_CHECK)
+ (lastprimarycon->contype != CONSTR_CHECK &&
+ lastprimarycon->contype != CONSTR_FOREIGN))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced ENFORCED clause"),
@@ -3997,7 +4000,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
case CONSTR_ATTR_NOT_ENFORCED:
if (lastprimarycon == NULL ||
- lastprimarycon->contype != CONSTR_CHECK)
+ (lastprimarycon->contype != CONSTR_CHECK &&
+ lastprimarycon->contype != CONSTR_FOREIGN))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced NOT ENFORCED clause"),
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index e6721056536..18a14ae186e 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4697,6 +4697,7 @@ RelationGetFKeyList(Relation relation)
info->conoid = constraint->oid;
info->conrelid = constraint->conrelid;
info->confrelid = constraint->confrelid;
+ info->conenforced = constraint->conenforced;
DeconstructFkConstraintRow(htup, &info->nkeys,
info->conkey,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index df331b1c0d9..00fefa9483a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2495,6 +2495,8 @@ typedef struct ATAlterConstraint
{
NodeTag type;
char *conname; /* Constraint name */
+ bool alterEnforceability; /* changing enforceability properties? */
+ bool is_enforced; /* ENFORCED? */
bool alterDeferrability; /* changing deferrability properties? */
bool deferrable; /* DEFERRABLE? */
bool initdeferred; /* INITIALLY DEFERRED? */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index d94fddd7cef..b552359915f 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -284,6 +284,9 @@ typedef struct ForeignKeyCacheInfo
/* number of columns in the foreign key */
int nkeys;
+ /* Is enforced ? */
+ bool conenforced;
+
/*
* these arrays each have nkeys valid entries:
*/
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..a719d2f74e9 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -745,13 +745,9 @@ ERROR: misplaced NOT ENFORCED clause
LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED);
^
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked ENFORCED
-LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED;
- ^
+ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED;
-ERROR: FOREIGN KEY constraints cannot be marked NOT ENFORCED
-LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC...
- ^
+ERROR: cannot alter enforceability of constraint "unique_tbl_i_key" of relation "unique_tbl"
DROP TABLE unique_tbl;
--
-- EXCLUDE constraints
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 7f678349a8e..1d0a4548732 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1,12 +1,43 @@
--
-- FOREIGN KEY
--
--- MATCH FULL
+-- NOT ENFORCED
--
-- First test, check and cascade
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
-CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED,
+ ftest2 int );
+-- Inserting into the foreign key table will not result in an error, even if
+-- there is no matching key in the referenced table.
+INSERT INTO FKTABLE VALUES (1, 2);
+INSERT INTO FKTABLE VALUES (2, 3);
+-- Check FKTABLE
+SELECT * FROM FKTABLE;
+ ftest1 | ftest2
+--------+--------
+ 1 | 2
+ 2 | 3
+(2 rows)
+
+-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered,
+-- as it was previously in a valid state.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
+DETAIL: Key (ftest1)=(1) is not present in table "pktable".
+-- Remove any existing rows that violate the constraint, then attempt to change
+-- it.
+TRUNCATE FKTABLE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+-- Any further inserts will fail due to the enforcement.
+INSERT INTO FKTABLE VALUES (3, 4);
+ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
+DETAIL: Key (ftest1)=(3) is not present in table "pktable".
+--
+-- MATCH FULL
+--
+-- First test, check and cascade
+--
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
INSERT INTO PKTABLE VALUES (2, 'Test2');
@@ -351,6 +382,43 @@ INSERT INTO FKTABLE VALUES (1, NULL);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
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.
+-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa
+ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | f | f
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | f | f
+(1 row)
+
+-- Enforceability also changes the validate state, as data validation will be
+-- performed during this transformation.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ t | t | t | t
+(1 row)
+
+-- Can change enforceability and deferrability together
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+ condeferrable | condeferred | conenforced | convalidated
+---------------+-------------+-------------+--------------
+ f | f | f | f
+(1 row)
+
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- MATCH SIMPLE
@@ -1276,6 +1344,13 @@ INSERT INTO fktable VALUES (0, 20);
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
DETAIL: Key (fk)=(20) is not present in table "pktable".
COMMIT;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+BEGIN;
+-- doesn't match FK, no error.
+UPDATE pktable SET id = 10 WHERE id = 5;
+-- doesn't match PK, no error.
+INSERT INTO fktable VALUES (0, 20);
+ROLLBACK;
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- illegal options
@@ -1289,6 +1364,14 @@ ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
ERROR: FOREIGN KEY constraints cannot be marked NOT VALID
LINE 1: ...ER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
^
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED;
+ERROR: conflicting constraint properties
+LINE 1: ...fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORC...
+ ^
+CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED);
+ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed
+LINE 1: ...ABLE fktable2 (fk int references pktable ENFORCED NOT ENFORC...
+ ^
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row. This was broken by subtransaction-related
-- changes in 8.0.
@@ -1586,10 +1669,13 @@ ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1;
CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int);
ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b)
+ REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
@@ -1612,10 +1698,10 @@ INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
@@ -1665,6 +1751,37 @@ Indexes:
Referenced by:
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+-- Check the exsting FK trigger
+CREATE TEMP TABLE tmp_trg_info AS SELECT tgtype, tgrelid::regclass, tgconstraint
+FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+SELECT count(1) FROM tmp_trg_info;
+ count
+-------
+ 14
+(1 row)
+
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+-- No triggers
+SELECT count(1) FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+ count
+-------
+ 0
+(1 row)
+
+-- Changing it back to ENFORCED will recreate the necessary triggers.
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+-- Should be exactly the same number of triggers found as before
+SELECT COUNT(1) FROM pg_trigger pgt JOIN tmp_trg_info tt
+ON (pgt.tgtype = tt.tgtype AND pgt.tgrelid = tt.tgrelid AND pgt.tgconstraint = tt.tgconstraint);
+ count
+-------
+ 14
+(1 row)
+
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
@@ -1962,6 +2079,40 @@ Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
Foreign-key constraints:
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
+BEGIN;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should be having two constraints
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ "fk_partitioned_fk_2_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+DROP TABLE fk_partitioned_fk_2;
+ROLLBACK;
+BEGIN;
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
+
+ROLLBACK;
DROP TABLE fk_partitioned_fk_2;
CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 4d07d0bd79b..caab1164fdb 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1332,6 +1332,13 @@ NOTICE: merging constraint "inh_check_constraint5" with inherited definition
alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced;
alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced;
NOTICE: merging constraint "inh_check_constraint6" with inherited definition
+alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced;
+alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced;
+NOTICE: merging constraint "inh_check_constraint9" with inherited definition
+-- the invalid state of the child constraint will be ignored here.
+alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced;
+alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced;
+NOTICE: merging constraint "inh_check_constraint10" with inherited definition
create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1);
NOTICE: merging column "f1" with inherited definition
NOTICE: merging constraint "inh_check_constraint4" with inherited definition
@@ -1356,39 +1363,47 @@ ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constrain
select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced, convalidated
from pg_constraint where conname like 'inh\_check\_constraint%'
order by 1, 2;
- relname | conname | conislocal | coninhcount | conenforced | convalidated
----------+-----------------------+------------+-------------+-------------+--------------
- p1 | inh_check_constraint1 | t | 0 | t | t
- p1 | inh_check_constraint2 | t | 0 | t | t
- p1 | inh_check_constraint3 | t | 0 | f | f
- p1 | inh_check_constraint4 | t | 0 | f | f
- p1 | inh_check_constraint5 | t | 0 | f | f
- p1 | inh_check_constraint6 | t | 0 | f | f
- p1 | inh_check_constraint8 | t | 0 | t | t
- p1_c1 | inh_check_constraint1 | t | 1 | t | t
- p1_c1 | inh_check_constraint2 | t | 1 | t | t
- p1_c1 | inh_check_constraint3 | t | 1 | f | f
- p1_c1 | inh_check_constraint4 | t | 1 | f | f
- p1_c1 | inh_check_constraint5 | t | 1 | t | t
- p1_c1 | inh_check_constraint6 | t | 1 | t | t
- p1_c1 | inh_check_constraint7 | t | 0 | f | f
- p1_c1 | inh_check_constraint8 | f | 1 | t | t
- p1_c2 | inh_check_constraint1 | f | 1 | t | t
- p1_c2 | inh_check_constraint2 | f | 1 | t | t
- p1_c2 | inh_check_constraint3 | f | 1 | f | f
- p1_c2 | inh_check_constraint4 | t | 1 | t | t
- p1_c2 | inh_check_constraint5 | f | 1 | f | f
- p1_c2 | inh_check_constraint6 | f | 1 | f | f
- p1_c2 | inh_check_constraint8 | f | 1 | t | t
- p1_c3 | inh_check_constraint1 | f | 2 | t | t
- p1_c3 | inh_check_constraint2 | f | 2 | t | t
- p1_c3 | inh_check_constraint3 | f | 2 | f | f
- p1_c3 | inh_check_constraint4 | f | 2 | f | f
- p1_c3 | inh_check_constraint5 | f | 2 | t | t
- p1_c3 | inh_check_constraint6 | f | 2 | t | t
- p1_c3 | inh_check_constraint7 | f | 1 | f | f
- p1_c3 | inh_check_constraint8 | f | 2 | t | t
-(30 rows)
+ relname | conname | conislocal | coninhcount | conenforced | convalidated
+---------+------------------------+------------+-------------+-------------+--------------
+ p1 | inh_check_constraint1 | t | 0 | t | t
+ p1 | inh_check_constraint10 | t | 0 | f | f
+ p1 | inh_check_constraint2 | t | 0 | t | t
+ p1 | inh_check_constraint3 | t | 0 | f | f
+ p1 | inh_check_constraint4 | t | 0 | f | f
+ p1 | inh_check_constraint5 | t | 0 | f | f
+ p1 | inh_check_constraint6 | t | 0 | f | f
+ p1 | inh_check_constraint8 | t | 0 | t | t
+ p1 | inh_check_constraint9 | t | 0 | f | f
+ p1_c1 | inh_check_constraint1 | t | 1 | t | t
+ p1_c1 | inh_check_constraint10 | t | 1 | t | t
+ p1_c1 | inh_check_constraint2 | t | 1 | t | t
+ p1_c1 | inh_check_constraint3 | t | 1 | f | f
+ p1_c1 | inh_check_constraint4 | t | 1 | f | f
+ p1_c1 | inh_check_constraint5 | t | 1 | t | t
+ p1_c1 | inh_check_constraint6 | t | 1 | t | t
+ p1_c1 | inh_check_constraint7 | t | 0 | f | f
+ p1_c1 | inh_check_constraint8 | f | 1 | t | t
+ p1_c1 | inh_check_constraint9 | t | 1 | t | f
+ p1_c2 | inh_check_constraint1 | f | 1 | t | t
+ p1_c2 | inh_check_constraint10 | f | 1 | f | f
+ p1_c2 | inh_check_constraint2 | f | 1 | t | t
+ p1_c2 | inh_check_constraint3 | f | 1 | f | f
+ p1_c2 | inh_check_constraint4 | t | 1 | t | t
+ p1_c2 | inh_check_constraint5 | f | 1 | f | f
+ p1_c2 | inh_check_constraint6 | f | 1 | f | f
+ p1_c2 | inh_check_constraint8 | f | 1 | t | t
+ p1_c2 | inh_check_constraint9 | f | 1 | f | f
+ p1_c3 | inh_check_constraint1 | f | 2 | t | t
+ p1_c3 | inh_check_constraint10 | f | 2 | t | t
+ p1_c3 | inh_check_constraint2 | f | 2 | t | t
+ p1_c3 | inh_check_constraint3 | f | 2 | f | f
+ p1_c3 | inh_check_constraint4 | f | 2 | f | f
+ p1_c3 | inh_check_constraint5 | f | 2 | t | t
+ p1_c3 | inh_check_constraint6 | f | 2 | t | t
+ p1_c3 | inh_check_constraint7 | f | 1 | f | f
+ p1_c3 | inh_check_constraint8 | f | 2 | t | t
+ p1_c3 | inh_check_constraint9 | f | 2 | t | t
+(38 rows)
drop table p1 cascade;
NOTICE: drop cascades to 3 other objects
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 44945b0453a..7e5be0702da 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2,13 +2,39 @@
-- FOREIGN KEY
--
--- MATCH FULL
+-- NOT ENFORCED
--
-- First test, check and cascade
--
CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text );
-CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE, ftest2 int );
+CREATE TABLE FKTABLE ( ftest1 int CONSTRAINT fktable_ftest1_fkey REFERENCES PKTABLE MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE NOT ENFORCED,
+ ftest2 int );
+
+-- Inserting into the foreign key table will not result in an error, even if
+-- there is no matching key in the referenced table.
+INSERT INTO FKTABLE VALUES (1, 2);
+INSERT INTO FKTABLE VALUES (2, 3);
+
+-- Check FKTABLE
+SELECT * FROM FKTABLE;
+
+-- Reverting it back to ENFORCED will result in failure because constraint validation will be triggered,
+-- as it was previously in a valid state.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+
+-- Remove any existing rows that violate the constraint, then attempt to change
+-- it.
+TRUNCATE FKTABLE;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fktable_ftest1_fkey ENFORCED;
+-- Any further inserts will fail due to the enforcement.
+INSERT INTO FKTABLE VALUES (3, 4);
+
+--
+-- MATCH FULL
+--
+-- First test, check and cascade
+--
-- Insert test data into PKTABLE
INSERT INTO PKTABLE VALUES (1, 'Test1');
INSERT INTO PKTABLE VALUES (2, 'Test2');
@@ -230,6 +256,27 @@ CREATE TABLE FKTABLE ( ftest1 int, ftest2 int );
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL;
+-- Modifying other attributes of a constraint should not affect its enforceability, and vice versa
+ALTER TABLE FKTABLE ADD CONSTRAINT fk_con FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE NOT VALID NOT ENFORCED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con DEFERRABLE INITIALLY DEFERRED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+-- Enforceability also changes the validate state, as data validation will be
+-- performed during this transformation.
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con ENFORCED;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
+-- Can change enforceability and deferrability together
+ALTER TABLE FKTABLE ALTER CONSTRAINT fk_con NOT ENFORCED NOT DEFERRABLE;
+SELECT condeferrable, condeferred, conenforced, convalidated
+FROM pg_constraint WHERE conname = 'fk_con';
+
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
@@ -968,12 +1015,25 @@ CREATE TEMP TABLE fktable (
COMMIT;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT ENFORCED;
+
+BEGIN;
+
+-- doesn't match FK, no error.
+UPDATE pktable SET id = 10 WHERE id = 5;
+-- doesn't match PK, no error.
+INSERT INTO fktable VALUES (0, 20);
+
+ROLLBACK;
+
-- try additional syntax
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
-- illegal options
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NO INHERIT;
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT VALID;
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey ENFORCED NOT ENFORCED;
+CREATE TEMP TABLE fktable2 (fk int references pktable ENFORCED NOT ENFORCED);
-- test order of firing of FK triggers when several RI-induced changes need to
-- be made to the same row. This was broken by subtransaction-related
@@ -1184,11 +1244,13 @@ CREATE TABLE fk_partitioned_fk (b int, fdrop1 int, a int) PARTITION BY RANGE (a,
CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b int);
ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP COLUMN fdrop3;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN KEY (a, b)
+ REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
-
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
@@ -1234,6 +1296,27 @@ CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES W
UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500;
-- check psql behavior
\d fk_notpartitioned_pk
+
+-- Check the exsting FK trigger
+CREATE TEMP TABLE tmp_trg_info AS SELECT tgtype, tgrelid::regclass, tgconstraint
+FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+SELECT count(1) FROM tmp_trg_info;
+
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+-- No triggers
+SELECT count(1) FROM pg_trigger
+WHERE tgrelid IN (SELECT relid FROM pg_partition_tree('fk_partitioned_fk'::regclass)
+ UNION ALL SELECT 'fk_notpartitioned_pk'::regclass);
+
+-- Changing it back to ENFORCED will recreate the necessary triggers.
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+
+-- Should be exactly the same number of triggers found as before
+SELECT COUNT(1) FROM pg_trigger pgt JOIN tmp_trg_info tt
+ON (pgt.tgtype = tt.tgtype AND pgt.tgrelid = tt.tgrelid AND pgt.tgconstraint = tt.tgconstraint);
+
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_b_fkey;
-- done.
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
@@ -1441,6 +1524,22 @@ CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
\d fk_partitioned_fk_2
DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
+BEGIN;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should be having two constraints
+\d fk_partitioned_fk_2
+DROP TABLE fk_partitioned_fk_2;
+ROLLBACK;
+BEGIN;
+ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ROLLBACK;
+DROP TABLE fk_partitioned_fk_2;
+
CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 941189761fd..5f0b2617464 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -481,6 +481,13 @@ CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints)
alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced;
alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced;
+alter table p1_c1 add constraint inh_check_constraint9 check (f1 < 10) not valid enforced;
+alter table p1 add constraint inh_check_constraint9 check (f1 < 10) not enforced;
+
+-- the invalid state of the child constraint will be ignored here.
+alter table p1 add constraint inh_check_constraint10 check (f1 < 10) not enforced;
+alter table p1_c1 add constraint inh_check_constraint10 check (f1 < 10) not valid enforced;
+
create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1);
-- but reverse is not allowed
--
2.49.0
[text/plain] v18.1-0003-Merge-the-parent-and-child-constraints-with-di.patch (27.3K, ../[email protected]/4-v18.1-0003-Merge-the-parent-and-child-constraints-with-di.patch)
download | inline diff:
From c80eabbaf55edb4c44729499e9c9cd16596f1ddf Mon Sep 17 00:00:00 2001
From: Amul Sul <[email protected]>
Date: Mon, 10 Feb 2025 10:59:28 +0530
Subject: [PATCH v18.1 3/3] Merge the parent and child constraints with
differing enforcibility.
If an ENFORCED parent constraint is attached to a NOT ENFORCED child
constraint, the child constraint will be made ENFORCED, with
validation applied if the parent constraint is validated as well.
Otherwise, a new ENFORCED constraint (with validation, if the parent
constraint is validated) would need to be created on the child table,
which would be unnecessary if a similar constraint already exists and
can be attached.
On the other hand, having a NOT ENFORCED parent constraint with an
ENFORCED child constraint does not cause any issues, and no changes
are required.
----
NOTE: This patch is intended to reduce the diff noise from the main
patch and is not meant to be committed separately. It should be
squashed with the main patch that adds ENFORCED/NOT ENFORCED.
----
---
src/backend/commands/tablecmds.c | 164 ++++++++++++++++++++--
src/test/regress/expected/foreign_key.out | 77 ++++++++--
src/test/regress/sql/foreign_key.sql | 37 ++++-
3 files changed, 253 insertions(+), 25 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b56d59e77d..e2707132771 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11543,7 +11543,6 @@ tryAttachPartitionForeignKey(List **wqueue,
if (OidIsValid(partConstr->conparentid) ||
partConstr->condeferrable != parentConstr->condeferrable ||
partConstr->condeferred != parentConstr->condeferred ||
- partConstr->conenforced != parentConstr->conenforced ||
partConstr->confupdtype != parentConstr->confupdtype ||
partConstr->confdeltype != parentConstr->confdeltype ||
partConstr->confmatchtype != parentConstr->confmatchtype)
@@ -11588,6 +11587,8 @@ AttachPartitionForeignKey(List **wqueue,
Oid partConstrFrelid;
Oid partConstrRelid;
bool parentConstrIsEnforced;
+ bool partConstrIsEnforced;
+ bool partConstrParentIsSet;
/* Fetch the parent constraint tuple */
parentConstrTup = SearchSysCache1(CONSTROID,
@@ -11603,13 +11604,47 @@ AttachPartitionForeignKey(List **wqueue,
if (!HeapTupleIsValid(partcontup))
elog(ERROR, "cache lookup failed for constraint %u", partConstrOid);
partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
+ partConstrIsEnforced = partConstr->conenforced;
partConstrFrelid = partConstr->confrelid;
partConstrRelid = partConstr->conrelid;
+ /*
+ * The case where the parent constraint is NOT ENFORCED and the child
+ * constraint is ENFORCED is acceptable because the not enforced parent
+ * constraint lacks triggers, eliminating any redundancy issues with the
+ * enforced child constraint. In this scenario, the child constraint
+ * remains enforced, and its trigger is retained, ensuring that
+ * referential integrity checks for the child continue as before, even
+ * with the parent constraint not enforced. The relationship between the
+ * two constraints is preserved by setting the parent constraint, which
+ * allows us to locate the child constraint. This becomes important if the
+ * parent constraint is later changed to enforced, at which point the
+ * necessary trigger will be created for the parent, and any redundancy
+ * from these triggers will be appropriately handled.
+ */
+ if (!parentConstrIsEnforced && partConstrIsEnforced)
+ {
+ ReleaseSysCache(partcontup);
+ ReleaseSysCache(parentConstrTup);
+
+ ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
+ RelationGetRelid(partition));
+ CommandCounterIncrement();
+
+ return;
+ }
+
/*
* If the referenced table is partitioned, then the partition we're
* attaching now has extra pg_constraint rows and action triggers that are
* no longer needed. Remove those.
+ *
+ * Note that this must be done beforehand, particularly in situations
+ * where we might decide to change the constraint to an ENFORCED state
+ * which will create the required triggers and add the child constraint to
+ * the validation queue. To avoid generating unnecessary triggers and
+ * adding them to the validation queue, it is crucial to eliminate any
+ * redundant constraints beforehand.
*/
if (get_rel_relkind(partConstrFrelid) == RELKIND_PARTITIONED_TABLE)
{
@@ -11628,6 +11663,53 @@ AttachPartitionForeignKey(List **wqueue,
*/
queueValidation = parentConstr->convalidated && !partConstr->convalidated;
+ /*
+ * The case where the parent constraint is ENFORCED and the child
+ * constraint is NOT ENFORCED is not acceptable, as it would violate
+ * referential integrity. In such cases, the child constraint will first
+ * be enforced before merging it with the enforced parent constraint.
+ * Subsequently, removing action triggers, setting up constraint triggers,
+ * and handling check triggers for the parent will be managed in the usual
+ * manner, similar to how two enforced constraints are merged.
+ */
+ if (parentConstrIsEnforced && !partConstrIsEnforced)
+ {
+ ATAlterConstraint *cmdcon = makeNode(ATAlterConstraint);
+ Relation conrel;
+
+ cmdcon->conname = NameStr(partConstr->conname);
+ cmdcon->deferrable = partConstr->condeferrable;
+ cmdcon->initdeferred = partConstr->condeferred;
+ cmdcon->alterEnforceability = true;
+ cmdcon->is_enforced = true;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, trigrel,
+ partConstr->conrelid,
+ partConstr->confrelid,
+ partcontup, AccessExclusiveLock,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid);
+
+ table_close(conrel, RowExclusiveLock);
+
+ CommandCounterIncrement();
+
+ /*
+ * No further validation is needed, as changing the constraint to
+ * enforced will implicitly trigger the same validation.
+ */
+ queueValidation = false;
+ }
+
+ /*
+ * The constraint parent shouldn't be set beforehand, or if it's already
+ * set, it should be the specified parent.
+ */
+ partConstrParentIsSet = OidIsValid(partConstr->conparentid);
+ Assert(!partConstrParentIsSet || partConstr->conparentid == parentConstrOid);
+
ReleaseSysCache(partcontup);
ReleaseSysCache(parentConstrTup);
@@ -11640,8 +11722,10 @@ AttachPartitionForeignKey(List **wqueue,
DropForeignKeyConstraintTriggers(trigrel, partConstrOid, partConstrFrelid,
partConstrRelid);
- ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
- RelationGetRelid(partition));
+ /* Skip if the parent is already set */
+ if (!partConstrParentIsSet)
+ ConstraintSetParentConstraint(partConstrOid, parentConstrOid,
+ RelationGetRelid(partition));
/*
* Like the constraint, attach partition's "check" triggers to the
@@ -12241,6 +12325,17 @@ ATExecAlterConstrEnforceability(List **wqueue, ATAlterConstraint *cmdcon,
/* Drop all the triggers */
DropForeignKeyConstraintTriggers(tgrel, conoid, InvalidOid, InvalidOid);
+
+ /*
+ * If the referenced table is partitioned, the child constraint we're
+ * changing to NOT ENFORCED may have additional pg_constraint rows and
+ * action triggers that remain untouched while this child constraint
+ * is attached to the NOT ENFORCED parent. These must now be removed.
+ * For more details, see AttachPartitionForeignKey().
+ */
+ if (OidIsValid(currcon->conparentid) &&
+ get_rel_relkind(currcon->confrelid) == RELKIND_PARTITIONED_TABLE)
+ RemoveInheritedConstraint(conrel, tgrel, currcon->conrelid, conoid);
}
else if (changed) /* Create triggers */
{
@@ -12566,13 +12661,40 @@ AlterConstrEnforceabilityRecurse(List **wqueue, ATAlterConstraint *cmdcon,
true, NULL, 1, &pkey);
while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
- ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
- pkrelid, childtup, lockmode,
- ReferencedParentDelTrigger,
- ReferencedParentUpdTrigger,
- ReferencingParentInsTrigger,
- ReferencingParentUpdTrigger);
+ {
+ Form_pg_constraint childcon;
+
+ childcon = (Form_pg_constraint) GETSTRUCT(childtup);
+
+ /*
+ * When the parent constraint is modified to be ENFORCED, and the
+ * child constraint is attached to the parent constraint (which is
+ * already ENFORCED), some constraints and action triggers on the
+ * child table may become redundant and need to be removed.
+ */
+ if (cmdcon->is_enforced && childcon->conenforced)
+ {
+ if (currcon->confrelid == pkrelid)
+ {
+ Relation rel = table_open(childcon->conrelid, lockmode);
+
+ AttachPartitionForeignKey(wqueue, rel, childcon->oid,
+ conoid,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger,
+ tgrel);
+ table_close(rel, NoLock);
+ }
+ }
+ else
+ ATExecAlterConstrEnforceability(wqueue, cmdcon, conrel, tgrel, fkrelid,
+ pkrelid, childtup, lockmode,
+ ReferencedParentDelTrigger,
+ ReferencedParentUpdTrigger,
+ ReferencingParentInsTrigger,
+ ReferencingParentUpdTrigger);
+ }
systable_endscan(pscan);
}
@@ -20746,7 +20868,9 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
{
ForeignKeyCacheInfo *fk = lfirst(cell);
HeapTuple contup;
+ HeapTuple parentContup;
Form_pg_constraint conform;
+ Oid parentConstrIsEnforced;
contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
if (!HeapTupleIsValid(contup))
@@ -20765,12 +20889,34 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
continue;
}
+ /* Get the enforcibility of the parent constraint */
+ parentContup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(conform->conparentid));
+ if (!HeapTupleIsValid(parentContup))
+ elog(ERROR, "cache lookup failed for constraint %u",
+ conform->conparentid);
+ parentConstrIsEnforced =
+ ((Form_pg_constraint) GETSTRUCT(parentContup))->conenforced;
+ ReleaseSysCache(parentContup);
+
/*
* The constraint on this table must be marked no longer a child of
* the parent's constraint, as do its check triggers.
*/
ConstraintSetParentConstraint(fk->conoid, InvalidOid, InvalidOid);
+ /*
+ * Unsetting the parent is sufficient when the parent constraint is
+ * NOT ENFORCED and the child constraint is ENFORCED, as we link them
+ * by setting the constraint parent, while leaving the rest unchanged.
+ * For more details, see AttachPartitionForeignKey().
+ */
+ if (!parentConstrIsEnforced && fk->conenforced)
+ {
+ ReleaseSysCache(contup);
+ continue;
+ }
+
/*
* Also, look up the partition's "check" triggers corresponding to the
* ENFORCED constraint being detached and detach them from the parent
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 1d0a4548732..e4e45d0668a 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1673,7 +1673,6 @@ ALTER TABLE fk_partitioned_fk ADD CONSTRAINT fk_partitioned_fk_a_b_fkey FOREIGN
REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
-ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
@@ -1682,8 +1681,65 @@ ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
DROP COLUMN fdrop3, DROP COLUMN fdrop4;
CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 1);
+-- Merge the not-enforced parent constraint with the enforced and not-enforced child constraints
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+-- Merge the non-enforced parent constraint with both the enforced and
+-- non-enforced child constraints, where the referenced table is partitioned.
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+-- Merge the enforced parent constraint with the enforced and not-enforced child constraints.
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | f | f | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | f | f | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | t | t | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | t | t | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
+ALTER TABLE fk_partitioned_fk_3 DETACH PARTITION fk_partitioned_fk_3_0;
+ALTER TABLE fk_partitioned_fk_3 ATTACH PARTITION fk_partitioned_fk_3_0 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | f | f | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | f | f | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | t | t | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | t | t | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | t | t | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
+ALTER TABLE fk_partitioned_fk_3 ALTER CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 NOT ENFORCED;
+-- Merging an enforced parent constraint (validated) with a not-enforced child
+-- constraint will implicitly change the child constraint to enforced and apply
+-- the validation as well.
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
FOR VALUES FROM (2000,2000) TO (3000,3000);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+ conname | conenforced | convalidated | conrelid | confrelid
+---------------------------------+-------------+--------------+-----------------------+----------------------
+ fk_partitioned_fk_3_0_a_b_fkey | t | t | fk_partitioned_fk_3_0 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey | t | t | fk_partitioned_fk_3_1 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_a_b_fkey | t | t | fk_partitioned_fk_3 | fk_notpartitioned_pk
+ fk_partitioned_fk_3_0_a_b_fkey1 | f | f | fk_partitioned_fk_3_0 | fk_partitioned_pk
+ fk_partitioned_fk_3_1_a_b_fkey1 | f | f | fk_partitioned_fk_3_1 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey1 | f | f | fk_partitioned_fk_3 | fk_partitioned_pk
+ fk_partitioned_fk_3_a_b_fkey2 | f | f | fk_partitioned_fk_3 | fk_partitioned_pk_1
+(7 rows)
+
-- Creating a foreign key with ONLY on a partitioned table referencing
-- a non-partitioned table fails.
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
@@ -1698,22 +1754,22 @@ INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
-ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
DETAIL: Key (a, b)=(1500, 1501) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2500, 2502) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_b_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
-ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key constraint "fk_partitioned_fk_3_0_a_b_fkey"
DETAIL: Key (a, b)=(2501, 2503) is not present in table "fk_notpartitioned_pk".
-- but if we insert the values that make them valid, then they work
INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
@@ -1724,7 +1780,7 @@ INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
-- this update fails because there is no referenced row
UPDATE fk_partitioned_fk SET a = a + 1 WHERE a = 2501;
-ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_a_b_fkey"
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key constraint "fk_partitioned_fk_3_1_a_b_fkey"
DETAIL: Key (a, b)=(2502, 2503) is not present in table "fk_notpartitioned_pk".
-- but we can fix it thusly:
INSERT INTO fk_notpartitioned_pk (a,b) VALUES (2502, 2503);
@@ -1867,8 +1923,6 @@ WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid::regclass:
DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
-CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
-CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
CREATE TABLE fk_notpartitioned_fk (b int, a int);
ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
-- Constraint will be invalid.
@@ -2084,7 +2138,7 @@ CREATE TABLE fk_partitioned_fk_2 (b int, a int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
BEGIN;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
--- should be having two constraints
+-- should only have one constraint
\d fk_partitioned_fk_2
Table "public.fk_partitioned_fk_2"
Column | Type | Collation | Nullable | Default
@@ -2093,7 +2147,6 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN
a | integer | | |
Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
Foreign-key constraints:
- "fk_partitioned_fk_2_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED
TABLE "fk_partitioned_fk" CONSTRAINT "fk_partitioned_fk_a_b_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
DROP TABLE fk_partitioned_fk_2;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 7e5be0702da..fa51c7110ef 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1248,18 +1248,49 @@ CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b i
REFERENCES fk_notpartitioned_pk NOT ENFORCED;
CREATE TABLE fk_partitioned_fk_2 (b int, fdrop1 int, fdrop2 int, a int);
ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
-ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
ALTER TABLE fk_partitioned_fk ALTER CONSTRAINT fk_partitioned_fk_a_b_fkey ENFORCED;
+
CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4 int, b int, a int)
PARTITION BY HASH (a);
ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
DROP COLUMN fdrop3, DROP COLUMN fdrop4;
CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES WITH (MODULUS 5, REMAINDER 1);
+-- Merge the not-enforced parent constraint with the enforced and not-enforced child constraints
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT ENFORCED;
+
+-- Merge the non-enforced parent constraint with both the enforced and
+-- non-enforced child constraints, where the referenced table is partitioned.
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+-- Merge the enforced parent constraint with the enforced and not-enforced child constraints.
+ALTER TABLE fk_partitioned_fk_3_0 ADD CONSTRAINT fk_partitioned_fk_3_0_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+ALTER TABLE fk_partitioned_fk_3_1 ADD CONSTRAINT fk_partitioned_fk_3_1_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT ENFORCED;
+ALTER TABLE fk_partitioned_fk_3 ADD CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk ENFORCED;
+
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
+ALTER TABLE fk_partitioned_fk_3 DETACH PARTITION fk_partitioned_fk_3_0;
+ALTER TABLE fk_partitioned_fk_3 ATTACH PARTITION fk_partitioned_fk_3_0 FOR VALUES WITH (MODULUS 5, REMAINDER 0);
+
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
+ALTER TABLE fk_partitioned_fk_3 ALTER CONSTRAINT fk_partitioned_fk_3_a_b_fkey1 NOT ENFORCED;
+
+-- Merging an enforced parent constraint (validated) with a not-enforced child
+-- constraint will implicitly change the child constraint to enforced and apply
+-- the validation as well.
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
FOR VALUES FROM (2000,2000) TO (3000,3000);
+SELECT conname, conenforced, convalidated, conrelid::regclass, confrelid::regclass
+FROM pg_constraint WHERE conrelid::regclass::text like 'fk_partitioned_fk_3%' ORDER BY oid;
+
-- Creating a foreign key with ONLY on a partitioned table referencing
-- a non-partitioned table fails.
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
@@ -1386,8 +1417,6 @@ CREATE TABLE fk_partitioned_fk_3_1 (a int, b int);
DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
-CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
-CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
CREATE TABLE fk_notpartitioned_fk (b int, a int);
ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
@@ -1528,7 +1557,7 @@ CREATE TABLE fk_partitioned_fk_2 (b int, a int,
FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE NOT ENFORCED);
BEGIN;
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
--- should be having two constraints
+-- should only have one constraint
\d fk_partitioned_fk_2
DROP TABLE fk_partitioned_fk_2;
ROLLBACK;
--
2.49.0
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: NOT ENFORCED constraint feature
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox