Hello,
Thanks, Peter, Jesper, Amit, for reviewing the patch. Replying to
all review comments at once:
Jesper Pedersen wrote:
> Maybe add a test case to indexing.sql that highlights that hash indexes
> doesn't support UNIQUE; although not unique to partitioned indexes.
I'm not sure about this. If one day unique is supported by hash, why
would this test have to be modified? Other than to add a few relevant
test cases, that is! Lack of support is already tested elsewhere (one
hopes).
Peter Eisentraut wrote:
> + 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)));
>
> Double errmsg(). (Maybe an Assert somewhere should help catch this?)
Ooh, great catch! Fixed.
> +alter table idxpart add primary key (a); -- not an incomplete one tho
>
> "though"?
Fixed :-)
> I would like to see some tests that the unique constraints are actually
> enforced. That is, insert some duplicate values and see it fail. Throw
> some null values in, to check PK behavior as well. It should be
> trivial, but seems kind of useful.
Added.
Amit Langote said:
> That said, I think that it might be a good idea to include the above
> detail in the documentation of CREATE INDEX and ALTER TABLE ADD UNIQUE.
Added some text there.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 6024a4317ed36d038e2b43945db043b717722afc 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 v5] allow indexes on partitioned tables to be unique
---
doc/src/sgml/ddl.sgml | 9 +-
doc/src/sgml/ref/alter_table.sgml | 14 +-
doc/src/sgml/ref/create_index.sgml | 5 +
doc/src/sgml/ref/create_table.sgml | 19 +-
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/bin/pg_dump/t/002_pg_dump.pl | 65 ++++++
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 | 294 +++++++++++++++++++++++++-
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 | 172 ++++++++++++++-
25 files changed, 883 insertions(+), 93 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8c3be5b103..15a9285136 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3146,9 +3146,8 @@ CREATE TABLE measurement_y2006m02 PARTITION OF measurement
<para>
Create an index on the key column(s), as well as any other indexes you
might want, on the partitioned table. (The key index is not strictly
- necessary, but in most scenarios it is helpful. If you intend the key
- values to be unique then you should always create a unique or
- primary-key constraint for each partition.) This automatically creates
+ necessary, but in most scenarios it is helpful.)
+ This automatically creates
one index on each partition, and any partitions you create or attach
later will also contain the index.
@@ -3270,7 +3269,7 @@ ALTER TABLE measurement ATTACH PARTITION
measurement_y2008m02
<itemizedlist>
<listitem>
<para>
- There is no way to create a primary key, unique constraint, or
+ There is no way to create a
exclusion constraint spanning all partitions; it is only possible
to constrain each leaf partition individually.
</para>
@@ -3278,7 +3277,7 @@ ALTER TABLE measurement ATTACH PARTITION
measurement_y2008m02
<listitem>
<para>
- Since primary keys are not supported on partitioned tables, foreign
+ While primary keys are supported on partitioned tables, foreign
keys referencing partitioned tables are not supported, nor are foreign
key references from a partitioned table to some other table.
</para>
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 2b514b7606..2d3b6d3960 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -412,6 +412,11 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
disappear too.
</para>
+ <para>
+ Additional restrictions apply when unique or primary key constraints
+ are added to partitioned tables; see <xref linkend="sql-createtable" />.
+ </para>
+
<note>
<para>
Adding a constraint using an existing index can be helpful in
@@ -835,8 +840,9 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
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.
@@ -851,8 +857,10 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
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_index.sgml
b/doc/src/sgml/ref/create_index.sgml
index f464557de8..1fd21e12bd 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -108,6 +108,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS
] <replaceable class=
insert or update data which would result in duplicate entries
will generate an error.
</para>
+
+ <para>
+ Additional restrictions apply when unique indexes are applied to
+ partitioned tables; see <xref linkend="sql-createtable" />.
+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index d2df40d543..83d3472da2 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,14 @@ 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, unique constraints must include all the
+ columns of the partition key.
+ If any partitions are in turn partitioned, all columns of each partition
+ key are considered at each level below the <literal>UNIQUE</literal>
+ constraint.
+ </para>
</listitem>
</varlistentry>
@@ -814,6 +822,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 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.c
index f2cb6d7fb8..a5fa3540a7 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -691,6 +691,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
@@ -722,6 +724,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.
*/
@@ -730,6 +733,7 @@ index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid parentIndexRelid,
+ Oid parentConstraintId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
@@ -742,7 +746,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;
@@ -989,6 +994,7 @@ index_create(Relation heapRelation,
if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
{
char constraintType;
+ ObjectAddress localaddr;
if (isprimary)
constraintType = CONSTRAINT_PRIMARY;
@@ -1002,14 +1008,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
{
@@ -1181,6 +1190,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
@@ -1198,6 +1209,7 @@ index_create(Relation heapRelation,
ObjectAddress
index_constraint_create(Relation heapRelation,
Oid indexRelationId,
+ Oid parentConstraintId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
@@ -1212,6 +1224,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;
@@ -1246,6 +1260,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.
*/
@@ -1273,8 +1298,8 @@ index_constraint_create(Relation heapRelation,
NULL,
/* no check constraint */
NULL,
NULL,
- true,
/* islocal */
- 0, /*
inhcount */
+ islocal,
+ inhcount,
true,
/* noinherit */
is_internal);
@@ -1295,6 +1320,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.c
index 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.c
index dcbad1286b..8bf2698545 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -330,13 +330,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 7c46613215..3e48a58dcb 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;
@@ -432,20 +437,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")));
}
/*
@@ -644,6 +640,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),
+ errdetail("%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.
*/
@@ -740,12 +814,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);
@@ -832,16 +908,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;
}
@@ -872,6 +972,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 89454d8e80..19233b68cb 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);
@@ -6823,6 +6826,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 */
@@ -6915,6 +6919,7 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation
rel,
address = index_constraint_create(rel,
index_oid,
+
InvalidOid,
indexInfo,
constraintName,
constraintType,
@@ -14146,6 +14151,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation
attachrel)
IndexInfo *info;
AttrNumber *attmap;
bool found = false;
+ Oid constraintOid;
/*
* Ignore indexes in the partitioned table other than
partitioned
@@ -14162,6 +14168,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
@@ -14170,6 +14177,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;
@@ -14182,8 +14191,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;
}
@@ -14196,12 +14223,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);
}
@@ -14444,6 +14474,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.
@@ -14499,8 +14531,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.c
index 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.c
index d415d7180f..ad3f212185 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -712,12 +712,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:
@@ -727,12 +721,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);
@@ -829,12 +817,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;
@@ -845,12 +827,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;
@@ -1192,7 +1168,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)
@@ -1275,7 +1251,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;
@@ -1373,6 +1349,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 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/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 7b21709f76..ac9cfa04c1 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -5242,6 +5242,40 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL
WITH FUNCTION pg_catalog
role => 1,
section_pre_data => 1, }, },
+ 'ALTER TABLE measurement PRIMARY KEY' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ create_order => 93,
+ create_sql => 'ALTER TABLE dump_test.measurement ADD PRIMARY
KEY (city_id, logdate);',
+ regexp => qr/^
+ \QALTER TABLE ONLY measurement\E \n^\s+
+ \QADD CONSTRAINT measurement_pkey PRIMARY KEY (city_id,
logdate);\E
+ /xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1, },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_pre_data => 1, }, },
+
'CREATE INDEX ... ON measurement_y2006_m2' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
@@ -5304,6 +5338,37 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL
WITH FUNCTION pg_catalog
section_pre_data => 1,
test_schema_plus_blobs => 1, }, },
+ 'ALTER INDEX ... ATTACH PARTITION (primary key)' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ regexp => qr/^
+ \QALTER INDEX dump_test.measurement_pkey ATTACH PARTITION
dump_test_second_schema.measurement_y2006m2_pkey\E
+ /xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ pg_dumpall_dbprivs => 1,
+ role => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ with_oids => 1, },
+ unlike => {
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1, }, },
+
'CREATE VIEW test_view' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a5cd8ddb1e..f20c5f789b 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.h
index 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.h
index 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.h
index 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.out
index 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.out
index bef5463bab..39a963888d 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -281,12 +281,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
);
@@ -298,12 +292,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 e034ad3aad..0a980dc07d 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;
@@ -755,6 +753,296 @@ 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;
+-- 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 ...
+ ^
+-- no expressions in partition key for PK/UNIQUE
+create table idxpart (a int primary key, b int) partition by range ((b + a));
+ERROR: unsupported PRIMARY KEY constraint with partition key definition
+DETAIL: PRIMARY KEY constraints cannot be used when partition keys include
expressions.
+create table idxpart (a int unique, b int) partition by range ((b + a));
+ERROR: unsupported UNIQUE constraint with partition key definition
+DETAIL: UNIQUE constraints cannot be used when partition keys include
expressions.
+-- use ALTER TABLE to add a primary key
+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 though
+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); -- this works
+\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;
+-- use ALTER TABLE to add a unique constraint
+create table idxpart (a int, b int) partition by range (a, b);
+alter table idxpart add unique (a); -- not an incomplete
one though
+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); -- this works
+\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;
+-- Test that unique constraints are working
+create table idxpart (a int, b text, primary key (a, b)) partition by range
(a);
+create table idxpart1 partition of idxpart for values from (0) to (100000);
+create table idxpart2 (c int, like idxpart);
+insert into idxpart2 (c, a, b) values (42, 572814, 'inserted first');
+alter table idxpart2 drop column c;
+create unique index on idxpart (a);
+alter table idxpart attach partition idxpart2 for values from (100000) to
(1000000);
+insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen');
+insert into idxpart select 2^g, format('two to power of %s', g) from
generate_series(15, 17) g;
+ERROR: duplicate key value violates unique constraint "idxpart1_a_idx"
+DETAIL: Key (a)=(65536) already exists.
+insert into idxpart values (16, 'sixteen');
+insert into idxpart (b, a) values ('one', 142857), ('two', 285714);
+insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and
2^19;
+ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+DETAIL: Key (a)=(285714) already exists.
+insert into idxpart values (572814, 'five');
+ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+DETAIL: Key (a)=(572814) already exists.
+insert into idxpart values (857142, 'six');
+select tableoid::regclass, * from idxpart order by a;
+ tableoid | a | b
+----------+--------+----------------
+ idxpart1 | 0 | zero
+ idxpart1 | 16 | sixteen
+ idxpart1 | 42 | life
+ idxpart1 | 65536 | sixteen
+ idxpart2 | 142857 | one
+ idxpart2 | 285714 | two
+ idxpart2 | 572814 | inserted first
+ idxpart2 | 857142 | six
+(8 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);
@@ -766,3 +1054,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.out
index 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.sql
index 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.sql
index fdd6d14104..7d67ce05d9 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -298,10 +298,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
);
@@ -311,10 +307,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 1a9ea89ade..f3d0387f34 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;
@@ -383,6 +382,175 @@ 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;
+
+-- not other types of index-based constraints
+create table idxpart (a int, exclude (a with = )) partition by range (a);
+
+-- no expressions in partition key for PK/UNIQUE
+create table idxpart (a int primary key, b int) partition by range ((b + a));
+create table idxpart (a int unique, b int) partition by range ((b + a));
+
+-- use ALTER TABLE to add a primary key
+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 though
+alter table idxpart add primary key (a, b); -- this works
+\d idxpart
+create table idxpart1 partition of idxpart for values from (0, 0) to (1000,
1000);
+\d idxpart1
+drop table idxpart;
+
+-- use ALTER TABLE to add a unique constraint
+create table idxpart (a int, b int) partition by range (a, b);
+alter table idxpart add unique (a); -- not an incomplete
one though
+alter table idxpart add unique (b, a); -- this works
+\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;
+
+-- Test that unique constraints are working
+create table idxpart (a int, b text, primary key (a, b)) partition by range
(a);
+create table idxpart1 partition of idxpart for values from (0) to (100000);
+create table idxpart2 (c int, like idxpart);
+insert into idxpart2 (c, a, b) values (42, 572814, 'inserted first');
+alter table idxpart2 drop column c;
+create unique index on idxpart (a);
+alter table idxpart attach partition idxpart2 for values from (100000) to
(1000000);
+insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen');
+insert into idxpart select 2^g, format('two to power of %s', g) from
generate_series(15, 17) g;
+insert into idxpart values (16, 'sixteen');
+insert into idxpart (b, a) values ('one', 142857), ('two', 285714);
+insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and
2^19;
+insert into idxpart values (572814, 'five');
+insert into idxpart values (857142, 'six');
+select tableoid::regclass, * from idxpart order by a;
+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);
@@ -394,3 +562,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
