From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Foreign keys and partitioned tables
Date: Wed, 24 Jan 2018 14:52:45 -0300
Message-ID: <20180124175245.hsakxldr4cd74svv@alvherre.pgsql> (raw)
In-Reply-To: <20171231194359.cvojcour423ulha4@alvherre.pgsql>
Alvaro Herrera wrote:
> This patch enables foreign key constraints to and from partitioned
> tables.
This version is rebased on current master.
0001: fix for a get_relation_info bug in current master.
Posted in <20180124174134.ma4ui2kczmqwb4um@alvherre.pgsql>
0002: Allows local partitioned index to be unique;
Posted in <20180122225559.7pbzomvgp5iwmath@alvherre.pgsql>
0003: Allows FOR EACH ROW triggers on partitioned tables;
Posted in <20180123221027.2qenwwpvgplrrx3d@alvherre.pgsql>
0004: the actual matter of this thread.
0005: bugfix for 0004, after recent changes I introduced in 0004.
It's separate because I am undecided about it being the best
approach; maybe further changes in 0003 are a better approach.
No further changes from the version I posted upthread. Tests pass. I'm
going to review this code now to see what further changes are needed (at
the very least, I think some dependency changes are in order; plus need
to add a few more tests for various ri_triggers.c code paths.)
Thanks
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 53506fd3a9f0cb81334024bf5a0e8856fd8e5e82 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 24 Jan 2018 14:40:26 -0300
Subject: [PATCH v2 1/5] Ignore partitioned indexes in get_relation_info
---
src/backend/optimizer/util/plancat.c | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 8c60b35068..60f21711f4 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -208,6 +208,16 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
}
/*
+ * Ignore partitioned indexes, since they are not usable for+ * queries.+ */+ if (indexRelation->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)+ {+ index_close(indexRelation, NoLock);+ continue;+ }++ /*
* If the index is valid, but cannot yet be used, ignore it; but
* mark the plan we are generating as transient. See
* src/backend/access/heap/README.HOT for discussion.
--
2.11.0
From f67c07baa0a211a42e42b1480c4ffd47b0a7fb06 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 6 Nov 2017 17:04:55 +0100
Subject: [PATCH v2 2/5] allow indexes on partitioned tables to be unique
---
doc/src/sgml/ref/alter_table.sgml | 9 +-
doc/src/sgml/ref/create_table.sgml | 16 +-
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/index.c | 45 ++++-
src/backend/catalog/pg_constraint.c | 76 ++++++++
src/backend/catalog/toasting.c | 4 +-
src/backend/commands/indexcmds.c | 125 +++++++++++--
src/backend/commands/tablecmds.c | 62 ++++++-
src/backend/parser/analyze.c | 7 +
src/backend/parser/parse_utilcmd.c | 31 +---
src/backend/tcop/utility.c | 1 +
src/include/catalog/index.h | 5 +-
src/include/catalog/pg_constraint_fn.h | 4 +-
src/include/commands/defrem.h | 1 +
src/include/parser/parse_utilcmd.h | 3 +-
src/test/regress/expected/alter_table.out | 8 -
src/test/regress/expected/create_table.out | 12 --
src/test/regress/expected/indexing.out | 254 +++++++++++++++++++++++++-
src/test/regress/expected/insert_conflict.out | 2 +-
src/test/regress/sql/alter_table.sql | 2 -
src/test/regress/sql/create_table.sql | 8 -
src/test/regress/sql/indexing.sql | 151 ++++++++++++++-
22 files changed, 740 insertions(+), 88 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex 286c7a8589..c00fd09fe1 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -804,8 +804,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
This form attaches an existing table (which might itself be partitioned)
as a partition of the target table. The table can be attached
as a partition for specific values using <literal>FOR VALUES
- </literal> or as a default partition by using <literal>DEFAULT- </literal>. For each index in the target table, a corresponding+ </literal> or as a default partition by using+ <literal>DEFAULT</literal>.+ For each index in the target table, a corresponding
one will be created in the attached table; or, if an equivalent
index already exists, will be attached to the target table's index,
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
@@ -820,8 +821,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
as the target table and no more; moreover, the column types must also
match. Also, it must have all the <literal>NOT NULL</literal> and
<literal>CHECK</literal> constraints of the target table. Currently
- <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>FOREIGN KEY</literal> constraints are not considered.
+ <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints+ from the parent table will be created in the partition, if they don't+ already exist.
If any of the <literal>CHECK</literal> constraints of the table being
attached is marked <literal>NO INHERIT</literal>, the command will fail;
such a constraint must be recreated without the <literal>NO INHERIT</literal>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgmlindex a0c9a6d257..4c56df8960 100644--- a/doc/src/sgml/ref/create_table.sgml+++ b/doc/src/sgml/ref/create_table.sgml@@ -546,8 +546,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Partitioned tables do not support <literal>UNIQUE</literal>,- <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or+ Partitioned tables do not support+ <literal>EXCLUDE</literal>, or
<literal>FOREIGN KEY</literal> constraints; however, you can define
these constraints on individual partitions.
</para>
@@ -786,6 +786,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
primary key constraint defined for the table. (Otherwise it
would just be the same constraint listed twice.)
</para>
++ <para>+ When used on partitioned tables, <literal>UNIQUE</literal> constraints+ must include all the columns of the partition key.+ </para>
</listitem>
</varlistentry>
@@ -814,6 +819,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
about the design of the schema, since a primary key implies that other
tables can rely on this set of columns as a unique identifier for rows.
</para>
++ <para>+ <literal>PRIMARY KEY</literal> constraints share the restrictions that+ <literal>UNIQUE</literal> constraints have when placed on partitioned+ tables.+ </para>+
</listitem>
</varlistentry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.yindex dfd53fa054..9e81f9514d 100644--- a/src/backend/bootstrap/bootparse.y+++ b/src/backend/bootstrap/bootparse.y@@ -322,6 +322,7 @@ Boot_DeclareIndexStmt:
stmt,
$4,
InvalidOid,
+ InvalidOid,
false,
false,
false,
@@ -367,6 +368,7 @@ Boot_DeclareUniqueIndexStmt:
stmt,
$5,
InvalidOid,
+ InvalidOid,
false,
false,
false,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 849a469127..1660711fb0 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -690,6 +690,8 @@ UpdateIndexRelation(Oid indexoid,
* nonzero to specify a preselected OID.
* parentIndexRelid: if creating an index partition, the OID of the
* parent index; otherwise InvalidOid.
+ * parentConstraintId: if creating a constraint on a partition, the OID+ * of the constraint in the parent; otherwise InvalidOid.
* relFileNode: normally, pass InvalidOid to get new storage. May be
* nonzero to attach an existing valid build.
* indexInfo: same info executor uses to insert into the index
@@ -721,6 +723,7 @@ UpdateIndexRelation(Oid indexoid,
* (only if INDEX_CREATE_ADD_CONSTRAINT is set)
* allow_system_table_mods: allow table to be a system catalog
* is_internal: if true, post creation hook for new index
+ * constraintId: if not NULL, receives OID of created constraint
*
* Returns the OID of the created index.
*/
@@ -729,6 +732,7 @@ index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -741,7 +745,8 @@ index_create(Relation heapRelation,
bits16 flags,
bits16 constr_flags,
bool allow_system_table_mods,
- bool is_internal)+ bool is_internal,+ Oid *constraintId)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -988,6 +993,7 @@ index_create(Relation heapRelation,
if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
{
char constraintType;
+ ObjectAddress localaddr;
if (isprimary)
constraintType = CONSTRAINT_PRIMARY;
@@ -1001,14 +1007,17 @@ index_create(Relation heapRelation,
constraintType = 0; /* keep compiler quiet */
}
- index_constraint_create(heapRelation,+ localaddr = index_constraint_create(heapRelation,
indexRelationId,
+ parentConstraintId,
indexInfo,
indexRelationName,
constraintType,
constr_flags,
allow_system_table_mods,
is_internal);
+ if (constraintId)+ *constraintId = localaddr.objectId;
}
else
{
@@ -1179,6 +1188,8 @@ index_create(Relation heapRelation,
*
* heapRelation: table owning the index (must be suitably locked by caller)
* indexRelationId: OID of the index
+ * parentConstraintId: if constraint is on a partition, the OID of the+ * constraint in the parent.
* indexInfo: same info executor uses to insert into the index
* constraintName: what it say (generally, should match name of index)
* constraintType: one of CONSTRAINT_PRIMARY, CONSTRAINT_UNIQUE, or
@@ -1196,6 +1207,7 @@ index_create(Relation heapRelation,
ObjectAddress
index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
@@ -1210,6 +1222,8 @@ index_constraint_create(Relation heapRelation,
bool deferrable;
bool initdeferred;
bool mark_as_primary;
+ bool islocal;+ int inhcount;
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
@@ -1244,6 +1258,17 @@ index_constraint_create(Relation heapRelation,
deleteDependencyRecordsForClass(RelationRelationId, indexRelationId,
RelationRelationId, DEPENDENCY_AUTO);
+ if (OidIsValid(parentConstraintId))+ {+ islocal = false;+ inhcount = 1;+ }+ else+ {+ islocal = true;+ inhcount = 0;+ }+
/*
* Construct a pg_constraint entry.
*/
@@ -1271,8 +1296,8 @@ index_constraint_create(Relation heapRelation,
NULL, /* no check constraint */
NULL,
NULL,
- true, /* islocal */- 0, /* inhcount */+ islocal,+ inhcount,
true, /* noinherit */
is_internal);
@@ -1293,6 +1318,18 @@ index_constraint_create(Relation heapRelation,
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
/*
+ * Also, if this is a constraint on a partition, mark it as depending+ * on the constraint in the parent.+ */+ if (OidIsValid(parentConstraintId))+ {+ ObjectAddress third;++ ObjectAddressSet(third, ConstraintRelationId, parentConstraintId);+ recordDependencyOn(&referenced, &third, DEPENDENCY_INTERNAL_AUTO);+ }++ /*
* If the constraint is deferrable, create the deferred uniqueness
* checking trigger. (The trigger will be given an internal dependency on
* the constraint by CreateTrigger.)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 442ae7e23d..731c5e4317 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -748,6 +748,43 @@ AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
}
/*
+ * ConstraintSetParentConstraint+ * Set a partition's constraint as child of its parent table's+ *+ * This updates the constraint's pg_constraint row to show it as inherited, and+ * add a dependency to the parent so that it cannot be removed on its own.+ */+void+ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)+{+ Relation constrRel;+ Form_pg_constraint constrForm;+ HeapTuple tuple,+ newtup;+ ObjectAddress depender;+ ObjectAddress referenced;++ constrRel = heap_open(ConstraintRelationId, RowExclusiveLock);+ tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(childConstrId));+ if (!HeapTupleIsValid(tuple))+ elog(ERROR, "cache lookup failed for constraint %u", childConstrId);+ newtup = heap_copytuple(tuple);+ constrForm = (Form_pg_constraint) GETSTRUCT(newtup);+ constrForm->conislocal = false;+ constrForm->coninhcount++;+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);+ ReleaseSysCache(tuple);++ ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);+ ObjectAddressSet(depender, ConstraintRelationId, childConstrId);++ recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);++ heap_close(constrRel, RowExclusiveLock);+}+++/*
* get_relation_constraint_oid
* Find a constraint on the specified relation with the specified name.
* Returns constraint's OID.
@@ -904,6 +941,45 @@ get_relation_constraint_attnos(Oid relid, const char *conname,
}
/*
+ * Return the OID of the constraint associated with the given index in the+ * given relation; or InvalidOid if no such index is catalogued.+ */+Oid+get_relation_idx_constraint_oid(Oid relationId, Oid indexId)+{+ Relation pg_constraint;+ SysScanDesc scan;+ ScanKeyData key;+ HeapTuple tuple;+ Oid constraintId = InvalidOid;++ pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);++ ScanKeyInit(&key,+ Anum_pg_constraint_conrelid,+ BTEqualStrategyNumber,+ F_OIDEQ,+ ObjectIdGetDatum(relationId));+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId,+ true, NULL, 1, &key);+ while ((tuple = systable_getnext(scan)) != NULL)+ {+ Form_pg_constraint constrForm;++ constrForm = (Form_pg_constraint) GETSTRUCT(tuple);+ if (constrForm->conindid == indexId)+ {+ constraintId = HeapTupleGetOid(tuple);+ break;+ }+ }+ systable_endscan(scan);++ heap_close(pg_constraint, AccessShareLock);+ return constraintId;+}++/*
* get_domain_constraint_oid
* Find a constraint on the specified domain with the specified name.
* Returns constraint's OID.
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.cindex cf37011b73..f4e7b83fee 100644--- a/src/backend/catalog/toasting.c+++ b/src/backend/catalog/toasting.c@@ -329,13 +329,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
coloptions[1] = 0;
index_create(toast_rel, toast_idxname, toastIndexOid, InvalidOid,
- InvalidOid,+ InvalidOid, InvalidOid,
indexInfo,
list_make2("chunk_id", "chunk_seq"),
BTREE_AM_OID,
rel->rd_rel->reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
- INDEX_CREATE_IS_PRIMARY, 0, true, true);+ INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex a9461a4b06..b0e5ede488 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -25,6 +25,7 @@
#include "catalog/indexing.h"
#include "catalog/partition.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_constraint_fn.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_opclass.h"
@@ -301,6 +302,8 @@ CheckIndexCompatible(Oid oldId,
* nonzero to specify a preselected OID for the index.
* 'parentIndexId': the OID of the parent index; InvalidOid if not the child
* of a partitioned index.
+ * 'parentConstraintId': the OID of the parent constraint; InvalidOid if not+ * the child of a constraint (only used when recursing)
* 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
* 'check_rights': check for CREATE rights in namespace and tablespace. (This
* should be true except when ALTER is deleting/recreating an index.)
@@ -317,6 +320,7 @@ DefineIndex(Oid relationId,
IndexStmt *stmt,
Oid indexRelationId,
Oid parentIndexId,
+ Oid parentConstraintId,
bool is_alter_table,
bool check_rights,
bool check_not_in_use,
@@ -331,6 +335,7 @@ DefineIndex(Oid relationId,
Oid accessMethodId;
Oid namespaceId;
Oid tablespaceId;
+ Oid createdConstraintId = InvalidOid;
List *indexColNames;
Relation rel;
Relation indexRelation;
@@ -428,20 +433,11 @@ DefineIndex(Oid relationId,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot create index on partitioned table \"%s\" concurrently",
RelationGetRelationName(rel))));
- if (stmt->unique)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot create unique index on partitioned table \"%s\"",- RelationGetRelationName(rel))));
if (stmt->excludeOpNames)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
RelationGetRelationName(rel))));
- if (stmt->primary || stmt->isconstraint)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot create constraints on partitioned tables")));
}
/*
@@ -639,6 +635,84 @@ DefineIndex(Oid relationId,
index_check_primary_key(rel, indexInfo, is_alter_table);
/*
+ * If this table is partitioned and we're creating a unique index or a+ * primary key, make sure that the indexed columns are part of the+ * partition key. Otherwise it would be possible to violate uniqueness by+ * putting values that ought to be unique in different partitions.+ *+ * We could lift this limitation if we had global indexes, but those have+ * their own problems, so this is a useful feature combination.+ */+ if (partitioned && (stmt->unique || stmt->primary))+ {+ PartitionKey key = rel->rd_partkey;+ int i;++ /*+ * A partitioned table can have unique indexes, as long as all the+ * columns in the partition key appear in the unique key. A+ * partition-local index can enforce global uniqueness iff the PK+ * value completely determines the partition that a row is in.+ *+ * Thus, verify that all the columns in the partition key appear+ * in the unique key definition.+ */+ for (i = 0; i < key->partnatts; i++)+ {+ bool found = false;+ int j;+ const char *constraint_type;++ if (stmt->primary)+ constraint_type = "PRIMARY KEY";+ else if (stmt->unique)+ constraint_type = "UNIQUE";+ else if (stmt->excludeOpNames != NIL)+ constraint_type = "EXCLUDE";+ else+ {+ elog(ERROR, "unknown constraint type");+ constraint_type = NULL; /* keep compiler quiet */+ }++ /*+ * It may be possible to support UNIQUE constraints when partition+ * keys are expressions, but is it worth it? Give up for now.+ */+ if (key->partattrs[i] == 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("unsupported %s constraint with partition key definition",+ constraint_type),+ errmsg("%s constraints cannot be used when partition keys include expressions.",+ constraint_type)));++ for (j = 0; j < indexInfo->ii_NumIndexAttrs; j++)+ {+ if (key->partattrs[i] == indexInfo->ii_KeyAttrNumbers[j])+ {+ found = true;+ break;+ }+ }+ if (!found)+ {+ Form_pg_attribute att;++ att = TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("insufficient columns in %s constraint definition",+ constraint_type),+ errdetail("%s constraint on table \"%s\" lacks column \"%s\" which is part of the partition key.",+ constraint_type, RelationGetRelationName(rel),+ NameStr(att->attname))));+ }+ }+ }+++ /*
* We disallow indexes on system columns other than OID. They would not
* necessarily get updated correctly, and they don't seem useful anyway.
*/
@@ -735,12 +809,14 @@ DefineIndex(Oid relationId,
indexRelationId =
index_create(rel, indexRelationName, indexRelationId, parentIndexId,
+ parentConstraintId,
stmt->oldNode, indexInfo, indexColNames,
accessMethodId, tablespaceId,
collationObjectId, classObjectId,
coloptions, reloptions,
flags, constr_flags,
- allowSystemTableMods, !check_rights);+ allowSystemTableMods, !check_rights,+ &createdConstraintId);
ObjectAddressSet(address, RelationRelationId, indexRelationId);
@@ -827,16 +903,40 @@ DefineIndex(Oid relationId,
opfamOids,
attmap, maplen))
{
+ Oid cldConstrOid = InvalidOid;+
/*
- * Found a match. Attach index to parent and we're- * done, but keep lock till commit.+ * Found a match.+ *+ * If this index is being created in the parent+ * because of a constraint, then the child needs to+ * have a constraint also, so look for one. If there+ * is no such constraint, this index is no good, so+ * keep looking.
*/
+ if (createdConstraintId != InvalidOid)+ {+ cldConstrOid =+ get_relation_idx_constraint_oid(childRelid,+ cldidxid);+ if (cldConstrOid == InvalidOid)+ {+ index_close(cldidx, lockmode);+ continue;+ }+ }++ /* Attach index to parent and we're done. */
IndexSetParentIndex(cldidx, indexRelationId);
+ if (createdConstraintId != InvalidOid)+ ConstraintSetParentConstraint(cldConstrOid,+ createdConstraintId);
if (!IndexIsValid(cldidx->rd_index))
invalidate_parent = true;
found = true;
+ /* keep lock till commit */
index_close(cldidx, NoLock);
break;
}
@@ -867,6 +967,7 @@ DefineIndex(Oid relationId,
DefineIndex(childRelid, childStmt,
InvalidOid, /* no predefined OID */
indexRelationId, /* this is our child */
+ createdConstraintId,
false, check_rights, check_not_in_use,
false, quiet);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 2e768dd5e4..5ba7971c43 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -939,17 +939,20 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
Relation idxRel = index_open(lfirst_oid(cell), AccessShareLock);
AttrNumber *attmap;
IndexStmt *idxstmt;
+ Oid constraintOid;
attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
RelationGetDescr(parent),
gettext_noop("could not convert row type"));
idxstmt =
generateClonedIndexStmt(NULL, RelationGetRelid(rel), idxRel,
- attmap, RelationGetDescr(rel)->natts);+ attmap, RelationGetDescr(rel)->natts,+ &constraintOid);
DefineIndex(RelationGetRelid(rel),
idxstmt,
InvalidOid,
RelationGetRelid(idxRel),
+ constraintOid,
false, false, false, false, false);
index_close(idxRel, AccessShareLock);
@@ -6809,6 +6812,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
stmt,
InvalidOid, /* no predefined OID */
InvalidOid, /* no parent index */
+ InvalidOid, /* no parent constraint */
true, /* is_alter_table */
check_rights,
false, /* check_not_in_use - we did it already */
@@ -6901,6 +6905,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
address = index_constraint_create(rel,
index_oid,
+ InvalidOid,
indexInfo,
constraintName,
constraintType,
@@ -14132,6 +14137,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
IndexInfo *info;
AttrNumber *attmap;
bool found = false;
+ Oid constraintOid;
/*
* Ignore indexes in the partitioned table other than partitioned
@@ -14148,6 +14154,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
attmap = convert_tuples_by_name_map(RelationGetDescr(attachrel),
RelationGetDescr(rel),
gettext_noop("could not convert row type"));
+ constraintOid = get_relation_idx_constraint_oid(RelationGetRelid(rel), idx);
/*
* Scan the list of existing indexes in the partition-to-be, and mark
@@ -14156,6 +14163,8 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
*/
for (i = 0; i < list_length(attachRelIdxs); i++)
{
+ Oid cldConstrOid;+
/* does this index have a parent? if so, can't use it */
if (has_superclass(RelationGetRelid(attachrelIdxRels[i])))
continue;
@@ -14168,8 +14177,26 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
attmap,
RelationGetDescr(rel)->natts))
{
+ /*+ * If this index is being created in the parent because of a+ * constraint, then the child needs to have a constraint also,+ * so look for one. If there is no such constraint, this+ * index is no good, so keep looking.+ */+ if (OidIsValid(constraintOid))+ {+ cldConstrOid =+ get_relation_idx_constraint_oid(RelationGetRelid(attachrel),+ RelationGetRelid(attachrelIdxRels[i]));+ /* no dice */+ if (!OidIsValid(cldConstrOid))+ continue;+ }+
/* bingo. */
IndexSetParentIndex(attachrelIdxRels[i], idx);
+ if (OidIsValid(constraintOid))+ ConstraintSetParentConstraint(cldConstrOid, constraintOid);
found = true;
break;
}
@@ -14182,12 +14209,15 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
if (!found)
{
IndexStmt *stmt;
+ Oid constraintOid;
stmt = generateClonedIndexStmt(NULL, RelationGetRelid(attachrel),
idxRel, attmap,
- RelationGetDescr(rel)->natts);+ RelationGetDescr(rel)->natts,+ &constraintOid);
DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid,
RelationGetRelid(idxRel),
+ constraintOid,
false, false, false, false, false);
}
@@ -14430,6 +14460,8 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
bool found;
int i;
PartitionDesc partDesc;
+ Oid constraintOid,+ cldConstrId;
/*
* If this partition already has an index attached, refuse the operation.
@@ -14485,8 +14517,34 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
RelationGetRelationName(parentIdx)),
errdetail("The index definitions do not match.")));
+ /*+ * If there is a constraint in the parent, make sure there is one+ * in the child too.+ */+ constraintOid = get_relation_idx_constraint_oid(RelationGetRelid(parentTbl),+ RelationGetRelid(parentIdx));++ if (OidIsValid(constraintOid))+ {+ cldConstrId = get_relation_idx_constraint_oid(RelationGetRelid(partTbl),+ partIdxId);+ if (!OidIsValid(cldConstrId))+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),+ errmsg("cannot attach index \"%s\" as a partition of index \"%s\"",+ RelationGetRelationName(partIdx),+ RelationGetRelationName(parentIdx)),+ errdetail("The index \"%s\" belongs to a constraint in table \"%s\" but no constraint exists for index \"%s\".",+ RelationGetRelationName(parentIdx),+ RelationGetRelationName(parentTbl),+ RelationGetRelationName(partIdx))));+ }+
/* All good -- do it */
IndexSetParentIndex(partIdx, RelationGetRelid(parentIdx));
+ if (OidIsValid(constraintOid))+ ConstraintSetParentConstraint(cldConstrId, constraintOid);+
pfree(attmap);
CommandCounterIncrement();
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.cindex e7b2bc7e73..5b3a610cf9 100644--- a/src/backend/parser/analyze.c+++ b/src/backend/parser/analyze.c@@ -1017,6 +1017,13 @@ transformOnConflictClause(ParseState *pstate,
TargetEntry *te;
int attno;
+ if (targetrel->rd_partdesc)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("%s cannot be applied to partitioned table \"%s\"",+ "ON CONFLICT DO UPDATE",+ RelationGetRelationName(targetrel))));+
/*
* All INSERT expressions have been parsed, get ready for potentially
* existing SET statements that need to be processed like an UPDATE.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex 5afb363096..a93fe11828 100644--- a/src/backend/parser/parse_utilcmd.c+++ b/src/backend/parser/parse_utilcmd.c@@ -704,12 +704,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("primary key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("primary key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
/* FALL THRU */
case CONSTR_UNIQUE:
@@ -719,12 +713,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("unique constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("unique constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
if (constraint->keys == NIL)
constraint->keys = list_make1(makeString(column->colname));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
@@ -821,12 +809,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("primary key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("primary key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
@@ -837,12 +819,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("unique constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("unique constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
@@ -1184,7 +1160,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
/* Build CREATE INDEX statement to recreate the parent_index */
index_stmt = generateClonedIndexStmt(cxt->relation, InvalidOid,
parent_index,
- attmap, tupleDesc->natts);+ attmap, tupleDesc->natts, NULL);
/* Copy comment on index, if requested */
if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
@@ -1267,7 +1243,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
*/
IndexStmt *
generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
- const AttrNumber *attmap, int attmap_length)+ const AttrNumber *attmap, int attmap_length, Oid *constraintOid)
{
Oid source_relid = RelationGetRelid(source_idx);
HeapTuple ht_idxrel;
@@ -1365,6 +1341,9 @@ generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
HeapTuple ht_constr;
Form_pg_constraint conrec;
+ if (constraintOid)+ *constraintOid = constraintId;+
ht_constr = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(constraintId));
if (!HeapTupleIsValid(ht_constr))
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 3abe7d6155..8c23ee53e2 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1353,6 +1353,7 @@ ProcessUtilitySlow(ParseState *pstate,
stmt,
InvalidOid, /* no predefined OID */
InvalidOid, /* no parent index */
+ InvalidOid, /* no parent constraint */
false, /* is_alter_table */
true, /* check_rights */
true, /* check_not_in_use */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.hindex 235e180299..c8b6fe85c5 100644--- a/src/include/catalog/index.h+++ b/src/include/catalog/index.h@@ -54,6 +54,7 @@ extern Oid index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -66,7 +67,8 @@ extern Oid index_create(Relation heapRelation,
bits16 flags,
bits16 constr_flags,
bool allow_system_table_mods,
- bool is_internal);+ bool is_internal,+ Oid *constraintId);
#define INDEX_CONSTR_CREATE_MARK_AS_PRIMARY (1 << 0)
#define INDEX_CONSTR_CREATE_DEFERRABLE (1 << 1)
@@ -76,6 +78,7 @@ extern Oid index_create(Relation heapRelation,
extern ObjectAddress index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex 6bb1b09714..d3351f4a83 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -58,7 +58,6 @@ extern Oid CreateConstraintEntry(const char *constraintName,
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
-extern void SetValidatedConstraintById(Oid conId);
extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
Oid objNamespace, const char *conname);
@@ -68,10 +67,13 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
Oid newNspId, bool isType, ObjectAddresses *objsMoved);
+extern void ConstraintSetParentConstraint(Oid childConstrId,+ Oid parentConstrId);
extern Oid get_relation_constraint_oid(Oid relid, const char *conname, bool missing_ok);
extern Bitmapset *get_relation_constraint_attnos(Oid relid, const char *conname,
bool missing_ok, Oid *constraintOid);
extern Oid get_domain_constraint_oid(Oid typid, const char *conname, bool missing_ok);
+extern Oid get_relation_idx_constraint_oid(Oid relationId, Oid indexId);
extern Bitmapset *get_primary_key_attnos(Oid relid, bool deferrableOk,
Oid *constraintOid);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.hindex 7b824c95af..f510f40945 100644--- a/src/include/commands/defrem.h+++ b/src/include/commands/defrem.h@@ -26,6 +26,7 @@ extern ObjectAddress DefineIndex(Oid relationId,
IndexStmt *stmt,
Oid indexRelationId,
Oid parentIndexId,
+ Oid parentConstraintId,
bool is_alter_table,
bool check_rights,
bool check_not_in_use,
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.hindex 64aa8234e5..35ac97940a 100644--- a/src/include/parser/parse_utilcmd.h+++ b/src/include/parser/parse_utilcmd.h@@ -29,6 +29,7 @@ extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation
PartitionBoundSpec *spec);
extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel, Oid heapOid,
Relation source_idx,
- const AttrNumber *attmap, int attmap_length);+ const AttrNumber *attmap, int attmap_length,+ Oid *constraintOid);
#endif /* PARSE_UTILCMD_H */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex e9a1d37f6f..ccd2c38dbc 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -3305,14 +3305,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);-ERROR: unique constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD UNIQUE (a);- ^-ALTER TABLE partitioned ADD PRIMARY KEY (a);-ERROR: primary key constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD PRIMARY KEY (a);- ^
ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ERROR: foreign key constraints are not supported on partitioned tables
LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.outindex 8e745402ae..866cc99b9f 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -276,12 +276,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (- a int PRIMARY KEY-) PARTITION BY RANGE (a);-ERROR: primary key constraints are not supported on partitioned tables-LINE 2: a int PRIMARY KEY- ^
CREATE TABLE pkrel (
a int PRIMARY KEY
);
@@ -293,12 +287,6 @@ LINE 2: a int REFERENCES pkrel(a)
^
DROP TABLE pkrel;
CREATE TABLE partitioned (
- a int UNIQUE-) PARTITION BY RANGE (a);-ERROR: unique constraints are not supported on partitioned tables-LINE 2: a int UNIQUE- ^-CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
) PARTITION BY RANGE (a);
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.outindex ffd4b10c37..febd1b3162 100644--- a/src/test/regress/expected/indexing.out+++ b/src/test/regress/expected/indexing.out@@ -26,8 +26,6 @@ drop table idxpart;
-- Some unsupported features
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);-ERROR: cannot create unique index on partitioned table "idxpart"
create index concurrently on idxpart (a);
ERROR: cannot create index on partitioned table "idxpart" concurrently
drop table idxpart;
@@ -744,6 +742,256 @@ select attrelid::regclass, attname, attnum from pg_attribute
(7 rows)
drop table idxpart;
+--+-- Constraint-related indexes+--+-- Verify that it works to add primary key / unique to partitioned tables+create table idxpart (a int primary key, b int) partition by range (a);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | | +Partition key: RANGE (a)+Indexes:+ "idxpart_pkey" PRIMARY KEY, btree (a)+Number of partitions: 0++drop table idxpart;+-- but not if you fail to use the full partition key+create table idxpart (a int unique, b int) partition by range (a, b);+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.+create table idxpart (a int, b int unique) partition by range (a, b);+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "a" which is part of the partition key.+create table idxpart (a int primary key, b int) partition by range (b, a);+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.+create table idxpart (a int, b int primary key) partition by range (b, a);+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "a" which is part of the partition key.+-- OK if you use them in some other order+create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);+drop table idxpart;+create table idxpart (a int primary key, b int) partition by range ((b + a));+ERROR: unsupported PRIMARY KEY constraint with partition key definition+-- not other types of index-based constraints+create table idxpart (a int, exclude (a with = )) partition by range (a);+ERROR: exclusion constraints are not supported on partitioned tables+LINE 1: create table idxpart (a int, exclude (a with = )) partition ...+ ^+-- It works to add primary keys after the partitioned table is created+create table idxpart (a int, b int, c text) partition by range (a, b);+alter table idxpart add primary key (a); -- not an incomplete one tho+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.+alter table idxpart add primary key (a, b);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | not null | + c | text | | | +Partition key: RANGE (a, b)+Indexes:+ "idxpart_pkey" PRIMARY KEY, btree (a, b)+Number of partitions: 0++create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);+\d idxpart1+ Table "public.idxpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | not null | + c | text | | | +Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)+Indexes:+ "idxpart1_pkey" PRIMARY KEY, btree (a, b)++drop table idxpart;+-- It works to add unique constraints after the partitioned table is created+create table idxpart (a int, b int) partition by range (a, b);+alter table idxpart add unique (a); -- ... nope+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.+alter table idxpart add unique (b, a);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | +Partition key: RANGE (a, b)+Indexes:+ "idxpart_b_a_key" UNIQUE CONSTRAINT, btree (b, a)+Number of partitions: 0++drop table idxpart;+-- Exclusion constraints cannot be added+create table idxpart (a int, b int) partition by range (a);+alter table idxpart add exclude (a with =);+ERROR: exclusion constraints are not supported on partitioned tables+LINE 1: alter table idxpart add exclude (a with =);+ ^+drop table idxpart;+-- When (sub)partitions are created, they also contain the constraint+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)+ partition by range (b);+create table idxpart21 partition of idxpart2 for values from (10) to (15);+create table idxpart22 partition of idxpart2 for values from (15) to (20);+create table idxpart3 (b int not null, a int not null);+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+ conname | contype | conrelid | conindid | conkey +----------------+---------+-----------+----------------+--------+ idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}+ idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}+ idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}+(6 rows)++drop table idxpart;+-- Verify that multi-layer partitioning honors the requirement that all+-- columns in the partition key must appear in primary key+create table idxpart (a int, b int, primary key (a)) partition by range (a);+create table idxpart2 partition of idxpart+for values from (0) to (1000) partition by range (b); -- fail+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart2" lacks column "b" which is part of the partition key.+drop table idxpart;+-- Multi-layer partitioning works correctly in this case:+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);+create table idxpart21 partition of idxpart2 for values from (0) to (1000);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+ conname | contype | conrelid | conindid | conkey +----------------+---------+-----------+----------------+--------+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}+(3 rows)++drop table idxpart;+-- If a partitioned table has a unique/PK constraint, then it's not possible+-- to drop the corresponding constraint in the children; nor it's possible+-- to drop the indexes individually. Dropping the constraint in the parent+-- gets rid of the lot.+create table idxpart (i int) partition by hash (i);+create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0);+create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);+alter table idxpart0 add primary key(i);+alter table idxpart add primary key(i);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t+ idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(3 rows)++drop index idxpart0_pkey; -- fail+ERROR: cannot drop index idxpart0_pkey because index idxpart_pkey requires it+HINT: You can drop index idxpart_pkey instead.+drop index idxpart1_pkey; -- fail+ERROR: cannot drop index idxpart1_pkey because index idxpart_pkey requires it+HINT: You can drop index idxpart_pkey instead.+alter table idxpart0 drop constraint idxpart0_pkey; -- fail+ERROR: cannot drop inherited constraint "idxpart0_pkey" of relation "idxpart0"+alter table idxpart1 drop constraint idxpart1_pkey; -- fail+ERROR: cannot drop inherited constraint "idxpart1_pkey" of relation "idxpart1"+alter table idxpart drop constraint idxpart_pkey; -- ok+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+------------+-----------+------------+---------+------------+-------------+--------------+--------------+(0 rows)++drop table idxpart;+-- If a partitioned table has a constraint whose index is not valid,+-- attaching a missing partition makes it valid.+create table idxpart (a int) partition by range (a);+create table idxpart0 (like idxpart);+alter table idxpart0 add primary key (a);+alter table idxpart attach partition idxpart0 for values from (0) to (1000);+alter table only idxpart add primary key (a);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+-----------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | | t | idxpart0_pkey | t | 0 | t | t+ idxpart | idxpart_pkey | | f | idxpart_pkey | t | 0 | t | t+(2 rows)++alter index idxpart_pkey attach partition idxpart0_pkey;+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(2 rows)++drop table idxpart;+-- if a partition has a unique index without a constraint, does not attach+-- automatically; creates a new index instead.+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart add primary key (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+----------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart1 | idxpart1_a_idx | | t | | | | | + idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(3 rows)++drop table idxpart;+-- Can't attach an index without a corresponding constraint+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+alter table only idxpart add primary key (a);+alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail+ERROR: cannot attach index "idxpart1_a_idx" as a partition of index "idxpart_pkey"+DETAIL: The index "idxpart_pkey" belongs to a constraint in table "idxpart" but no constraint exists for index "idxpart1_a_idx".+drop table idxpart;
-- intentionally leave some objects around
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
@@ -755,3 +1003,5 @@ create index on idxpart22 (a);
create index on only idxpart2 (a);
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
create index on idxpart (a);
+create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart_another_1 partition of idxpart_another for values from (0) to (100);diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.outindex 8fd2027d6a..2650faedee 100644--- a/src/test/regress/expected/insert_conflict.out+++ b/src/test/regress/expected/insert_conflict.out@@ -794,7 +794,7 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing;
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
-- however, on conflict do update is not supported yet
insert into parted_conflict_test values (1) on conflict (b) do update set a = excluded.a;
-ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification+ERROR: ON CONFLICT DO UPDATE cannot be applied to partitioned table "parted_conflict_test"
-- but it works OK if we target the partition directly
insert into parted_conflict_test_1 values (1) on conflict (b) do
update set a = excluded.a;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b27e8f6777..b73f523e8a 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -2035,8 +2035,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);-ALTER TABLE partitioned ADD PRIMARY KEY (a);
ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sqlindex 8f9991ef18..fefccf21a2 100644--- a/src/test/regress/sql/create_table.sql+++ b/src/test/regress/sql/create_table.sql@@ -294,10 +294,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
-- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (- a int PRIMARY KEY-) PARTITION BY RANGE (a);-
CREATE TABLE pkrel (
a int PRIMARY KEY
);
@@ -307,10 +303,6 @@ CREATE TABLE partitioned (
DROP TABLE pkrel;
CREATE TABLE partitioned (
- a int UNIQUE-) PARTITION BY RANGE (a);--CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
) PARTITION BY RANGE (a);
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sqlindex 2f985ec866..439d19c621 100644--- a/src/test/regress/sql/indexing.sql+++ b/src/test/regress/sql/indexing.sql@@ -15,7 +15,6 @@ drop table idxpart;
-- Some unsupported features
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);
create index concurrently on idxpart (a);
drop table idxpart;
@@ -375,6 +374,154 @@ select attrelid::regclass, attname, attnum from pg_attribute
order by attrelid::regclass, attnum;
drop table idxpart;
+--+-- Constraint-related indexes+--++-- Verify that it works to add primary key / unique to partitioned tables+create table idxpart (a int primary key, b int) partition by range (a);+\d idxpart+drop table idxpart;++-- but not if you fail to use the full partition key+create table idxpart (a int unique, b int) partition by range (a, b);+create table idxpart (a int, b int unique) partition by range (a, b);+create table idxpart (a int primary key, b int) partition by range (b, a);+create table idxpart (a int, b int primary key) partition by range (b, a);++-- OK if you use them in some other order+create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);+drop table idxpart;++create table idxpart (a int primary key, b int) partition by range ((b + a));+-- not other types of index-based constraints+create table idxpart (a int, exclude (a with = )) partition by range (a);++-- It works to add primary keys after the partitioned table is created+create table idxpart (a int, b int, c text) partition by range (a, b);+alter table idxpart add primary key (a); -- not an incomplete one tho+alter table idxpart add primary key (a, b);+\d idxpart+create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);+\d idxpart1+drop table idxpart;++-- It works to add unique constraints after the partitioned table is created+create table idxpart (a int, b int) partition by range (a, b);+alter table idxpart add unique (a); -- ... nope+alter table idxpart add unique (b, a);+\d idxpart+drop table idxpart;++-- Exclusion constraints cannot be added+create table idxpart (a int, b int) partition by range (a);+alter table idxpart add exclude (a with =);+drop table idxpart;++-- When (sub)partitions are created, they also contain the constraint+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)+ partition by range (b);+create table idxpart21 partition of idxpart2 for values from (10) to (15);+create table idxpart22 partition of idxpart2 for values from (15) to (20);+create table idxpart3 (b int not null, a int not null);+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+drop table idxpart;++-- Verify that multi-layer partitioning honors the requirement that all+-- columns in the partition key must appear in primary key+create table idxpart (a int, b int, primary key (a)) partition by range (a);+create table idxpart2 partition of idxpart+for values from (0) to (1000) partition by range (b); -- fail+drop table idxpart;++-- Multi-layer partitioning works correctly in this case:+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);+create table idxpart21 partition of idxpart2 for values from (0) to (1000);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+drop table idxpart;++-- If a partitioned table has a unique/PK constraint, then it's not possible+-- to drop the corresponding constraint in the children; nor it's possible+-- to drop the indexes individually. Dropping the constraint in the parent+-- gets rid of the lot.+create table idxpart (i int) partition by hash (i);+create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0);+create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);+alter table idxpart0 add primary key(i);+alter table idxpart add primary key(i);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop index idxpart0_pkey; -- fail+drop index idxpart1_pkey; -- fail+alter table idxpart0 drop constraint idxpart0_pkey; -- fail+alter table idxpart1 drop constraint idxpart1_pkey; -- fail+alter table idxpart drop constraint idxpart_pkey; -- ok+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- If a partitioned table has a constraint whose index is not valid,+-- attaching a missing partition makes it valid.+create table idxpart (a int) partition by range (a);+create table idxpart0 (like idxpart);+alter table idxpart0 add primary key (a);+alter table idxpart attach partition idxpart0 for values from (0) to (1000);+alter table only idxpart add primary key (a);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+alter index idxpart_pkey attach partition idxpart0_pkey;+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- if a partition has a unique index without a constraint, does not attach+-- automatically; creates a new index instead.+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart add primary key (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- Can't attach an index without a corresponding constraint+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+alter table only idxpart add primary key (a);+alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail+drop table idxpart;+
-- intentionally leave some objects around
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
@@ -386,3 +533,5 @@ create index on idxpart22 (a);
create index on only idxpart2 (a);
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
create index on idxpart (a);
+create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart_another_1 partition of idxpart_another for values from (0) to (100);
--
2.11.0
From 2e150936a0acdbb0b959673a047eac8a0a10e80e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v2 3/5] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/index.c | 3 +-
src/backend/commands/tablecmds.c | 87 ++++++++++++++++++++--
src/backend/commands/trigger.c | 124 ++++++++++++++++++++++++++++---
src/backend/tcop/utility.c | 3 +-
src/include/commands/trigger.h | 2 +-
src/test/regress/expected/triggers.out | 130 ++++++++++++++++++++++++++++++---
src/test/regress/sql/triggers.sql | 87 +++++++++++++++++++---
7 files changed, 393 insertions(+), 43 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 1660711fb0..e126f1f19b 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -1356,7 +1356,8 @@ index_constraint_create(Relation heapRelation,
trigger->constrrel = NULL;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
- InvalidOid, conOid, indexRelationId, true);+ InvalidOid, conOid, indexRelationId, InvalidOid,+ InvalidOid, true);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 5ba7971c43..9ca88894de 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -487,6 +487,7 @@ static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
List *scanrel_children,
List *partConstraint,
bool validate_default);
+static void CloneRowTriggersOnPartition(Oid parentId, Oid partitionId);
static ObjectAddress ATExecDetachPartition(Relation rel, RangeVar *name);
static ObjectAddress ATExecAttachPartitionIdx(List **wqueue, Relation rel,
RangeVar *name);
@@ -916,9 +917,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes defined in- * the parent. We can't do it earlier, because DefineIndex wants to know- * the partition key which we just stored.+ * If we're creating a partition, create now all the indexes and triggers+ * defined in the parent.+ *+ * We can't do it earlier, because DefineIndex wants to know the partition+ * key which we just stored.
*/
if (stmt->partbound)
{
@@ -959,6 +962,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
list_free(idxlist);
++ /*+ * If there are any triggers, clone the appropriate ones to the new+ * partition.+ */+ if (parent->trigdesc != NULL)+ CloneRowTriggersOnPartition(RelationGetRelid(parent), relationId);+
heap_close(parent, NoLock);
}
@@ -8431,7 +8442,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8505,7 +8516,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8560,7 +8571,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -14015,6 +14026,8 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* Ensure there exists a correct set of indexes in the partition. */
AttachPartitionEnsureIndexes(rel, attachrel);
+ /* and triggers */+ CloneRowTriggersOnPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));
/*
* Generate partition constraint from the partition bound specification.
@@ -14224,6 +14237,9 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
index_close(idxRel, AccessShareLock);
}
+ /* Make this all visible */+ CommandCounterIncrement();+
/* Clean up. */
for (i = 0; i < list_length(attachRelIdxs); i++)
index_close(attachrelIdxRels[i], AccessShareLock);
@@ -14232,6 +14248,65 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
}
/*
+ * CloneRowTriggersOnPartition+ * subroutine for ATExecAttachPartition/DefineRelation to create row+ * triggers on partitions+ */+static void+CloneRowTriggersOnPartition(Oid parentId, Oid partitionId)+{+ Relation pg_trigger;+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key, Anum_pg_trigger_tgrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ pg_trigger = heap_open(TriggerRelationId, RowExclusiveLock);+ scan = systable_beginscan(pg_trigger, TriggerRelidNameIndexId,+ true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ {+ Form_pg_trigger trigForm;+ CreateTrigStmt *trigStmt;++ trigForm = (Form_pg_trigger) GETSTRUCT(tuple);+ if (!TRIGGER_FOR_ROW(trigForm->tgtype) ||+ TRIGGER_FOR_BEFORE(trigForm->tgtype) ||+ TRIGGER_FOR_INSTEAD(trigForm->tgtype) ||+ OidIsValid(trigForm->tgconstraint))+ continue;++ trigStmt = makeNode(CreateTrigStmt);++ trigStmt->trigname = NameStr(trigForm->tgname);+ trigStmt->relation = NULL;+ trigStmt->funcname = NULL;+ trigStmt->args = NULL;+ trigStmt->row = true;+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;+ trigStmt->columns = NIL;+ trigStmt->whenClause = NULL;+ trigStmt->isconstraint = false;+ trigStmt->transitionRels = NIL;+ trigStmt->deferrable = trigForm->tgdeferrable;+ trigStmt->initdeferred = trigForm->tginitdeferred;+ trigStmt->constrrel = NULL;++ CreateTrigger(trigStmt, NULL, partitionId,+ InvalidOid, InvalidOid, InvalidOid,+ trigForm->tgfoid, HeapTupleGetOid(tuple), false);+ pfree(trigStmt);+ }++ systable_endscan(scan);++ heap_close(pg_trigger, RowExclusiveLock);+}++/*
* ALTER TABLE DETACH PARTITION
*
* Return the address of the relation that is no longer a partition of rel.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 160d941c00..7cb709ea26 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -125,6 +125,12 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* indexOid, if nonzero, is the OID of an index associated with the constraint.
* We do nothing with this except store it into pg_trigger.tgconstrindid.
*
+ * funcoid, if nonzero, is the OID of the function to invoke. When this is+ * given, stmt->funcname is ignored.+ *+ * parentTriggerOid, if nonzero, is a trigger that begets this one; so that+ * if that trigger is dropped, this one should be too.+ *
* If isInternal is true then this is an internally-generated trigger.
* This argument sets the tgisinternal field of the pg_trigger entry, and
* if true causes us to modify the given trigger name to ensure uniqueness.
@@ -133,6 +139,10 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, this function recurses to create the+ * trigger on all the partitions, except if isInternal is true, in which+ * case caller is expected to execute recursion on its own.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -140,7 +150,7 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal)+ Oid funcoid, Oid parentTriggerOid, bool isInternal)
{
int16 tgtype;
int ncolumns;
@@ -159,7 +169,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Relation pgrel;
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
- Oid funcoid;
Oid funcrettype;
Oid trigoid;
char internaltrigname[NAMEDATALEN];
@@ -179,8 +188,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +198,69 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * Disallow WHEN clauses; I think it's okay, but disallow for now+ * to reduce testing surface.+ */+ if (stmt->whenClause)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.")));++ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));++ /*+ * Constraint triggers are not allowed, either.+ */+ if (stmt->isconstraint)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.")));++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -587,7 +651,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Find and validate the trigger function.
*/
- funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);+ if (!OidIsValid(funcoid))+ funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);
if (!isInternal)
{
aclresult = pg_proc_aclcheck(funcoid, GetUserId(), ACL_EXECUTE);
@@ -928,11 +993,18 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* User CREATE TRIGGER, so place dependencies. We make trigger be
* auto-dropped if its relation is dropped or if the FK relation is
* dropped. (Auto drop is compatible with our pre-7.3 behavior.)
+ *+ * Exception: if this trigger comes from a parent partitioned table,+ * then it's not separately drop-able, but goes away if the partition+ * does.
*/
referenced.classId = RelationRelationId;
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
- recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);+ recordDependencyOn(&myself, &referenced, OidIsValid(parentTriggerOid) ?+ DEPENDENCY_INTERNAL_AUTO :+ DEPENDENCY_AUTO);+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -954,6 +1026,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectSubId = 0;
recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL);
}
++ /* Depends on the parent trigger, if there is one. */+ if (OidIsValid(parentTriggerOid))+ {+ ObjectAddressSet(referenced, TriggerRelationId, parentTriggerOid);+ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL_AUTO);+ }
}
/* If column-specific trigger, add normal dependencies on columns */
@@ -982,6 +1061,31 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
+ /*+ * If this is a FOR EACH ROW trigger on a partitioned table, recurse for+ * each partition if invoked directly by user (otherwise, caller must do+ * its own recursion).+ */+ if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ !isInternal)+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ int i;++ for (i = 0; i < partdesc->nparts; i++)+ {+ /* XXX must create a separate constraint for each child */+ Assert(constraintOid == InvalidOid);+ /* XXX must create a separate index for each child */+ Assert(indexOid == InvalidOid);++ CreateTrigger(copyObject(stmt), queryString,+ partdesc->oids[i], refRelOid,+ constraintOid, indexOid,+ InvalidOid, trigoid, isInternal);+ }+ }+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 8c23ee53e2..f60f31249a 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1507,7 +1507,8 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, false);+ InvalidOid, InvalidOid, InvalidOid,+ InvalidOid, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.hindex ff5546cf28..3970ab06b4 100644--- a/src/include/commands/trigger.h+++ b/src/include/commands/trigger.h@@ -159,7 +159,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal);+ Oid funcid, Oid parentTriggerOid, bool isInternal);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex 9a7aafcc96..54116064b7 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1815,7 +1815,80 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart2 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(4 rows)++drop trigger f on trigpart1; -- fail+ERROR: cannot drop trigger f on table trigpart1 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart2; -- fail+ERROR: cannot drop trigger f on table trigpart2 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart3; -- fail+ERROR: cannot drop trigger f on table trigpart3 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(3 rows)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1832,7 +1905,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1845,27 +1918,51 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
@@ -1874,6 +1971,8 @@ NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted2_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
@@ -1889,21 +1988,28 @@ NOTICE: trigger on parted_stmt_trig BEFORE UPDATE for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE for ROW
NOTICE: trigger on parted2_stmt_trig BEFORE UPDATE for STATEMENT
NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger on parted_stmt_trig AFTER UPDATE for STATEMENT
NOTICE: trigger on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
drop table parted_stmt_trig, parted2_stmt_trig;
--
-- Test the interaction between transition tables and both kinds of
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex 47b5bde390..8dee659757 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1292,7 +1292,50 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart1; -- fail+drop trigger f on trigpart2; -- fail+drop trigger f on trigpart3; -- fail+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1312,7 +1355,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1326,28 +1369,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
--
2.11.0
From f5dc4e06948dcd35d50dd2585f67c27fd74f5841 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:54:14 -0300
Subject: [PATCH v2 4/5] [WIP] Allow foreign key triggers on partitioned tables
---
src/backend/catalog/pg_constraint.c | 192 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 109 +++++++++++++---
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 50 +++-----
src/include/catalog/pg_constraint_fn.h | 2 +
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 115 ++++++++++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 67 ++++++++++
src/test/regress/sql/alter_table.sql | 57 ++++++++-
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/foreign_key.sql | 28 +++++
12 files changed, 568 insertions(+), 86 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 731c5e4317..bfa580cc0a 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -26,6 +26,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "commands/tablecmds.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
@@ -375,6 +376,197 @@ CreateConstraintEntry(const char *constraintName,
return conOid;
}
+/*+ * For each foreign key constraint in relation parentId, create a cloned+ * copy of it for relationId.+ *+ * relationId is a partition of parentId, so we can be certain that it has+ * the same columns with the same datatypes. They may be in different order,+ * though.+ */+void+CloneForeignKeyConstraints(Oid parentId, Oid relationId)+{+ Relation pg_constraint;+ Relation rel;+ ScanKeyData key;+ SysScanDesc scan;+ TupleDesc tupdesc;+ HeapTuple tuple;++ /* see ATAddForeignKeyConstraint about lock level */+ rel = heap_open(relationId, AccessExclusiveLock);++ pg_constraint = heap_open(ConstraintRelationId, RowShareLock);+ tupdesc = RelationGetDescr(pg_constraint);++ ScanKeyInit(&key,+ Anum_pg_constraint_conrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,+ NULL, 1, &key);++ while ((tuple = systable_getnext(scan)) != NULL)+ {+ Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);+ AttrNumber conkey[INDEX_MAX_KEYS];+ AttrNumber confkey[INDEX_MAX_KEYS];+ Oid conpfeqop[INDEX_MAX_KEYS];+ Oid conppeqop[INDEX_MAX_KEYS];+ Oid conffeqop[INDEX_MAX_KEYS];+ Constraint *fkconstraint;+ Oid constrOid;+ ObjectAddress parentAddr,+ childAddr;+ int nelem;+ ArrayType *arr;+ Datum datum;+ bool isnull;++ /* only foreign keys */+ if (constrForm->contype != CONSTRAINT_FOREIGN)+ continue;++ ObjectAddressSet(parentAddr, ConstraintRelationId,+ HeapTupleGetOid(tuple));++ datum = fastgetattr(tuple, Anum_pg_constraint_conkey,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conkey");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != INT2OID)+ elog(ERROR, "conkey is not a 1-D smallint array");+ memcpy(conkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));++ datum = fastgetattr(tuple, Anum_pg_constraint_confkey,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null confkey");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != INT2OID)+ elog(ERROR, "confkey is not a 1-D smallint array");+ memcpy(confkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));++ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conpfeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conpfeqop is not a 1-D OID array");+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conpfeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conpfeqop is not a 1-D OID array");+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conppeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conppeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conppeqop is not a 1-D OID array");+ memcpy(conppeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conffeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conffeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conffeqop is not a 1-D OID array");+ memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ constrOid =+ CreateConstraintEntry(NameStr(constrForm->conname),+ constrForm->connamespace,+ CONSTRAINT_FOREIGN,+ constrForm->condeferrable,+ constrForm->condeferred,+ constrForm->convalidated,+ relationId,+ conkey,+ nelem,+ InvalidOid, /* not a domain constraint */+ constrForm->conindid, /* same index */+ constrForm->confrelid, /* same foreign rel */+ confkey,+ conpfeqop,+ conppeqop,+ conffeqop,+ nelem,+ constrForm->confupdtype,+ constrForm->confdeltype,+ constrForm->confmatchtype,+ NULL,+ NULL,+ NULL,+ NULL,+ false,+ 1, false, true);++ ObjectAddressSet(childAddr, ConstraintRelationId, constrOid);+ recordDependencyOn(&childAddr, &parentAddr, DEPENDENCY_INTERNAL);++ fkconstraint = makeNode(Constraint);+ /* for now this is all we need */+ fkconstraint->fk_upd_action = constrForm->confupdtype;+ fkconstraint->fk_del_action = constrForm->confdeltype;+ fkconstraint->deferrable = constrForm->condeferrable;+ fkconstraint->initdeferred = constrForm->condeferred;++ createForeignKeyTriggers(rel, constrForm->confrelid, fkconstraint,+ constrOid, constrForm->conindid);++ /*+ * XXX Normal constraint creation can be invoked during ALTER and+ * so it needs ALTER TABLE's phase 3 checking. Current caller is just+ * CREATE TABLE .. PARTITION OF so we don't need it, but maybe for+ * ALTER TABLE .. ATTACH PARTITION we'll need it.+ */+ }+ systable_endscan(scan);++ heap_close(rel, NoLock); /* keep lock till commit */+ heap_close(pg_constraint, RowShareLock);+}
/*
* Test whether given name is currently used as a constraint name
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 9ca88894de..25fd76c3f0 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -338,9 +338,6 @@ static void validateCheckConstraint(Relation rel, HeapTuple constrtup);
static void validateForeignKeyConstraint(char *conname,
Relation rel, Relation pkrel,
Oid pkindOid, Oid constraintOid);
-static void createForeignKeyTriggers(Relation rel, Oid refRelOid,- Constraint *fkconstraint,- Oid constraintOid, Oid indexOid);
static void ATController(AlterTableStmt *parsetree,
Relation rel, List *cmds, bool recurse, LOCKMODE lockmode);
static void ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
@@ -411,8 +408,10 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue,
Constraint *constr,
bool recurse, bool recursing, bool is_readd,
LOCKMODE lockmode);
-static ObjectAddress ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,- Constraint *fkconstraint, LOCKMODE lockmode);+static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab,+ Relation rel,+ Constraint *fkconstraint, bool recurse, bool recursing,+ LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -505,6 +504,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
* relkind: relkind to assign to the new relation
* ownerId: if not InvalidOid, use this as the new relation's owner.
* typaddress: if not null, it's set to the pg_type entry's address.
+ * queryString: for error reporting
*
* Note that permissions checks are done against current user regardless of
* ownerId. A nonzero ownerId is used when someone is creating a relation
@@ -917,8 +917,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes and triggers- * defined in the parent.+ * If we're creating a partition, create now all the indexes, triggers,+ * FKs defined in the parent.
*
* We can't do it earlier, because DefineIndex wants to know the partition
* key which we just stored.
@@ -970,6 +970,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (parent->trigdesc != NULL)
CloneRowTriggersOnPartition(RelationGetRelid(parent), relationId);
+ /* And foreign keys too */+ CloneForeignKeyConstraints(parentId, relationId);+
heap_close(parent, NoLock);
}
@@ -6986,7 +6989,8 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
RelationGetNamespace(rel),
NIL);
- address = ATAddForeignKeyConstraint(tab, rel, newConstraint,+ address = ATAddForeignKeyConstraint(wqueue, tab, rel,+ newConstraint, recurse, false,
lockmode);
break;
@@ -7141,8 +7145,9 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* We do permissions checks here, however.
*/
static ObjectAddress
-ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,- Constraint *fkconstraint, LOCKMODE lockmode)+ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,+ Constraint *fkconstraint, bool recurse,+ bool recursing, LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7176,12 +7181,22 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
* numbers)
*/
if (pkrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("cannot reference partitioned table \"%s\"",- RelationGetRelationName(pkrel))));+ {+ if (!recurse)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("foreign key referencing partitioned table \"%s\" must not be ONLY",+ RelationGetRelationName(pkrel))));+ /* fix recursion in ATExecValidateConstraint to enable this case */+ if (fkconstraint->skip_validation && !fkconstraint->initially_valid)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("cannot add NOT VALID foreign key to relation \"%s\"",+ RelationGetRelationName(pkrel))));+ }- if (pkrel->rd_rel->relkind != RELKIND_RELATION)+ if (pkrel->rd_rel->relkind != RELKIND_RELATION &&+ pkrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("referenced relation \"%s\" is not a table",
@@ -7541,6 +7556,45 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
}
/*
+ * If this is a partitioned table, recurse to create the constraint on the+ * partitions also.+ */+ if (recurse)+ {+ List *children;+ ListCell *child;++ /* XXX why not find_all_inheritors? */+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);++ foreach(child, children)+ {+ Oid childrelid = lfirst_oid(child);+ Relation childrel;+ AlteredTableInfo *childtab;+ ObjectAddress childAddr;++ /* find_inheritance_children already got lock */+ childrel = heap_open(childrelid, NoLock);+ CheckTableNotInUse(childrel, "ALTER TABLE"); /* XXX do we need this? */++ /* Find or create work queue entry for this table */+ childtab = ATGetQueueEntry(wqueue, childrel);++ /* Recurse to child */+ childAddr =+ ATAddForeignKeyConstraint(wqueue, childtab, childrel,+ fkconstraint, recurse, true,+ lockmode);++ /* make sure they go away together, or not at all */+ recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL);++ heap_close(childrel, NoLock);+ }+ }++ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -7802,8 +7856,8 @@ ATExecValidateConstraint(Relation rel, char *constrName, bool recurse,
heap_close(refrel, NoLock);
/*
- * Foreign keys do not inherit, so we purposely ignore the- * recursion bit here+ * We disallow creating invalid foreign keys to or from+ * partitioned tables, so ignoring the recursion bit is okay.
*/
}
else if (con->contype == CONSTRAINT_CHECK)
@@ -8454,7 +8508,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
* NB: if you change any trigger properties here, see also
* ATExecAlterConstraint.
*/
-static void+void
createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
Oid constraintOid, Oid indexOid)
{
@@ -8584,6 +8638,25 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
indexOid, true);
CreateFKCheckTrigger(myRelOid, refRelOid, fkconstraint, constraintOid,
indexOid, false);
++ /*+ * If this is a partitioned table, recurse to create triggers for each+ * child. We consider that one pg_constraint entry is enough; we only+ * need the triggers to appear per-partition.+ */+ if (get_rel_relkind(refRelOid) == RELKIND_PARTITIONED_TABLE)+ {+ ListCell *cell;+ List *dchildren;++ /* XXX maybe we need a stronger lock? */+ dchildren = find_inheritance_children(refRelOid, RowShareLock);+ foreach(cell, dchildren)+ {+ createForeignKeyTriggers(rel, lfirst_oid(cell), fkconstraint,+ constraintOid, indexOid);+ }+ }
}
/*
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex a93fe11828..f84b05c544 100644--- a/src/backend/parser/parse_utilcmd.c+++ b/src/backend/parser/parse_utilcmd.c@@ -730,12 +730,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("foreign key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
/*
* Fill in the current attribute's name and throw it into the
@@ -849,12 +843,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("foreign key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->fkconstraints = lappend(cxt->fkconstraints, constraint);
break;
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.cindex 8faae1d069..b3dd174d28 100644--- a/src/backend/utils/adt/ri_triggers.c+++ b/src/backend/utils/adt/ri_triggers.c@@ -401,7 +401,7 @@ RI_FKey_check(TriggerData *trigdata)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding FK attributes.
@@ -409,7 +409,7 @@ RI_FKey_check(TriggerData *trigdata)
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -537,7 +537,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* PK attributes themselves.
@@ -545,7 +545,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -793,7 +793,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> x WHERE $1 = fkatt1 [AND ...]+ * SELECT 1 FROM <fktable> x WHERE $1 = fkatt1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -801,7 +801,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -951,14 +951,14 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * DELETE FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]+ * DELETE FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "DELETE FROM ONLY %s", fkrelname);+ appendStringInfo(&querybuf, "DELETE FROM %s", fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -1122,7 +1122,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]+ * UPDATE <fktable> SET fkatt1 = $1 [, ...]
* WHERE $n = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes. Note that we are assuming
@@ -1133,7 +1133,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
@@ -1342,7 +1342,7 @@ ri_setnull(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]+ * UPDATE <fktable> SET fkatt1 = NULL [, ...]
* WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -1351,7 +1351,7 @@ ri_setnull(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1559,7 +1559,7 @@ ri_setdefault(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]+ * UPDATE <fktable> SET fkatt1 = DEFAULT [, ...]
* WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -1568,7 +1568,7 @@ ri_setdefault(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1895,8 +1895,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
/*----------
* The query string built is:
- * SELECT fk.keycols FROM ONLY relname fk- * LEFT OUTER JOIN ONLY pkrelname pk+ * SELECT fk.keycols FROM relname fk+ * LEFT OUTER JOIN pkrelname pk
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
* WHERE pk.pkkeycol1 IS NULL AND
* For MATCH SIMPLE:
@@ -1922,7 +1922,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
quoteRelationName(pkrelname, pk_rel);
quoteRelationName(fkrelname, fk_rel);
appendStringInfo(&querybuf,
- " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON",+ " FROM %s fk LEFT OUTER JOIN %s pk ON",
fkrelname, pkrelname);
strcpy(pkattname, "pk.");
@@ -2345,22 +2345,6 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
/* Find or create a hashtable entry for the constraint */
riinfo = ri_LoadConstraintInfo(constraintOid);
- /* Do some easy cross-checks against the trigger call data */- if (rel_is_pk)- {- if (riinfo->fk_relid != trigger->tgconstrrelid ||- riinfo->pk_relid != RelationGetRelid(trig_rel))- elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",- trigger->tgname, RelationGetRelationName(trig_rel));- }- else- {- if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||- riinfo->pk_relid != trigger->tgconstrrelid)- elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",- trigger->tgname, RelationGetRelationName(trig_rel));- }-
return riinfo;
}
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex d3351f4a83..8f0628da05 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -56,6 +56,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern void CloneForeignKeyConstraints(Oid parentId, Oid relationId);+
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.hindex 06e5180a30..3896da3243 100644--- a/src/include/commands/tablecmds.h+++ b/src/include/commands/tablecmds.h@@ -74,6 +74,10 @@ extern void find_composite_type_dependencies(Oid typeOid,
extern void check_of_type(HeapTuple typetuple);
+extern void createForeignKeyTriggers(Relation rel, Oid refRelOid,+ Constraint *fkconstraint, Oid constraintOid,+ Oid indexOid);+
extern void register_on_commit_action(Oid relid, OnCommitAction action);
extern void remove_on_commit_action(Oid relid);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex ccd2c38dbc..375a357125 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -485,6 +485,117 @@ DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and from partitioned tables+SET search_path TO at_tst;+CREATE SCHEMA at_tst;+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned+ FOR VALUES FROM (0) TO (10000);+-- these fail:+INSERT INTO at_regular2 VALUES (1000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(1000) is not present in table "at_partitioned".+INSERT INTO at_partitioned VALUES (1000, 42);+ERROR: insert or update on table "at_partitioned_0" violates foreign key constraint "at_partitioned_reg1_col1_fkey"+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".+-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);+CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned+ FOR VALUES FROM (10000) TO (20000);+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+ERROR: column "col2" in child table must be marked NOT NULL+ALTER TABLE at_partitioned_2+ ALTER col2 SET NOT NULL,+ ALTER reg1_col1 SET NOT NULL;+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+\d at_regular2+ Table "at_tst.at_regular2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ col3 | integer | | | +Foreign-key constraints:+ "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)++\d at_partitioned+ Table "at_tst.at_partitioned"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition key: RANGE (col2)+Indexes:+ "at_partitioned_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)+Referenced by:+ TABLE "at_regular2" CONSTRAINT "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)+Number of partitions: 3 (Use \d+ to list them.)++\d at_partitioned_0+ Table "at_tst.at_partitioned_0"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (0) TO (10000)+Indexes:+ "at_partitioned_0_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_1+ Table "at_tst.at_partitioned_1"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (10000) TO (20000)+Indexes:+ "at_partitioned_1_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_2+ Table "at_tst.at_partitioned_2"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ reg1_col1 | integer | | not null | + col2 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (20000) TO (30000)+Indexes:+ "at_partitioned_2_pkey" PRIMARY KEY, btree (col2)++INSERT INTO at_partitioned VALUES (5000, 42);+ERROR: insert or update on table "at_partitioned_0" violates foreign key constraint "at_partitioned_reg1_col1_fkey"+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".+INSERT INTO at_regular1 VALUES (42), (1042), (2042);+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(35000) is not present in table "at_partitioned".+-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;+-- disallowed: must drop it from parent instead+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+ERROR: cannot drop inherited constraint "at_partitioned_reg1_col1_fkey" of relation "at_partitioned_0"+-- ok+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+\set VERBOSITY terse+DROP SCHEMA at_tst CASCADE;+NOTICE: drop cascades to 3 other objects+\set VERBOSITY default+RESET search_path;
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -3305,10 +3416,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;-ERROR: foreign key constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;- ^
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
ERROR: exclusion constraints are not supported on partitioned tables
LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.outindex 866cc99b9f..7c3703b73d 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -276,16 +276,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (- a int PRIMARY KEY-);-CREATE TABLE partitioned (- a int REFERENCES pkrel(a)-) PARTITION BY RANGE (a);-ERROR: foreign key constraints are not supported on partitioned tables-LINE 2: a int REFERENCES pkrel(a)- ^-DROP TABLE pkrel;
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.outindex fef072eddf..8c71c21973 100644--- a/src/test/regress/expected/foreign_key.out+++ b/src/test/regress/expected/foreign_key.out@@ -1415,3 +1415,70 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
commit;
drop table pktable2, fktable2;
+--+-- Foreign keys and partitioned tables+--+-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f1 int primary key);+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO fkpart1 VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+UPDATE pkregular SET f1 = 501;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ERROR: cannot drop inherited constraint "fkpartit_f1_fkey" of relation "fkpart1"+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)++ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++DELETE FROM pkregular;+SELECT * FROM fkpartit;+ f1 +----+(0 rows)+diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b73f523e8a..f6dedf0287 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -394,6 +394,62 @@ DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and from partitioned tables+SET search_path TO at_tst;+CREATE SCHEMA at_tst;+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned+ FOR VALUES FROM (0) TO (10000);+-- these fail:+INSERT INTO at_regular2 VALUES (1000);+INSERT INTO at_partitioned VALUES (1000, 42);++-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);++CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned+ FOR VALUES FROM (10000) TO (20000);+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+ALTER TABLE at_partitioned_2+ ALTER col2 SET NOT NULL,+ ALTER reg1_col1 SET NOT NULL;+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);++\d at_regular2+\d at_partitioned+\d at_partitioned_0+\d at_partitioned_1+\d at_partitioned_2++INSERT INTO at_partitioned VALUES (5000, 42);+INSERT INTO at_regular1 VALUES (42), (1042), (2042);+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);++-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;++-- disallowed: must drop it from parent instead+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+-- ok+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;++\set VERBOSITY terse+DROP SCHEMA at_tst CASCADE;+\set VERBOSITY default+RESET search_path;+
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -2035,7 +2091,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-- cannot drop column that is part of the partition key
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sqlindex fefccf21a2..09a634d79d 100644--- a/src/test/regress/sql/create_table.sql+++ b/src/test/regress/sql/create_table.sql@@ -294,14 +294,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (- a int PRIMARY KEY-);-CREATE TABLE partitioned (- a int REFERENCES pkrel(a)-) PARTITION BY RANGE (a);-DROP TABLE pkrel;-
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sqlindex 5f19dad03c..1a2c93fcce 100644--- a/src/test/regress/sql/foreign_key.sql+++ b/src/test/regress/sql/foreign_key.sql@@ -1055,3 +1055,31 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
commit;
drop table pktable2, fktable2;
+++--+-- Foreign keys and partitioned tables+--++-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f1 int primary key);+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+UPDATE pkregular SET f1 = 501;++ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+\d fkpart1+ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+\d fkpart1+DELETE FROM pkregular;+SELECT * FROM fkpartit;
--
2.11.0
From 96aa2fa7eb0554483dd960cd6e8964bbf07521b9 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 24 Jan 2018 13:02:38 -0300
Subject: [PATCH v2 5/5] don't error creating constraint triggers if internal
---
src/backend/commands/trigger.c | 8 ++++++--
1 file changed, 6 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 7cb709ea26..71a1fbeca5 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -238,9 +238,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));
/*
- * Constraint triggers are not allowed, either.+ * Constraint triggers are not allowed, either, except those+ * created internally. (This distinction is important because+ * internally-created triggers are expected to recurse creation+ * by themselves, while regular ones would have to be recursed+ * here, and that's not implemented yet.)
*/
- if (stmt->isconstraint)+ if (stmt->isconstraint && !isInternal)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("\"%s\" is a partitioned table",
--
2.11.0
Attachments:
[text/plain] v2-0001-Ignore-partitioned-indexes-in-get_relation_info.patch (1.0K, ../20180124175245.hsakxldr4cd74svv@alvherre.pgsql/2-v2-0001-Ignore-partitioned-indexes-in-get_relation_info.patch)
download | inline diff:
From 53506fd3a9f0cb81334024bf5a0e8856fd8e5e82 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 24 Jan 2018 14:40:26 -0300
Subject: [PATCH v2 1/5] Ignore partitioned indexes in get_relation_info
---
src/backend/optimizer/util/plancat.c | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 8c60b35068..60f21711f4 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -208,6 +208,16 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
}
/*
+ * Ignore partitioned indexes, since they are not usable for+ * queries.+ */+ if (indexRelation->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)+ {+ index_close(indexRelation, NoLock);+ continue;+ }++ /*
* If the index is valid, but cannot yet be used, ignore it; but
* mark the plan we are generating as transient. See
* src/backend/access/heap/README.HOT for discussion.
--
2.11.0
[text/plain] v2-0002-allow-indexes-on-partitioned-tables-to-be-unique.patch (62.7K, ../20180124175245.hsakxldr4cd74svv@alvherre.pgsql/3-v2-0002-allow-indexes-on-partitioned-tables-to-be-unique.patch)
download | inline diff:
From f67c07baa0a211a42e42b1480c4ffd47b0a7fb06 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 6 Nov 2017 17:04:55 +0100
Subject: [PATCH v2 2/5] allow indexes on partitioned tables to be unique
---
doc/src/sgml/ref/alter_table.sgml | 9 +-
doc/src/sgml/ref/create_table.sgml | 16 +-
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/index.c | 45 ++++-
src/backend/catalog/pg_constraint.c | 76 ++++++++
src/backend/catalog/toasting.c | 4 +-
src/backend/commands/indexcmds.c | 125 +++++++++++--
src/backend/commands/tablecmds.c | 62 ++++++-
src/backend/parser/analyze.c | 7 +
src/backend/parser/parse_utilcmd.c | 31 +---
src/backend/tcop/utility.c | 1 +
src/include/catalog/index.h | 5 +-
src/include/catalog/pg_constraint_fn.h | 4 +-
src/include/commands/defrem.h | 1 +
src/include/parser/parse_utilcmd.h | 3 +-
src/test/regress/expected/alter_table.out | 8 -
src/test/regress/expected/create_table.out | 12 --
src/test/regress/expected/indexing.out | 254 +++++++++++++++++++++++++-
src/test/regress/expected/insert_conflict.out | 2 +-
src/test/regress/sql/alter_table.sql | 2 -
src/test/regress/sql/create_table.sql | 8 -
src/test/regress/sql/indexing.sql | 151 ++++++++++++++-
22 files changed, 740 insertions(+), 88 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex 286c7a8589..c00fd09fe1 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -804,8 +804,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
This form attaches an existing table (which might itself be partitioned)
as a partition of the target table. The table can be attached
as a partition for specific values using <literal>FOR VALUES
- </literal> or as a default partition by using <literal>DEFAULT- </literal>. For each index in the target table, a corresponding+ </literal> or as a default partition by using+ <literal>DEFAULT</literal>.+ For each index in the target table, a corresponding
one will be created in the attached table; or, if an equivalent
index already exists, will be attached to the target table's index,
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
@@ -820,8 +821,10 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
as the target table and no more; moreover, the column types must also
match. Also, it must have all the <literal>NOT NULL</literal> and
<literal>CHECK</literal> constraints of the target table. Currently
- <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
<literal>FOREIGN KEY</literal> constraints are not considered.
+ <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints+ from the parent table will be created in the partition, if they don't+ already exist.
If any of the <literal>CHECK</literal> constraints of the table being
attached is marked <literal>NO INHERIT</literal>, the command will fail;
such a constraint must be recreated without the <literal>NO INHERIT</literal>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgmlindex a0c9a6d257..4c56df8960 100644--- a/doc/src/sgml/ref/create_table.sgml+++ b/doc/src/sgml/ref/create_table.sgml@@ -546,8 +546,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Partitioned tables do not support <literal>UNIQUE</literal>,- <literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or+ Partitioned tables do not support+ <literal>EXCLUDE</literal>, or
<literal>FOREIGN KEY</literal> constraints; however, you can define
these constraints on individual partitions.
</para>
@@ -786,6 +786,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
primary key constraint defined for the table. (Otherwise it
would just be the same constraint listed twice.)
</para>
++ <para>+ When used on partitioned tables, <literal>UNIQUE</literal> constraints+ must include all the columns of the partition key.+ </para>
</listitem>
</varlistentry>
@@ -814,6 +819,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
about the design of the schema, since a primary key implies that other
tables can rely on this set of columns as a unique identifier for rows.
</para>
++ <para>+ <literal>PRIMARY KEY</literal> constraints share the restrictions that+ <literal>UNIQUE</literal> constraints have when placed on partitioned+ tables.+ </para>+
</listitem>
</varlistentry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.yindex dfd53fa054..9e81f9514d 100644--- a/src/backend/bootstrap/bootparse.y+++ b/src/backend/bootstrap/bootparse.y@@ -322,6 +322,7 @@ Boot_DeclareIndexStmt:
stmt,
$4,
InvalidOid,
+ InvalidOid,
false,
false,
false,
@@ -367,6 +368,7 @@ Boot_DeclareUniqueIndexStmt:
stmt,
$5,
InvalidOid,
+ InvalidOid,
false,
false,
false,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 849a469127..1660711fb0 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -690,6 +690,8 @@ UpdateIndexRelation(Oid indexoid,
* nonzero to specify a preselected OID.
* parentIndexRelid: if creating an index partition, the OID of the
* parent index; otherwise InvalidOid.
+ * parentConstraintId: if creating a constraint on a partition, the OID+ * of the constraint in the parent; otherwise InvalidOid.
* relFileNode: normally, pass InvalidOid to get new storage. May be
* nonzero to attach an existing valid build.
* indexInfo: same info executor uses to insert into the index
@@ -721,6 +723,7 @@ UpdateIndexRelation(Oid indexoid,
* (only if INDEX_CREATE_ADD_CONSTRAINT is set)
* allow_system_table_mods: allow table to be a system catalog
* is_internal: if true, post creation hook for new index
+ * constraintId: if not NULL, receives OID of created constraint
*
* Returns the OID of the created index.
*/
@@ -729,6 +732,7 @@ index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -741,7 +745,8 @@ index_create(Relation heapRelation,
bits16 flags,
bits16 constr_flags,
bool allow_system_table_mods,
- bool is_internal)+ bool is_internal,+ Oid *constraintId)
{
Oid heapRelationId = RelationGetRelid(heapRelation);
Relation pg_class;
@@ -988,6 +993,7 @@ index_create(Relation heapRelation,
if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
{
char constraintType;
+ ObjectAddress localaddr;
if (isprimary)
constraintType = CONSTRAINT_PRIMARY;
@@ -1001,14 +1007,17 @@ index_create(Relation heapRelation,
constraintType = 0; /* keep compiler quiet */
}
- index_constraint_create(heapRelation,+ localaddr = index_constraint_create(heapRelation,
indexRelationId,
+ parentConstraintId,
indexInfo,
indexRelationName,
constraintType,
constr_flags,
allow_system_table_mods,
is_internal);
+ if (constraintId)+ *constraintId = localaddr.objectId;
}
else
{
@@ -1179,6 +1188,8 @@ index_create(Relation heapRelation,
*
* heapRelation: table owning the index (must be suitably locked by caller)
* indexRelationId: OID of the index
+ * parentConstraintId: if constraint is on a partition, the OID of the+ * constraint in the parent.
* indexInfo: same info executor uses to insert into the index
* constraintName: what it say (generally, should match name of index)
* constraintType: one of CONSTRAINT_PRIMARY, CONSTRAINT_UNIQUE, or
@@ -1196,6 +1207,7 @@ index_create(Relation heapRelation,
ObjectAddress
index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
@@ -1210,6 +1222,8 @@ index_constraint_create(Relation heapRelation,
bool deferrable;
bool initdeferred;
bool mark_as_primary;
+ bool islocal;+ int inhcount;
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
@@ -1244,6 +1258,17 @@ index_constraint_create(Relation heapRelation,
deleteDependencyRecordsForClass(RelationRelationId, indexRelationId,
RelationRelationId, DEPENDENCY_AUTO);
+ if (OidIsValid(parentConstraintId))+ {+ islocal = false;+ inhcount = 1;+ }+ else+ {+ islocal = true;+ inhcount = 0;+ }+
/*
* Construct a pg_constraint entry.
*/
@@ -1271,8 +1296,8 @@ index_constraint_create(Relation heapRelation,
NULL, /* no check constraint */
NULL,
NULL,
- true, /* islocal */- 0, /* inhcount */+ islocal,+ inhcount,
true, /* noinherit */
is_internal);
@@ -1293,6 +1318,18 @@ index_constraint_create(Relation heapRelation,
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
/*
+ * Also, if this is a constraint on a partition, mark it as depending+ * on the constraint in the parent.+ */+ if (OidIsValid(parentConstraintId))+ {+ ObjectAddress third;++ ObjectAddressSet(third, ConstraintRelationId, parentConstraintId);+ recordDependencyOn(&referenced, &third, DEPENDENCY_INTERNAL_AUTO);+ }++ /*
* If the constraint is deferrable, create the deferred uniqueness
* checking trigger. (The trigger will be given an internal dependency on
* the constraint by CreateTrigger.)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 442ae7e23d..731c5e4317 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -748,6 +748,43 @@ AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
}
/*
+ * ConstraintSetParentConstraint+ * Set a partition's constraint as child of its parent table's+ *+ * This updates the constraint's pg_constraint row to show it as inherited, and+ * add a dependency to the parent so that it cannot be removed on its own.+ */+void+ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)+{+ Relation constrRel;+ Form_pg_constraint constrForm;+ HeapTuple tuple,+ newtup;+ ObjectAddress depender;+ ObjectAddress referenced;++ constrRel = heap_open(ConstraintRelationId, RowExclusiveLock);+ tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(childConstrId));+ if (!HeapTupleIsValid(tuple))+ elog(ERROR, "cache lookup failed for constraint %u", childConstrId);+ newtup = heap_copytuple(tuple);+ constrForm = (Form_pg_constraint) GETSTRUCT(newtup);+ constrForm->conislocal = false;+ constrForm->coninhcount++;+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);+ ReleaseSysCache(tuple);++ ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);+ ObjectAddressSet(depender, ConstraintRelationId, childConstrId);++ recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);++ heap_close(constrRel, RowExclusiveLock);+}+++/*
* get_relation_constraint_oid
* Find a constraint on the specified relation with the specified name.
* Returns constraint's OID.
@@ -904,6 +941,45 @@ get_relation_constraint_attnos(Oid relid, const char *conname,
}
/*
+ * Return the OID of the constraint associated with the given index in the+ * given relation; or InvalidOid if no such index is catalogued.+ */+Oid+get_relation_idx_constraint_oid(Oid relationId, Oid indexId)+{+ Relation pg_constraint;+ SysScanDesc scan;+ ScanKeyData key;+ HeapTuple tuple;+ Oid constraintId = InvalidOid;++ pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);++ ScanKeyInit(&key,+ Anum_pg_constraint_conrelid,+ BTEqualStrategyNumber,+ F_OIDEQ,+ ObjectIdGetDatum(relationId));+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId,+ true, NULL, 1, &key);+ while ((tuple = systable_getnext(scan)) != NULL)+ {+ Form_pg_constraint constrForm;++ constrForm = (Form_pg_constraint) GETSTRUCT(tuple);+ if (constrForm->conindid == indexId)+ {+ constraintId = HeapTupleGetOid(tuple);+ break;+ }+ }+ systable_endscan(scan);++ heap_close(pg_constraint, AccessShareLock);+ return constraintId;+}++/*
* get_domain_constraint_oid
* Find a constraint on the specified domain with the specified name.
* Returns constraint's OID.
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.cindex cf37011b73..f4e7b83fee 100644--- a/src/backend/catalog/toasting.c+++ b/src/backend/catalog/toasting.c@@ -329,13 +329,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
coloptions[1] = 0;
index_create(toast_rel, toast_idxname, toastIndexOid, InvalidOid,
- InvalidOid,+ InvalidOid, InvalidOid,
indexInfo,
list_make2("chunk_id", "chunk_seq"),
BTREE_AM_OID,
rel->rd_rel->reltablespace,
collationObjectId, classObjectId, coloptions, (Datum) 0,
- INDEX_CREATE_IS_PRIMARY, 0, true, true);+ INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
heap_close(toast_rel, NoLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex a9461a4b06..b0e5ede488 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -25,6 +25,7 @@
#include "catalog/indexing.h"
#include "catalog/partition.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_constraint_fn.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_opclass.h"
@@ -301,6 +302,8 @@ CheckIndexCompatible(Oid oldId,
* nonzero to specify a preselected OID for the index.
* 'parentIndexId': the OID of the parent index; InvalidOid if not the child
* of a partitioned index.
+ * 'parentConstraintId': the OID of the parent constraint; InvalidOid if not+ * the child of a constraint (only used when recursing)
* 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
* 'check_rights': check for CREATE rights in namespace and tablespace. (This
* should be true except when ALTER is deleting/recreating an index.)
@@ -317,6 +320,7 @@ DefineIndex(Oid relationId,
IndexStmt *stmt,
Oid indexRelationId,
Oid parentIndexId,
+ Oid parentConstraintId,
bool is_alter_table,
bool check_rights,
bool check_not_in_use,
@@ -331,6 +335,7 @@ DefineIndex(Oid relationId,
Oid accessMethodId;
Oid namespaceId;
Oid tablespaceId;
+ Oid createdConstraintId = InvalidOid;
List *indexColNames;
Relation rel;
Relation indexRelation;
@@ -428,20 +433,11 @@ DefineIndex(Oid relationId,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot create index on partitioned table \"%s\" concurrently",
RelationGetRelationName(rel))));
- if (stmt->unique)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot create unique index on partitioned table \"%s\"",- RelationGetRelationName(rel))));
if (stmt->excludeOpNames)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
RelationGetRelationName(rel))));
- if (stmt->primary || stmt->isconstraint)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("cannot create constraints on partitioned tables")));
}
/*
@@ -639,6 +635,84 @@ DefineIndex(Oid relationId,
index_check_primary_key(rel, indexInfo, is_alter_table);
/*
+ * If this table is partitioned and we're creating a unique index or a+ * primary key, make sure that the indexed columns are part of the+ * partition key. Otherwise it would be possible to violate uniqueness by+ * putting values that ought to be unique in different partitions.+ *+ * We could lift this limitation if we had global indexes, but those have+ * their own problems, so this is a useful feature combination.+ */+ if (partitioned && (stmt->unique || stmt->primary))+ {+ PartitionKey key = rel->rd_partkey;+ int i;++ /*+ * A partitioned table can have unique indexes, as long as all the+ * columns in the partition key appear in the unique key. A+ * partition-local index can enforce global uniqueness iff the PK+ * value completely determines the partition that a row is in.+ *+ * Thus, verify that all the columns in the partition key appear+ * in the unique key definition.+ */+ for (i = 0; i < key->partnatts; i++)+ {+ bool found = false;+ int j;+ const char *constraint_type;++ if (stmt->primary)+ constraint_type = "PRIMARY KEY";+ else if (stmt->unique)+ constraint_type = "UNIQUE";+ else if (stmt->excludeOpNames != NIL)+ constraint_type = "EXCLUDE";+ else+ {+ elog(ERROR, "unknown constraint type");+ constraint_type = NULL; /* keep compiler quiet */+ }++ /*+ * It may be possible to support UNIQUE constraints when partition+ * keys are expressions, but is it worth it? Give up for now.+ */+ if (key->partattrs[i] == 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("unsupported %s constraint with partition key definition",+ constraint_type),+ errmsg("%s constraints cannot be used when partition keys include expressions.",+ constraint_type)));++ for (j = 0; j < indexInfo->ii_NumIndexAttrs; j++)+ {+ if (key->partattrs[i] == indexInfo->ii_KeyAttrNumbers[j])+ {+ found = true;+ break;+ }+ }+ if (!found)+ {+ Form_pg_attribute att;++ att = TupleDescAttr(RelationGetDescr(rel), key->partattrs[i] - 1);+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("insufficient columns in %s constraint definition",+ constraint_type),+ errdetail("%s constraint on table \"%s\" lacks column \"%s\" which is part of the partition key.",+ constraint_type, RelationGetRelationName(rel),+ NameStr(att->attname))));+ }+ }+ }+++ /*
* We disallow indexes on system columns other than OID. They would not
* necessarily get updated correctly, and they don't seem useful anyway.
*/
@@ -735,12 +809,14 @@ DefineIndex(Oid relationId,
indexRelationId =
index_create(rel, indexRelationName, indexRelationId, parentIndexId,
+ parentConstraintId,
stmt->oldNode, indexInfo, indexColNames,
accessMethodId, tablespaceId,
collationObjectId, classObjectId,
coloptions, reloptions,
flags, constr_flags,
- allowSystemTableMods, !check_rights);+ allowSystemTableMods, !check_rights,+ &createdConstraintId);
ObjectAddressSet(address, RelationRelationId, indexRelationId);
@@ -827,16 +903,40 @@ DefineIndex(Oid relationId,
opfamOids,
attmap, maplen))
{
+ Oid cldConstrOid = InvalidOid;+
/*
- * Found a match. Attach index to parent and we're- * done, but keep lock till commit.+ * Found a match.+ *+ * If this index is being created in the parent+ * because of a constraint, then the child needs to+ * have a constraint also, so look for one. If there+ * is no such constraint, this index is no good, so+ * keep looking.
*/
+ if (createdConstraintId != InvalidOid)+ {+ cldConstrOid =+ get_relation_idx_constraint_oid(childRelid,+ cldidxid);+ if (cldConstrOid == InvalidOid)+ {+ index_close(cldidx, lockmode);+ continue;+ }+ }++ /* Attach index to parent and we're done. */
IndexSetParentIndex(cldidx, indexRelationId);
+ if (createdConstraintId != InvalidOid)+ ConstraintSetParentConstraint(cldConstrOid,+ createdConstraintId);
if (!IndexIsValid(cldidx->rd_index))
invalidate_parent = true;
found = true;
+ /* keep lock till commit */
index_close(cldidx, NoLock);
break;
}
@@ -867,6 +967,7 @@ DefineIndex(Oid relationId,
DefineIndex(childRelid, childStmt,
InvalidOid, /* no predefined OID */
indexRelationId, /* this is our child */
+ createdConstraintId,
false, check_rights, check_not_in_use,
false, quiet);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 2e768dd5e4..5ba7971c43 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -939,17 +939,20 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
Relation idxRel = index_open(lfirst_oid(cell), AccessShareLock);
AttrNumber *attmap;
IndexStmt *idxstmt;
+ Oid constraintOid;
attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
RelationGetDescr(parent),
gettext_noop("could not convert row type"));
idxstmt =
generateClonedIndexStmt(NULL, RelationGetRelid(rel), idxRel,
- attmap, RelationGetDescr(rel)->natts);+ attmap, RelationGetDescr(rel)->natts,+ &constraintOid);
DefineIndex(RelationGetRelid(rel),
idxstmt,
InvalidOid,
RelationGetRelid(idxRel),
+ constraintOid,
false, false, false, false, false);
index_close(idxRel, AccessShareLock);
@@ -6809,6 +6812,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
stmt,
InvalidOid, /* no predefined OID */
InvalidOid, /* no parent index */
+ InvalidOid, /* no parent constraint */
true, /* is_alter_table */
check_rights,
false, /* check_not_in_use - we did it already */
@@ -6901,6 +6905,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
address = index_constraint_create(rel,
index_oid,
+ InvalidOid,
indexInfo,
constraintName,
constraintType,
@@ -14132,6 +14137,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
IndexInfo *info;
AttrNumber *attmap;
bool found = false;
+ Oid constraintOid;
/*
* Ignore indexes in the partitioned table other than partitioned
@@ -14148,6 +14154,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
attmap = convert_tuples_by_name_map(RelationGetDescr(attachrel),
RelationGetDescr(rel),
gettext_noop("could not convert row type"));
+ constraintOid = get_relation_idx_constraint_oid(RelationGetRelid(rel), idx);
/*
* Scan the list of existing indexes in the partition-to-be, and mark
@@ -14156,6 +14163,8 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
*/
for (i = 0; i < list_length(attachRelIdxs); i++)
{
+ Oid cldConstrOid;+
/* does this index have a parent? if so, can't use it */
if (has_superclass(RelationGetRelid(attachrelIdxRels[i])))
continue;
@@ -14168,8 +14177,26 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
attmap,
RelationGetDescr(rel)->natts))
{
+ /*+ * If this index is being created in the parent because of a+ * constraint, then the child needs to have a constraint also,+ * so look for one. If there is no such constraint, this+ * index is no good, so keep looking.+ */+ if (OidIsValid(constraintOid))+ {+ cldConstrOid =+ get_relation_idx_constraint_oid(RelationGetRelid(attachrel),+ RelationGetRelid(attachrelIdxRels[i]));+ /* no dice */+ if (!OidIsValid(cldConstrOid))+ continue;+ }+
/* bingo. */
IndexSetParentIndex(attachrelIdxRels[i], idx);
+ if (OidIsValid(constraintOid))+ ConstraintSetParentConstraint(cldConstrOid, constraintOid);
found = true;
break;
}
@@ -14182,12 +14209,15 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
if (!found)
{
IndexStmt *stmt;
+ Oid constraintOid;
stmt = generateClonedIndexStmt(NULL, RelationGetRelid(attachrel),
idxRel, attmap,
- RelationGetDescr(rel)->natts);+ RelationGetDescr(rel)->natts,+ &constraintOid);
DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid,
RelationGetRelid(idxRel),
+ constraintOid,
false, false, false, false, false);
}
@@ -14430,6 +14460,8 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
bool found;
int i;
PartitionDesc partDesc;
+ Oid constraintOid,+ cldConstrId;
/*
* If this partition already has an index attached, refuse the operation.
@@ -14485,8 +14517,34 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
RelationGetRelationName(parentIdx)),
errdetail("The index definitions do not match.")));
+ /*+ * If there is a constraint in the parent, make sure there is one+ * in the child too.+ */+ constraintOid = get_relation_idx_constraint_oid(RelationGetRelid(parentTbl),+ RelationGetRelid(parentIdx));++ if (OidIsValid(constraintOid))+ {+ cldConstrId = get_relation_idx_constraint_oid(RelationGetRelid(partTbl),+ partIdxId);+ if (!OidIsValid(cldConstrId))+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),+ errmsg("cannot attach index \"%s\" as a partition of index \"%s\"",+ RelationGetRelationName(partIdx),+ RelationGetRelationName(parentIdx)),+ errdetail("The index \"%s\" belongs to a constraint in table \"%s\" but no constraint exists for index \"%s\".",+ RelationGetRelationName(parentIdx),+ RelationGetRelationName(parentTbl),+ RelationGetRelationName(partIdx))));+ }+
/* All good -- do it */
IndexSetParentIndex(partIdx, RelationGetRelid(parentIdx));
+ if (OidIsValid(constraintOid))+ ConstraintSetParentConstraint(cldConstrId, constraintOid);+
pfree(attmap);
CommandCounterIncrement();
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.cindex e7b2bc7e73..5b3a610cf9 100644--- a/src/backend/parser/analyze.c+++ b/src/backend/parser/analyze.c@@ -1017,6 +1017,13 @@ transformOnConflictClause(ParseState *pstate,
TargetEntry *te;
int attno;
+ if (targetrel->rd_partdesc)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("%s cannot be applied to partitioned table \"%s\"",+ "ON CONFLICT DO UPDATE",+ RelationGetRelationName(targetrel))));+
/*
* All INSERT expressions have been parsed, get ready for potentially
* existing SET statements that need to be processed like an UPDATE.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex 5afb363096..a93fe11828 100644--- a/src/backend/parser/parse_utilcmd.c+++ b/src/backend/parser/parse_utilcmd.c@@ -704,12 +704,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("primary key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("primary key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
/* FALL THRU */
case CONSTR_UNIQUE:
@@ -719,12 +713,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("unique constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("unique constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
if (constraint->keys == NIL)
constraint->keys = list_make1(makeString(column->colname));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
@@ -821,12 +809,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("primary key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("primary key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
@@ -837,12 +819,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("unique constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("unique constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
@@ -1184,7 +1160,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
/* Build CREATE INDEX statement to recreate the parent_index */
index_stmt = generateClonedIndexStmt(cxt->relation, InvalidOid,
parent_index,
- attmap, tupleDesc->natts);+ attmap, tupleDesc->natts, NULL);
/* Copy comment on index, if requested */
if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS)
@@ -1267,7 +1243,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
*/
IndexStmt *
generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
- const AttrNumber *attmap, int attmap_length)+ const AttrNumber *attmap, int attmap_length, Oid *constraintOid)
{
Oid source_relid = RelationGetRelid(source_idx);
HeapTuple ht_idxrel;
@@ -1365,6 +1341,9 @@ generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
HeapTuple ht_constr;
Form_pg_constraint conrec;
+ if (constraintOid)+ *constraintOid = constraintId;+
ht_constr = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(constraintId));
if (!HeapTupleIsValid(ht_constr))
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 3abe7d6155..8c23ee53e2 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1353,6 +1353,7 @@ ProcessUtilitySlow(ParseState *pstate,
stmt,
InvalidOid, /* no predefined OID */
InvalidOid, /* no parent index */
+ InvalidOid, /* no parent constraint */
false, /* is_alter_table */
true, /* check_rights */
true, /* check_not_in_use */
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.hindex 235e180299..c8b6fe85c5 100644--- a/src/include/catalog/index.h+++ b/src/include/catalog/index.h@@ -54,6 +54,7 @@ extern Oid index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -66,7 +67,8 @@ extern Oid index_create(Relation heapRelation,
bits16 flags,
bits16 constr_flags,
bool allow_system_table_mods,
- bool is_internal);+ bool is_internal,+ Oid *constraintId);
#define INDEX_CONSTR_CREATE_MARK_AS_PRIMARY (1 << 0)
#define INDEX_CONSTR_CREATE_DEFERRABLE (1 << 1)
@@ -76,6 +78,7 @@ extern Oid index_create(Relation heapRelation,
extern ObjectAddress index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex 6bb1b09714..d3351f4a83 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -58,7 +58,6 @@ extern Oid CreateConstraintEntry(const char *constraintName,
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
-extern void SetValidatedConstraintById(Oid conId);
extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
Oid objNamespace, const char *conname);
@@ -68,10 +67,13 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
Oid newNspId, bool isType, ObjectAddresses *objsMoved);
+extern void ConstraintSetParentConstraint(Oid childConstrId,+ Oid parentConstrId);
extern Oid get_relation_constraint_oid(Oid relid, const char *conname, bool missing_ok);
extern Bitmapset *get_relation_constraint_attnos(Oid relid, const char *conname,
bool missing_ok, Oid *constraintOid);
extern Oid get_domain_constraint_oid(Oid typid, const char *conname, bool missing_ok);
+extern Oid get_relation_idx_constraint_oid(Oid relationId, Oid indexId);
extern Bitmapset *get_primary_key_attnos(Oid relid, bool deferrableOk,
Oid *constraintOid);
diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.hindex 7b824c95af..f510f40945 100644--- a/src/include/commands/defrem.h+++ b/src/include/commands/defrem.h@@ -26,6 +26,7 @@ extern ObjectAddress DefineIndex(Oid relationId,
IndexStmt *stmt,
Oid indexRelationId,
Oid parentIndexId,
+ Oid parentConstraintId,
bool is_alter_table,
bool check_rights,
bool check_not_in_use,
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.hindex 64aa8234e5..35ac97940a 100644--- a/src/include/parser/parse_utilcmd.h+++ b/src/include/parser/parse_utilcmd.h@@ -29,6 +29,7 @@ extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation
PartitionBoundSpec *spec);
extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel, Oid heapOid,
Relation source_idx,
- const AttrNumber *attmap, int attmap_length);+ const AttrNumber *attmap, int attmap_length,+ Oid *constraintOid);
#endif /* PARSE_UTILCMD_H */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex e9a1d37f6f..ccd2c38dbc 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -3305,14 +3305,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);-ERROR: unique constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD UNIQUE (a);- ^-ALTER TABLE partitioned ADD PRIMARY KEY (a);-ERROR: primary key constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD PRIMARY KEY (a);- ^
ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ERROR: foreign key constraints are not supported on partitioned tables
LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.outindex 8e745402ae..866cc99b9f 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -276,12 +276,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (- a int PRIMARY KEY-) PARTITION BY RANGE (a);-ERROR: primary key constraints are not supported on partitioned tables-LINE 2: a int PRIMARY KEY- ^
CREATE TABLE pkrel (
a int PRIMARY KEY
);
@@ -293,12 +287,6 @@ LINE 2: a int REFERENCES pkrel(a)
^
DROP TABLE pkrel;
CREATE TABLE partitioned (
- a int UNIQUE-) PARTITION BY RANGE (a);-ERROR: unique constraints are not supported on partitioned tables-LINE 2: a int UNIQUE- ^-CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
) PARTITION BY RANGE (a);
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.outindex ffd4b10c37..febd1b3162 100644--- a/src/test/regress/expected/indexing.out+++ b/src/test/regress/expected/indexing.out@@ -26,8 +26,6 @@ drop table idxpart;
-- Some unsupported features
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);-ERROR: cannot create unique index on partitioned table "idxpart"
create index concurrently on idxpart (a);
ERROR: cannot create index on partitioned table "idxpart" concurrently
drop table idxpart;
@@ -744,6 +742,256 @@ select attrelid::regclass, attname, attnum from pg_attribute
(7 rows)
drop table idxpart;
+--+-- Constraint-related indexes+--+-- Verify that it works to add primary key / unique to partitioned tables+create table idxpart (a int primary key, b int) partition by range (a);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | | +Partition key: RANGE (a)+Indexes:+ "idxpart_pkey" PRIMARY KEY, btree (a)+Number of partitions: 0++drop table idxpart;+-- but not if you fail to use the full partition key+create table idxpart (a int unique, b int) partition by range (a, b);+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.+create table idxpart (a int, b int unique) partition by range (a, b);+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "a" which is part of the partition key.+create table idxpart (a int primary key, b int) partition by range (b, a);+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.+create table idxpart (a int, b int primary key) partition by range (b, a);+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "a" which is part of the partition key.+-- OK if you use them in some other order+create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);+drop table idxpart;+create table idxpart (a int primary key, b int) partition by range ((b + a));+ERROR: unsupported PRIMARY KEY constraint with partition key definition+-- not other types of index-based constraints+create table idxpart (a int, exclude (a with = )) partition by range (a);+ERROR: exclusion constraints are not supported on partitioned tables+LINE 1: create table idxpart (a int, exclude (a with = )) partition ...+ ^+-- It works to add primary keys after the partitioned table is created+create table idxpart (a int, b int, c text) partition by range (a, b);+alter table idxpart add primary key (a); -- not an incomplete one tho+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "b" which is part of the partition key.+alter table idxpart add primary key (a, b);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | not null | + c | text | | | +Partition key: RANGE (a, b)+Indexes:+ "idxpart_pkey" PRIMARY KEY, btree (a, b)+Number of partitions: 0++create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);+\d idxpart1+ Table "public.idxpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | integer | | not null | + c | text | | | +Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)+Indexes:+ "idxpart1_pkey" PRIMARY KEY, btree (a, b)++drop table idxpart;+-- It works to add unique constraints after the partitioned table is created+create table idxpart (a int, b int) partition by range (a, b);+alter table idxpart add unique (a); -- ... nope+ERROR: insufficient columns in UNIQUE constraint definition+DETAIL: UNIQUE constraint on table "idxpart" lacks column "b" which is part of the partition key.+alter table idxpart add unique (b, a);+\d idxpart+ Table "public.idxpart"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | | + b | integer | | | +Partition key: RANGE (a, b)+Indexes:+ "idxpart_b_a_key" UNIQUE CONSTRAINT, btree (b, a)+Number of partitions: 0++drop table idxpart;+-- Exclusion constraints cannot be added+create table idxpart (a int, b int) partition by range (a);+alter table idxpart add exclude (a with =);+ERROR: exclusion constraints are not supported on partitioned tables+LINE 1: alter table idxpart add exclude (a with =);+ ^+drop table idxpart;+-- When (sub)partitions are created, they also contain the constraint+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)+ partition by range (b);+create table idxpart21 partition of idxpart2 for values from (10) to (15);+create table idxpart22 partition of idxpart2 for values from (15) to (20);+create table idxpart3 (b int not null, a int not null);+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+ conname | contype | conrelid | conindid | conkey +----------------+---------+-----------+----------------+--------+ idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}+ idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}+ idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}+(6 rows)++drop table idxpart;+-- Verify that multi-layer partitioning honors the requirement that all+-- columns in the partition key must appear in primary key+create table idxpart (a int, b int, primary key (a)) partition by range (a);+create table idxpart2 partition of idxpart+for values from (0) to (1000) partition by range (b); -- fail+ERROR: insufficient columns in PRIMARY KEY constraint definition+DETAIL: PRIMARY KEY constraint on table "idxpart2" lacks column "b" which is part of the partition key.+drop table idxpart;+-- Multi-layer partitioning works correctly in this case:+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);+create table idxpart21 partition of idxpart2 for values from (0) to (1000);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+ conname | contype | conrelid | conindid | conkey +----------------+---------+-----------+----------------+--------+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}+(3 rows)++drop table idxpart;+-- If a partitioned table has a unique/PK constraint, then it's not possible+-- to drop the corresponding constraint in the children; nor it's possible+-- to drop the indexes individually. Dropping the constraint in the parent+-- gets rid of the lot.+create table idxpart (i int) partition by hash (i);+create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0);+create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);+alter table idxpart0 add primary key(i);+alter table idxpart add primary key(i);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t+ idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(3 rows)++drop index idxpart0_pkey; -- fail+ERROR: cannot drop index idxpart0_pkey because index idxpart_pkey requires it+HINT: You can drop index idxpart_pkey instead.+drop index idxpart1_pkey; -- fail+ERROR: cannot drop index idxpart1_pkey because index idxpart_pkey requires it+HINT: You can drop index idxpart_pkey instead.+alter table idxpart0 drop constraint idxpart0_pkey; -- fail+ERROR: cannot drop inherited constraint "idxpart0_pkey" of relation "idxpart0"+alter table idxpart1 drop constraint idxpart1_pkey; -- fail+ERROR: cannot drop inherited constraint "idxpart1_pkey" of relation "idxpart1"+alter table idxpart drop constraint idxpart_pkey; -- ok+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+------------+-----------+------------+---------+------------+-------------+--------------+--------------+(0 rows)++drop table idxpart;+-- If a partitioned table has a constraint whose index is not valid,+-- attaching a missing partition makes it valid.+create table idxpart (a int) partition by range (a);+create table idxpart0 (like idxpart);+alter table idxpart0 add primary key (a);+alter table idxpart attach partition idxpart0 for values from (0) to (1000);+alter table only idxpart add primary key (a);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+-----------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | | t | idxpart0_pkey | t | 0 | t | t+ idxpart | idxpart_pkey | | f | idxpart_pkey | t | 0 | t | t+(2 rows)++alter index idxpart_pkey attach partition idxpart0_pkey;+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(2 rows)++drop table idxpart;+-- if a partition has a unique index without a constraint, does not attach+-- automatically; creates a new index instead.+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart add primary key (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit | convalidated +----------+----------------+--------------+------------+---------------+------------+-------------+--------------+--------------+ idxpart1 | idxpart1_a_idx | | t | | | | | + idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | t | t+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t+(3 rows)++drop table idxpart;+-- Can't attach an index without a corresponding constraint+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+alter table only idxpart add primary key (a);+alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail+ERROR: cannot attach index "idxpart1_a_idx" as a partition of index "idxpart_pkey"+DETAIL: The index "idxpart_pkey" belongs to a constraint in table "idxpart" but no constraint exists for index "idxpart1_a_idx".+drop table idxpart;
-- intentionally leave some objects around
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
@@ -755,3 +1003,5 @@ create index on idxpart22 (a);
create index on only idxpart2 (a);
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
create index on idxpart (a);
+create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart_another_1 partition of idxpart_another for values from (0) to (100);diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.outindex 8fd2027d6a..2650faedee 100644--- a/src/test/regress/expected/insert_conflict.out+++ b/src/test/regress/expected/insert_conflict.out@@ -794,7 +794,7 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing;
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
-- however, on conflict do update is not supported yet
insert into parted_conflict_test values (1) on conflict (b) do update set a = excluded.a;
-ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification+ERROR: ON CONFLICT DO UPDATE cannot be applied to partitioned table "parted_conflict_test"
-- but it works OK if we target the partition directly
insert into parted_conflict_test_1 values (1) on conflict (b) do
update set a = excluded.a;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b27e8f6777..b73f523e8a 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -2035,8 +2035,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD UNIQUE (a);-ALTER TABLE partitioned ADD PRIMARY KEY (a);
ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sqlindex 8f9991ef18..fefccf21a2 100644--- a/src/test/regress/sql/create_table.sql+++ b/src/test/regress/sql/create_table.sql@@ -294,10 +294,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
-- unsupported constraint type for partitioned tables
-CREATE TABLE partitioned (- a int PRIMARY KEY-) PARTITION BY RANGE (a);-
CREATE TABLE pkrel (
a int PRIMARY KEY
);
@@ -307,10 +303,6 @@ CREATE TABLE partitioned (
DROP TABLE pkrel;
CREATE TABLE partitioned (
- a int UNIQUE-) PARTITION BY RANGE (a);--CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
) PARTITION BY RANGE (a);
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sqlindex 2f985ec866..439d19c621 100644--- a/src/test/regress/sql/indexing.sql+++ b/src/test/regress/sql/indexing.sql@@ -15,7 +15,6 @@ drop table idxpart;
-- Some unsupported features
create table idxpart (a int, b int, c text) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
-create unique index on idxpart (a);
create index concurrently on idxpart (a);
drop table idxpart;
@@ -375,6 +374,154 @@ select attrelid::regclass, attname, attnum from pg_attribute
order by attrelid::regclass, attnum;
drop table idxpart;
+--+-- Constraint-related indexes+--++-- Verify that it works to add primary key / unique to partitioned tables+create table idxpart (a int primary key, b int) partition by range (a);+\d idxpart+drop table idxpart;++-- but not if you fail to use the full partition key+create table idxpart (a int unique, b int) partition by range (a, b);+create table idxpart (a int, b int unique) partition by range (a, b);+create table idxpart (a int primary key, b int) partition by range (b, a);+create table idxpart (a int, b int primary key) partition by range (b, a);++-- OK if you use them in some other order+create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);+drop table idxpart;++create table idxpart (a int primary key, b int) partition by range ((b + a));+-- not other types of index-based constraints+create table idxpart (a int, exclude (a with = )) partition by range (a);++-- It works to add primary keys after the partitioned table is created+create table idxpart (a int, b int, c text) partition by range (a, b);+alter table idxpart add primary key (a); -- not an incomplete one tho+alter table idxpart add primary key (a, b);+\d idxpart+create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000);+\d idxpart1+drop table idxpart;++-- It works to add unique constraints after the partitioned table is created+create table idxpart (a int, b int) partition by range (a, b);+alter table idxpart add unique (a); -- ... nope+alter table idxpart add unique (b, a);+\d idxpart+drop table idxpart;++-- Exclusion constraints cannot be added+create table idxpart (a int, b int) partition by range (a);+alter table idxpart add exclude (a with =);+drop table idxpart;++-- When (sub)partitions are created, they also contain the constraint+create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);+create table idxpart1 partition of idxpart for values from (1, 1) to (10, 10);+create table idxpart2 partition of idxpart for values from (10, 10) to (20, 20)+ partition by range (b);+create table idxpart21 partition of idxpart2 for values from (10) to (15);+create table idxpart22 partition of idxpart2 for values from (15) to (20);+create table idxpart3 (b int not null, a int not null);+alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+drop table idxpart;++-- Verify that multi-layer partitioning honors the requirement that all+-- columns in the partition key must appear in primary key+create table idxpart (a int, b int, primary key (a)) partition by range (a);+create table idxpart2 partition of idxpart+for values from (0) to (1000) partition by range (b); -- fail+drop table idxpart;++-- Multi-layer partitioning works correctly in this case:+create table idxpart (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart2 partition of idxpart for values from (0) to (1000) partition by range (b);+create table idxpart21 partition of idxpart2 for values from (0) to (1000);+select conname, contype, conrelid::regclass, conindid::regclass, conkey+ from pg_constraint where conrelid::regclass::text like 'idxpart%'+ order by conname;+drop table idxpart;++-- If a partitioned table has a unique/PK constraint, then it's not possible+-- to drop the corresponding constraint in the children; nor it's possible+-- to drop the indexes individually. Dropping the constraint in the parent+-- gets rid of the lot.+create table idxpart (i int) partition by hash (i);+create table idxpart0 partition of idxpart (i) for values with (modulus 2, remainder 0);+create table idxpart1 partition of idxpart (i) for values with (modulus 2, remainder 1);+alter table idxpart0 add primary key(i);+alter table idxpart add primary key(i);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop index idxpart0_pkey; -- fail+drop index idxpart1_pkey; -- fail+alter table idxpart0 drop constraint idxpart0_pkey; -- fail+alter table idxpart1 drop constraint idxpart1_pkey; -- fail+alter table idxpart drop constraint idxpart_pkey; -- ok+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- If a partitioned table has a constraint whose index is not valid,+-- attaching a missing partition makes it valid.+create table idxpart (a int) partition by range (a);+create table idxpart0 (like idxpart);+alter table idxpart0 add primary key (a);+alter table idxpart attach partition idxpart0 for values from (0) to (1000);+alter table only idxpart add primary key (a);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+alter index idxpart_pkey attach partition idxpart0_pkey;+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- if a partition has a unique index without a constraint, does not attach+-- automatically; creates a new index instead.+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart add primary key (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,+ conname, conislocal, coninhcount, connoinherit, convalidated+ from pg_index idx left join pg_inherits inh on (idx.indexrelid = inh.inhrelid)+ left join pg_constraint con on (idx.indexrelid = con.conindid)+ where indrelid::regclass::text like 'idxpart%'+ order by indexrelid::regclass::text collate "C";+drop table idxpart;++-- Can't attach an index without a corresponding constraint+create table idxpart (a int, b int) partition by range (a);+create table idxpart1 (a int not null, b int);+create unique index on idxpart1 (a);+alter table idxpart attach partition idxpart1 for values from (1) to (1000);+alter table only idxpart add primary key (a);+alter index idxpart_pkey attach partition idxpart1_a_idx; -- fail+drop table idxpart;+
-- intentionally leave some objects around
create table idxpart (a int) partition by range (a);
create table idxpart1 partition of idxpart for values from (0) to (100);
@@ -386,3 +533,5 @@ create index on idxpart22 (a);
create index on only idxpart2 (a);
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
create index on idxpart (a);
+create table idxpart_another (a int, b int, primary key (a, b)) partition by range (a);+create table idxpart_another_1 partition of idxpart_another for values from (0) to (100);--
2.11.0
[text/plain] v2-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch (32.2K, ../20180124175245.hsakxldr4cd74svv@alvherre.pgsql/4-v2-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch)
download | inline diff:
From 2e150936a0acdbb0b959673a047eac8a0a10e80e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v2 3/5] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/index.c | 3 +-
src/backend/commands/tablecmds.c | 87 ++++++++++++++++++++--
src/backend/commands/trigger.c | 124 ++++++++++++++++++++++++++++---
src/backend/tcop/utility.c | 3 +-
src/include/commands/trigger.h | 2 +-
src/test/regress/expected/triggers.out | 130 ++++++++++++++++++++++++++++++---
src/test/regress/sql/triggers.sql | 87 +++++++++++++++++++---
7 files changed, 393 insertions(+), 43 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 1660711fb0..e126f1f19b 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -1356,7 +1356,8 @@ index_constraint_create(Relation heapRelation,
trigger->constrrel = NULL;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
- InvalidOid, conOid, indexRelationId, true);+ InvalidOid, conOid, indexRelationId, InvalidOid,+ InvalidOid, true);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 5ba7971c43..9ca88894de 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -487,6 +487,7 @@ static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
List *scanrel_children,
List *partConstraint,
bool validate_default);
+static void CloneRowTriggersOnPartition(Oid parentId, Oid partitionId);
static ObjectAddress ATExecDetachPartition(Relation rel, RangeVar *name);
static ObjectAddress ATExecAttachPartitionIdx(List **wqueue, Relation rel,
RangeVar *name);
@@ -916,9 +917,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes defined in- * the parent. We can't do it earlier, because DefineIndex wants to know- * the partition key which we just stored.+ * If we're creating a partition, create now all the indexes and triggers+ * defined in the parent.+ *+ * We can't do it earlier, because DefineIndex wants to know the partition+ * key which we just stored.
*/
if (stmt->partbound)
{
@@ -959,6 +962,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
list_free(idxlist);
++ /*+ * If there are any triggers, clone the appropriate ones to the new+ * partition.+ */+ if (parent->trigdesc != NULL)+ CloneRowTriggersOnPartition(RelationGetRelid(parent), relationId);+
heap_close(parent, NoLock);
}
@@ -8431,7 +8442,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8505,7 +8516,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8560,7 +8571,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -14015,6 +14026,8 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* Ensure there exists a correct set of indexes in the partition. */
AttachPartitionEnsureIndexes(rel, attachrel);
+ /* and triggers */+ CloneRowTriggersOnPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));
/*
* Generate partition constraint from the partition bound specification.
@@ -14224,6 +14237,9 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
index_close(idxRel, AccessShareLock);
}
+ /* Make this all visible */+ CommandCounterIncrement();+
/* Clean up. */
for (i = 0; i < list_length(attachRelIdxs); i++)
index_close(attachrelIdxRels[i], AccessShareLock);
@@ -14232,6 +14248,65 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
}
/*
+ * CloneRowTriggersOnPartition+ * subroutine for ATExecAttachPartition/DefineRelation to create row+ * triggers on partitions+ */+static void+CloneRowTriggersOnPartition(Oid parentId, Oid partitionId)+{+ Relation pg_trigger;+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key, Anum_pg_trigger_tgrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ pg_trigger = heap_open(TriggerRelationId, RowExclusiveLock);+ scan = systable_beginscan(pg_trigger, TriggerRelidNameIndexId,+ true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ {+ Form_pg_trigger trigForm;+ CreateTrigStmt *trigStmt;++ trigForm = (Form_pg_trigger) GETSTRUCT(tuple);+ if (!TRIGGER_FOR_ROW(trigForm->tgtype) ||+ TRIGGER_FOR_BEFORE(trigForm->tgtype) ||+ TRIGGER_FOR_INSTEAD(trigForm->tgtype) ||+ OidIsValid(trigForm->tgconstraint))+ continue;++ trigStmt = makeNode(CreateTrigStmt);++ trigStmt->trigname = NameStr(trigForm->tgname);+ trigStmt->relation = NULL;+ trigStmt->funcname = NULL;+ trigStmt->args = NULL;+ trigStmt->row = true;+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;+ trigStmt->columns = NIL;+ trigStmt->whenClause = NULL;+ trigStmt->isconstraint = false;+ trigStmt->transitionRels = NIL;+ trigStmt->deferrable = trigForm->tgdeferrable;+ trigStmt->initdeferred = trigForm->tginitdeferred;+ trigStmt->constrrel = NULL;++ CreateTrigger(trigStmt, NULL, partitionId,+ InvalidOid, InvalidOid, InvalidOid,+ trigForm->tgfoid, HeapTupleGetOid(tuple), false);+ pfree(trigStmt);+ }++ systable_endscan(scan);++ heap_close(pg_trigger, RowExclusiveLock);+}++/*
* ALTER TABLE DETACH PARTITION
*
* Return the address of the relation that is no longer a partition of rel.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 160d941c00..7cb709ea26 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -125,6 +125,12 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* indexOid, if nonzero, is the OID of an index associated with the constraint.
* We do nothing with this except store it into pg_trigger.tgconstrindid.
*
+ * funcoid, if nonzero, is the OID of the function to invoke. When this is+ * given, stmt->funcname is ignored.+ *+ * parentTriggerOid, if nonzero, is a trigger that begets this one; so that+ * if that trigger is dropped, this one should be too.+ *
* If isInternal is true then this is an internally-generated trigger.
* This argument sets the tgisinternal field of the pg_trigger entry, and
* if true causes us to modify the given trigger name to ensure uniqueness.
@@ -133,6 +139,10 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, this function recurses to create the+ * trigger on all the partitions, except if isInternal is true, in which+ * case caller is expected to execute recursion on its own.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -140,7 +150,7 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal)+ Oid funcoid, Oid parentTriggerOid, bool isInternal)
{
int16 tgtype;
int ncolumns;
@@ -159,7 +169,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Relation pgrel;
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
- Oid funcoid;
Oid funcrettype;
Oid trigoid;
char internaltrigname[NAMEDATALEN];
@@ -179,8 +188,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +198,69 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * Disallow WHEN clauses; I think it's okay, but disallow for now+ * to reduce testing surface.+ */+ if (stmt->whenClause)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.")));++ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));++ /*+ * Constraint triggers are not allowed, either.+ */+ if (stmt->isconstraint)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.")));++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -587,7 +651,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Find and validate the trigger function.
*/
- funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);+ if (!OidIsValid(funcoid))+ funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);
if (!isInternal)
{
aclresult = pg_proc_aclcheck(funcoid, GetUserId(), ACL_EXECUTE);
@@ -928,11 +993,18 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* User CREATE TRIGGER, so place dependencies. We make trigger be
* auto-dropped if its relation is dropped or if the FK relation is
* dropped. (Auto drop is compatible with our pre-7.3 behavior.)
+ *+ * Exception: if this trigger comes from a parent partitioned table,+ * then it's not separately drop-able, but goes away if the partition+ * does.
*/
referenced.classId = RelationRelationId;
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
- recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);+ recordDependencyOn(&myself, &referenced, OidIsValid(parentTriggerOid) ?+ DEPENDENCY_INTERNAL_AUTO :+ DEPENDENCY_AUTO);+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -954,6 +1026,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectSubId = 0;
recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL);
}
++ /* Depends on the parent trigger, if there is one. */+ if (OidIsValid(parentTriggerOid))+ {+ ObjectAddressSet(referenced, TriggerRelationId, parentTriggerOid);+ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL_AUTO);+ }
}
/* If column-specific trigger, add normal dependencies on columns */
@@ -982,6 +1061,31 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
+ /*+ * If this is a FOR EACH ROW trigger on a partitioned table, recurse for+ * each partition if invoked directly by user (otherwise, caller must do+ * its own recursion).+ */+ if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ !isInternal)+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ int i;++ for (i = 0; i < partdesc->nparts; i++)+ {+ /* XXX must create a separate constraint for each child */+ Assert(constraintOid == InvalidOid);+ /* XXX must create a separate index for each child */+ Assert(indexOid == InvalidOid);++ CreateTrigger(copyObject(stmt), queryString,+ partdesc->oids[i], refRelOid,+ constraintOid, indexOid,+ InvalidOid, trigoid, isInternal);+ }+ }+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex 8c23ee53e2..f60f31249a 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1507,7 +1507,8 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, false);+ InvalidOid, InvalidOid, InvalidOid,+ InvalidOid, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.hindex ff5546cf28..3970ab06b4 100644--- a/src/include/commands/trigger.h+++ b/src/include/commands/trigger.h@@ -159,7 +159,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal);+ Oid funcid, Oid parentTriggerOid, bool isInternal);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex 9a7aafcc96..54116064b7 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1815,7 +1815,80 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart2 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(4 rows)++drop trigger f on trigpart1; -- fail+ERROR: cannot drop trigger f on table trigpart1 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart2; -- fail+ERROR: cannot drop trigger f on table trigpart2 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart3; -- fail+ERROR: cannot drop trigger f on table trigpart3 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(3 rows)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1832,7 +1905,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1845,27 +1918,51 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
@@ -1874,6 +1971,8 @@ NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted2_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
@@ -1889,21 +1988,28 @@ NOTICE: trigger on parted_stmt_trig BEFORE UPDATE for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE for ROW
NOTICE: trigger on parted2_stmt_trig BEFORE UPDATE for STATEMENT
NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger on parted_stmt_trig AFTER UPDATE for STATEMENT
NOTICE: trigger on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
drop table parted_stmt_trig, parted2_stmt_trig;
--
-- Test the interaction between transition tables and both kinds of
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex 47b5bde390..8dee659757 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1292,7 +1292,50 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each statement execute procedure trigger_nothing();+create constraint trigger failed after insert on parted_trig+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart1; -- fail+drop trigger f on trigpart2; -- fail+drop trigger f on trigpart3; -- fail+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1312,7 +1355,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1326,28 +1369,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
--
2.11.0
[text/plain] v2-0004-WIP-Allow-foreign-key-triggers-on-partitioned-tab.patch (38.6K, ../20180124175245.hsakxldr4cd74svv@alvherre.pgsql/5-v2-0004-WIP-Allow-foreign-key-triggers-on-partitioned-tab.patch)
download | inline diff:
From f5dc4e06948dcd35d50dd2585f67c27fd74f5841 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:54:14 -0300
Subject: [PATCH v2 4/5] [WIP] Allow foreign key triggers on partitioned tables
---
src/backend/catalog/pg_constraint.c | 192 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 109 +++++++++++++---
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 50 +++-----
src/include/catalog/pg_constraint_fn.h | 2 +
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 115 ++++++++++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 67 ++++++++++
src/test/regress/sql/alter_table.sql | 57 ++++++++-
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/foreign_key.sql | 28 +++++
12 files changed, 568 insertions(+), 86 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 731c5e4317..bfa580cc0a 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -26,6 +26,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "commands/tablecmds.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
@@ -375,6 +376,197 @@ CreateConstraintEntry(const char *constraintName,
return conOid;
}
+/*+ * For each foreign key constraint in relation parentId, create a cloned+ * copy of it for relationId.+ *+ * relationId is a partition of parentId, so we can be certain that it has+ * the same columns with the same datatypes. They may be in different order,+ * though.+ */+void+CloneForeignKeyConstraints(Oid parentId, Oid relationId)+{+ Relation pg_constraint;+ Relation rel;+ ScanKeyData key;+ SysScanDesc scan;+ TupleDesc tupdesc;+ HeapTuple tuple;++ /* see ATAddForeignKeyConstraint about lock level */+ rel = heap_open(relationId, AccessExclusiveLock);++ pg_constraint = heap_open(ConstraintRelationId, RowShareLock);+ tupdesc = RelationGetDescr(pg_constraint);++ ScanKeyInit(&key,+ Anum_pg_constraint_conrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,+ NULL, 1, &key);++ while ((tuple = systable_getnext(scan)) != NULL)+ {+ Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);+ AttrNumber conkey[INDEX_MAX_KEYS];+ AttrNumber confkey[INDEX_MAX_KEYS];+ Oid conpfeqop[INDEX_MAX_KEYS];+ Oid conppeqop[INDEX_MAX_KEYS];+ Oid conffeqop[INDEX_MAX_KEYS];+ Constraint *fkconstraint;+ Oid constrOid;+ ObjectAddress parentAddr,+ childAddr;+ int nelem;+ ArrayType *arr;+ Datum datum;+ bool isnull;++ /* only foreign keys */+ if (constrForm->contype != CONSTRAINT_FOREIGN)+ continue;++ ObjectAddressSet(parentAddr, ConstraintRelationId,+ HeapTupleGetOid(tuple));++ datum = fastgetattr(tuple, Anum_pg_constraint_conkey,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conkey");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != INT2OID)+ elog(ERROR, "conkey is not a 1-D smallint array");+ memcpy(conkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));++ datum = fastgetattr(tuple, Anum_pg_constraint_confkey,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null confkey");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != INT2OID)+ elog(ERROR, "confkey is not a 1-D smallint array");+ memcpy(confkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));++ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conpfeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conpfeqop is not a 1-D OID array");+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conpfeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conpfeqop is not a 1-D OID array");+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conppeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conppeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conppeqop is not a 1-D OID array");+ memcpy(conppeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ datum = fastgetattr(tuple, Anum_pg_constraint_conffeqop,+ tupdesc, &isnull);+ if (isnull)+ elog(ERROR, "null conffeqop");+ arr = DatumGetArrayTypeP(datum);+ nelem = ARR_DIMS(arr)[0];+ if (ARR_NDIM(arr) != 1 ||+ nelem < 1 ||+ nelem > INDEX_MAX_KEYS ||+ ARR_HASNULL(arr) ||+ ARR_ELEMTYPE(arr) != OIDOID)+ elog(ERROR, "conffeqop is not a 1-D OID array");+ memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));++ constrOid =+ CreateConstraintEntry(NameStr(constrForm->conname),+ constrForm->connamespace,+ CONSTRAINT_FOREIGN,+ constrForm->condeferrable,+ constrForm->condeferred,+ constrForm->convalidated,+ relationId,+ conkey,+ nelem,+ InvalidOid, /* not a domain constraint */+ constrForm->conindid, /* same index */+ constrForm->confrelid, /* same foreign rel */+ confkey,+ conpfeqop,+ conppeqop,+ conffeqop,+ nelem,+ constrForm->confupdtype,+ constrForm->confdeltype,+ constrForm->confmatchtype,+ NULL,+ NULL,+ NULL,+ NULL,+ false,+ 1, false, true);++ ObjectAddressSet(childAddr, ConstraintRelationId, constrOid);+ recordDependencyOn(&childAddr, &parentAddr, DEPENDENCY_INTERNAL);++ fkconstraint = makeNode(Constraint);+ /* for now this is all we need */+ fkconstraint->fk_upd_action = constrForm->confupdtype;+ fkconstraint->fk_del_action = constrForm->confdeltype;+ fkconstraint->deferrable = constrForm->condeferrable;+ fkconstraint->initdeferred = constrForm->condeferred;++ createForeignKeyTriggers(rel, constrForm->confrelid, fkconstraint,+ constrOid, constrForm->conindid);++ /*+ * XXX Normal constraint creation can be invoked during ALTER and+ * so it needs ALTER TABLE's phase 3 checking. Current caller is just+ * CREATE TABLE .. PARTITION OF so we don't need it, but maybe for+ * ALTER TABLE .. ATTACH PARTITION we'll need it.+ */+ }+ systable_endscan(scan);++ heap_close(rel, NoLock); /* keep lock till commit */+ heap_close(pg_constraint, RowShareLock);+}
/*
* Test whether given name is currently used as a constraint name
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 9ca88894de..25fd76c3f0 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -338,9 +338,6 @@ static void validateCheckConstraint(Relation rel, HeapTuple constrtup);
static void validateForeignKeyConstraint(char *conname,
Relation rel, Relation pkrel,
Oid pkindOid, Oid constraintOid);
-static void createForeignKeyTriggers(Relation rel, Oid refRelOid,- Constraint *fkconstraint,- Oid constraintOid, Oid indexOid);
static void ATController(AlterTableStmt *parsetree,
Relation rel, List *cmds, bool recurse, LOCKMODE lockmode);
static void ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
@@ -411,8 +408,10 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue,
Constraint *constr,
bool recurse, bool recursing, bool is_readd,
LOCKMODE lockmode);
-static ObjectAddress ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,- Constraint *fkconstraint, LOCKMODE lockmode);+static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab,+ Relation rel,+ Constraint *fkconstraint, bool recurse, bool recursing,+ LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -505,6 +504,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
* relkind: relkind to assign to the new relation
* ownerId: if not InvalidOid, use this as the new relation's owner.
* typaddress: if not null, it's set to the pg_type entry's address.
+ * queryString: for error reporting
*
* Note that permissions checks are done against current user regardless of
* ownerId. A nonzero ownerId is used when someone is creating a relation
@@ -917,8 +917,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes and triggers- * defined in the parent.+ * If we're creating a partition, create now all the indexes, triggers,+ * FKs defined in the parent.
*
* We can't do it earlier, because DefineIndex wants to know the partition
* key which we just stored.
@@ -970,6 +970,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (parent->trigdesc != NULL)
CloneRowTriggersOnPartition(RelationGetRelid(parent), relationId);
+ /* And foreign keys too */+ CloneForeignKeyConstraints(parentId, relationId);+
heap_close(parent, NoLock);
}
@@ -6986,7 +6989,8 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
RelationGetNamespace(rel),
NIL);
- address = ATAddForeignKeyConstraint(tab, rel, newConstraint,+ address = ATAddForeignKeyConstraint(wqueue, tab, rel,+ newConstraint, recurse, false,
lockmode);
break;
@@ -7141,8 +7145,9 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* We do permissions checks here, however.
*/
static ObjectAddress
-ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,- Constraint *fkconstraint, LOCKMODE lockmode)+ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,+ Constraint *fkconstraint, bool recurse,+ bool recursing, LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7176,12 +7181,22 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
* numbers)
*/
if (pkrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("cannot reference partitioned table \"%s\"",- RelationGetRelationName(pkrel))));+ {+ if (!recurse)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("foreign key referencing partitioned table \"%s\" must not be ONLY",+ RelationGetRelationName(pkrel))));+ /* fix recursion in ATExecValidateConstraint to enable this case */+ if (fkconstraint->skip_validation && !fkconstraint->initially_valid)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("cannot add NOT VALID foreign key to relation \"%s\"",+ RelationGetRelationName(pkrel))));+ }- if (pkrel->rd_rel->relkind != RELKIND_RELATION)+ if (pkrel->rd_rel->relkind != RELKIND_RELATION &&+ pkrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("referenced relation \"%s\" is not a table",
@@ -7541,6 +7556,45 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
}
/*
+ * If this is a partitioned table, recurse to create the constraint on the+ * partitions also.+ */+ if (recurse)+ {+ List *children;+ ListCell *child;++ /* XXX why not find_all_inheritors? */+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);++ foreach(child, children)+ {+ Oid childrelid = lfirst_oid(child);+ Relation childrel;+ AlteredTableInfo *childtab;+ ObjectAddress childAddr;++ /* find_inheritance_children already got lock */+ childrel = heap_open(childrelid, NoLock);+ CheckTableNotInUse(childrel, "ALTER TABLE"); /* XXX do we need this? */++ /* Find or create work queue entry for this table */+ childtab = ATGetQueueEntry(wqueue, childrel);++ /* Recurse to child */+ childAddr =+ ATAddForeignKeyConstraint(wqueue, childtab, childrel,+ fkconstraint, recurse, true,+ lockmode);++ /* make sure they go away together, or not at all */+ recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL);++ heap_close(childrel, NoLock);+ }+ }++ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -7802,8 +7856,8 @@ ATExecValidateConstraint(Relation rel, char *constrName, bool recurse,
heap_close(refrel, NoLock);
/*
- * Foreign keys do not inherit, so we purposely ignore the- * recursion bit here+ * We disallow creating invalid foreign keys to or from+ * partitioned tables, so ignoring the recursion bit is okay.
*/
}
else if (con->contype == CONSTRAINT_CHECK)
@@ -8454,7 +8508,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
* NB: if you change any trigger properties here, see also
* ATExecAlterConstraint.
*/
-static void+void
createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
Oid constraintOid, Oid indexOid)
{
@@ -8584,6 +8638,25 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
indexOid, true);
CreateFKCheckTrigger(myRelOid, refRelOid, fkconstraint, constraintOid,
indexOid, false);
++ /*+ * If this is a partitioned table, recurse to create triggers for each+ * child. We consider that one pg_constraint entry is enough; we only+ * need the triggers to appear per-partition.+ */+ if (get_rel_relkind(refRelOid) == RELKIND_PARTITIONED_TABLE)+ {+ ListCell *cell;+ List *dchildren;++ /* XXX maybe we need a stronger lock? */+ dchildren = find_inheritance_children(refRelOid, RowShareLock);+ foreach(cell, dchildren)+ {+ createForeignKeyTriggers(rel, lfirst_oid(cell), fkconstraint,+ constraintOid, indexOid);+ }+ }
}
/*
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex a93fe11828..f84b05c544 100644--- a/src/backend/parser/parse_utilcmd.c+++ b/src/backend/parser/parse_utilcmd.c@@ -730,12 +730,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
errmsg("foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("foreign key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
/*
* Fill in the current attribute's name and throw it into the
@@ -849,12 +843,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("foreign key constraints are not supported on partitioned tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->fkconstraints = lappend(cxt->fkconstraints, constraint);
break;
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.cindex 8faae1d069..b3dd174d28 100644--- a/src/backend/utils/adt/ri_triggers.c+++ b/src/backend/utils/adt/ri_triggers.c@@ -401,7 +401,7 @@ RI_FKey_check(TriggerData *trigdata)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding FK attributes.
@@ -409,7 +409,7 @@ RI_FKey_check(TriggerData *trigdata)
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -537,7 +537,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...]+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* PK attributes themselves.
@@ -545,7 +545,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x", pkrelname);+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -793,7 +793,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> x WHERE $1 = fkatt1 [AND ...]+ * SELECT 1 FROM <fktable> x WHERE $1 = fkatt1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -801,7 +801,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -951,14 +951,14 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * DELETE FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]+ * DELETE FROM <fktable> WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "DELETE FROM ONLY %s", fkrelname);+ appendStringInfo(&querybuf, "DELETE FROM %s", fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -1122,7 +1122,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]+ * UPDATE <fktable> SET fkatt1 = $1 [, ...]
* WHERE $n = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes. Note that we are assuming
@@ -1133,7 +1133,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
@@ -1342,7 +1342,7 @@ ri_setnull(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]+ * UPDATE <fktable> SET fkatt1 = NULL [, ...]
* WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -1351,7 +1351,7 @@ ri_setnull(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1559,7 +1559,7 @@ ri_setdefault(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]+ * UPDATE <fktable> SET fkatt1 = DEFAULT [, ...]
* WHERE $1 = fkatt1 [AND ...]
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
@@ -1568,7 +1568,7 @@ ri_setdefault(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);+ appendStringInfo(&querybuf, "UPDATE %s SET", fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1895,8 +1895,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
/*----------
* The query string built is:
- * SELECT fk.keycols FROM ONLY relname fk- * LEFT OUTER JOIN ONLY pkrelname pk+ * SELECT fk.keycols FROM relname fk+ * LEFT OUTER JOIN pkrelname pk
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
* WHERE pk.pkkeycol1 IS NULL AND
* For MATCH SIMPLE:
@@ -1922,7 +1922,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
quoteRelationName(pkrelname, pk_rel);
quoteRelationName(fkrelname, fk_rel);
appendStringInfo(&querybuf,
- " FROM ONLY %s fk LEFT OUTER JOIN ONLY %s pk ON",+ " FROM %s fk LEFT OUTER JOIN %s pk ON",
fkrelname, pkrelname);
strcpy(pkattname, "pk.");
@@ -2345,22 +2345,6 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
/* Find or create a hashtable entry for the constraint */
riinfo = ri_LoadConstraintInfo(constraintOid);
- /* Do some easy cross-checks against the trigger call data */- if (rel_is_pk)- {- if (riinfo->fk_relid != trigger->tgconstrrelid ||- riinfo->pk_relid != RelationGetRelid(trig_rel))- elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",- trigger->tgname, RelationGetRelationName(trig_rel));- }- else- {- if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||- riinfo->pk_relid != trigger->tgconstrrelid)- elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",- trigger->tgname, RelationGetRelationName(trig_rel));- }-
return riinfo;
}
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex d3351f4a83..8f0628da05 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -56,6 +56,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern void CloneForeignKeyConstraints(Oid parentId, Oid relationId);+
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.hindex 06e5180a30..3896da3243 100644--- a/src/include/commands/tablecmds.h+++ b/src/include/commands/tablecmds.h@@ -74,6 +74,10 @@ extern void find_composite_type_dependencies(Oid typeOid,
extern void check_of_type(HeapTuple typetuple);
+extern void createForeignKeyTriggers(Relation rel, Oid refRelOid,+ Constraint *fkconstraint, Oid constraintOid,+ Oid indexOid);+
extern void register_on_commit_action(Oid relid, OnCommitAction action);
extern void remove_on_commit_action(Oid relid);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex ccd2c38dbc..375a357125 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -485,6 +485,117 @@ DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and from partitioned tables+SET search_path TO at_tst;+CREATE SCHEMA at_tst;+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned+ FOR VALUES FROM (0) TO (10000);+-- these fail:+INSERT INTO at_regular2 VALUES (1000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(1000) is not present in table "at_partitioned".+INSERT INTO at_partitioned VALUES (1000, 42);+ERROR: insert or update on table "at_partitioned_0" violates foreign key constraint "at_partitioned_reg1_col1_fkey"+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".+-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);+CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned+ FOR VALUES FROM (10000) TO (20000);+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+ERROR: column "col2" in child table must be marked NOT NULL+ALTER TABLE at_partitioned_2+ ALTER col2 SET NOT NULL,+ ALTER reg1_col1 SET NOT NULL;+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+\d at_regular2+ Table "at_tst.at_regular2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ col3 | integer | | | +Foreign-key constraints:+ "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)++\d at_partitioned+ Table "at_tst.at_partitioned"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition key: RANGE (col2)+Indexes:+ "at_partitioned_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)+Referenced by:+ TABLE "at_regular2" CONSTRAINT "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)+Number of partitions: 3 (Use \d+ to list them.)++\d at_partitioned_0+ Table "at_tst.at_partitioned_0"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (0) TO (10000)+Indexes:+ "at_partitioned_0_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_1+ Table "at_tst.at_partitioned_1"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (10000) TO (20000)+Indexes:+ "at_partitioned_1_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_2+ Table "at_tst.at_partitioned_2"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ reg1_col1 | integer | | not null | + col2 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (20000) TO (30000)+Indexes:+ "at_partitioned_2_pkey" PRIMARY KEY, btree (col2)++INSERT INTO at_partitioned VALUES (5000, 42);+ERROR: insert or update on table "at_partitioned_0" violates foreign key constraint "at_partitioned_reg1_col1_fkey"+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".+INSERT INTO at_regular1 VALUES (42), (1042), (2042);+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(35000) is not present in table "at_partitioned".+-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;+-- disallowed: must drop it from parent instead+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+ERROR: cannot drop inherited constraint "at_partitioned_reg1_col1_fkey" of relation "at_partitioned_0"+-- ok+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+\set VERBOSITY terse+DROP SCHEMA at_tst CASCADE;+NOTICE: drop cascades to 3 other objects+\set VERBOSITY default+RESET search_path;
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -3305,10 +3416,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;-ERROR: foreign key constraints are not supported on partitioned tables-LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;- ^
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
ERROR: exclusion constraints are not supported on partitioned tables
LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.outindex 866cc99b9f..7c3703b73d 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -276,16 +276,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (- a int PRIMARY KEY-);-CREATE TABLE partitioned (- a int REFERENCES pkrel(a)-) PARTITION BY RANGE (a);-ERROR: foreign key constraints are not supported on partitioned tables-LINE 2: a int REFERENCES pkrel(a)- ^-DROP TABLE pkrel;
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.outindex fef072eddf..8c71c21973 100644--- a/src/test/regress/expected/foreign_key.out+++ b/src/test/regress/expected/foreign_key.out@@ -1415,3 +1415,70 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
commit;
drop table pktable2, fktable2;
+--+-- Foreign keys and partitioned tables+--+-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f1 int primary key);+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO fkpart1 VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+UPDATE pkregular SET f1 = 501;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ERROR: cannot drop inherited constraint "fkpartit_f1_fkey" of relation "fkpart1"+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)++ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++DELETE FROM pkregular;+SELECT * FROM fkpartit;+ f1 +----+(0 rows)+diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b73f523e8a..f6dedf0287 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -394,6 +394,62 @@ DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and from partitioned tables+SET search_path TO at_tst;+CREATE SCHEMA at_tst;+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned+ FOR VALUES FROM (0) TO (10000);+-- these fail:+INSERT INTO at_regular2 VALUES (1000);+INSERT INTO at_partitioned VALUES (1000, 42);++-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);++CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned+ FOR VALUES FROM (10000) TO (20000);+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);+ALTER TABLE at_partitioned_2+ ALTER col2 SET NOT NULL,+ ALTER reg1_col1 SET NOT NULL;+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2+ FOR VALUES FROM (20000) TO (30000);++\d at_regular2+\d at_partitioned+\d at_partitioned_0+\d at_partitioned_1+\d at_partitioned_2++INSERT INTO at_partitioned VALUES (5000, 42);+INSERT INTO at_regular1 VALUES (42), (1042), (2042);+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);++-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;++-- disallowed: must drop it from parent instead+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;+-- ok+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;++\set VERBOSITY terse+DROP SCHEMA at_tst CASCADE;+\set VERBOSITY default+RESET search_path;+
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -2035,7 +2091,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-- cannot drop column that is part of the partition key
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sqlindex fefccf21a2..09a634d79d 100644--- a/src/test/regress/sql/create_table.sql+++ b/src/test/regress/sql/create_table.sql@@ -294,14 +294,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (- a int PRIMARY KEY-);-CREATE TABLE partitioned (- a int REFERENCES pkrel(a)-) PARTITION BY RANGE (a);-DROP TABLE pkrel;-
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sqlindex 5f19dad03c..1a2c93fcce 100644--- a/src/test/regress/sql/foreign_key.sql+++ b/src/test/regress/sql/foreign_key.sql@@ -1055,3 +1055,31 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
commit;
drop table pktable2, fktable2;
+++--+-- Foreign keys and partitioned tables+--++-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f1 int primary key);+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+UPDATE pkregular SET f1 = 501;++ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+\d fkpart1+ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+\d fkpart1+DELETE FROM pkregular;+SELECT * FROM fkpartit;--
2.11.0
[text/plain] v2-0005-don-t-error-creating-constraint-triggers-if-inter.patch (1.2K, ../20180124175245.hsakxldr4cd74svv@alvherre.pgsql/6-v2-0005-don-t-error-creating-constraint-triggers-if-inter.patch)
download | inline diff:
From 96aa2fa7eb0554483dd960cd6e8964bbf07521b9 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 24 Jan 2018 13:02:38 -0300
Subject: [PATCH v2 5/5] don't error creating constraint triggers if internal
---
src/backend/commands/trigger.c | 8 ++++++--
1 file changed, 6 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex 7cb709ea26..71a1fbeca5 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -238,9 +238,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));
/*
- * Constraint triggers are not allowed, either.+ * Constraint triggers are not allowed, either, except those+ * created internally. (This distinction is important because+ * internally-created triggers are expected to recurse creation+ * by themselves, while regular ones would have to be recursed+ * here, and that's not implemented yet.)
*/
- if (stmt->isconstraint)+ if (stmt->isconstraint && !isInternal)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("\"%s\" is a partitioned table",
--
2.11.0
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: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org
Subject: Re: Foreign keys and partitioned tables
In-Reply-To: <20180124175245.hsakxldr4cd74svv@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox