Hello,
I'm giving this patch its own thread for mental sanity, but this is
essentially what already posted in [1], plus some doc fixes. This patch
depends on the main "local partitioned indexes" in that thread, last
version of which is at [2].
I also added a mechanism to set the constraints in partitions as
dependent on the constraint in the parent partitioned table, so deletion
is sensible: the PK in partitions goes away when the PK in the parent
table is dropped; and you can't drop the PK in partitions on their own.
In order to implement that I dress Rube Goldberg as Santa: the
constraint OID of the parent is needed by index_constraint_create when
creating the child; but it's the previous index_constraint_create itself
that generates the OID when creating the parent, and it's DefineIndex
that does the recursion. So index_constraint_create returns the value
to index_create who returns it to DefineIndex, so that the recursive
step can pass it down to index_create to give it to
index_constraint_create. It seems crazy, but it's correct.
As far as I can tell, pg_dump works correctly without any additional
changes.
[1] https://postgr.es/m/20171220194937.pldcecyx7yrwmgkg@alvherre.pgsql
[2] https://postgr.es/m/20171220212503.aamhlrs425flg47f@alvherre.pgsql
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 311cdeeae542a3f4cc20bf2308488756b8d3da80 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Mon, 6 Nov 2017 17:04:55 +0100
Subject: [PATCH] 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 | 28 +++++-
src/backend/catalog/toasting.c | 4 +-
src/backend/commands/indexcmds.c | 80 ++++++++++++++--
src/backend/commands/tablecmds.c | 12 ++-
src/backend/parser/parse_utilcmd.c | 31 +------
src/backend/tcop/utility.c | 1 +
src/include/catalog/index.h | 5 +-
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 | 142 ++++++++++++++++++++++++++++-
src/test/regress/sql/alter_table.sql | 2 -
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/indexing.sql | 73 ++++++++++++++-
18 files changed, 355 insertions(+), 82 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 0a2f3e3646..ee6a45c9ad 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -782,8 +782,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</command> had been executed.
@@ -798,8 +799,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.sgml
index a0c9a6d257..98ab39473b 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.y
index 95835ac1e7..fb14f558ec 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.c
index a62fe158ce..58c126f17f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -677,6 +677,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
@@ -708,6 +710,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.
*/
@@ -716,6 +719,7 @@ index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -728,7 +732,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;
@@ -971,6 +976,7 @@ index_create(Relation heapRelation,
if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
{
char constraintType;
+ ObjectAddress localaddr;
if (isprimary)
constraintType = CONSTRAINT_PRIMARY;
@@ -984,14 +990,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
{
@@ -1160,6 +1169,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
@@ -1177,6 +1188,7 @@ index_create(Relation heapRelation,
ObjectAddress
index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
@@ -1274,6 +1286,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);
+ }
+
+ /*
* 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/toasting.c b/src/backend/catalog/toasting.c
index 09582a8d52..94e167d775 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.c
index e925351056..e5b2c1d1e4 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -299,6 +299,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.)
@@ -315,6 +317,7 @@ DefineIndex(Oid relationId,
IndexStmt *stmt,
Oid indexRelationId,
Oid parentIndexId,
+ Oid parentConstraintId,
bool is_alter_table,
bool check_rights,
bool check_not_in_use,
@@ -329,6 +332,7 @@ DefineIndex(Oid relationId,
Oid accessMethodId;
Oid namespaceId;
Oid tablespaceId;
+ Oid createdConstraintId;
List *indexColNames;
Relation rel;
Relation indexRelation;
@@ -426,20 +430,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")));
}
/*
@@ -637,6 +632,68 @@ 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;
+
+ /*
+ * 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),
+ /* XXX reformulate error
message? */
+ errmsg("UNIQUE constraints are
not supported on partitioned tables using expressions as partition keys")));
+
+ 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 UNIQUE constraint definition"),
+ errdetail("UNIQUE constraint
on table \"%s\" does not include column \"%s\" which is part of the partition
key.",
+
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.
*/
@@ -733,12 +790,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);
@@ -850,6 +909,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.c
index 47cb7c13ef..b69b9d3cde 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -938,17 +938,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);
@@ -6873,6 +6876,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 */
@@ -6965,6 +6969,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation
rel,
address = index_constraint_create(rel,
index_oid,
+
InvalidOid,
indexInfo,
constraintName,
constraintType,
@@ -14166,12 +14171,15 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd)
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);
}
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 45f6ec2820..917b9bd636 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.c
index 8b5fd95a96..974b2f2c33 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1337,6 +1337,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.h
index 112d69debc..9a35ad6c0d 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/commands/defrem.h b/src/include/commands/defrem.h
index 35f50c1175..1435a31940 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.h
index 599f0e8e29..c130b3a93a 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.out
index 517fb080bd..2caf930242 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3290,14 +3290,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.out
index 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.out
index 1b391c2017..c702cb02aa 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -25,8 +25,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;
@@ -404,6 +402,146 @@ 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" does not include 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" does not include 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 UNIQUE constraint definition
+DETAIL: UNIQUE constraint on table "idxpart" does not include 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 UNIQUE constraint definition
+DETAIL: UNIQUE constraint on table "idxpart" does not include 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: UNIQUE constraints are not supported on partitioned tables using
expressions as partition keys
+-- 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 UNIQUE constraint definition
+DETAIL: UNIQUE constraint on table "idxpart" does not include 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" does not include 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;
+-- multi-layer partitioning honors the prohibition. So this fails:
+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);
+ERROR: insufficient columns in UNIQUE constraint definition
+DETAIL: UNIQUE constraint on table "idxpart2" does not include column "b"
which is part of the partition key.
+drop table idxpart;
+-- but this works:
+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;
-- 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);
diff --git a/src/test/regress/sql/alter_table.sql
b/src/test/regress/sql/alter_table.sql
index af25ee9e77..ed0bb7845b 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2016,8 +2016,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.sql
index 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.sql
index f764b29b39..e76c524f81 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -14,7 +14,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;
@@ -172,6 +171,78 @@ 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;
+
+-- multi-layer partitioning honors the prohibition. So this fails:
+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);
+drop table idxpart;
+
+-- but this works:
+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;
+
-- 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);
--
2.11.0
