Skip to content

Commit eb7ed3f

Browse files
alvherrealvherre
committed
Allow UNIQUE indexes on partitioned tables
If we restrict unique constraints on partitioned tables so that they must always include the partition key, then our standard approach to unique indexes already works --- each unique key is forced to exist within a single partition, so enforcing the unique restriction in each index individually is enough to have it enforced globally. Therefore we can implement unique indexes on partitions by simply removing a few restrictions (and adding others.) Discussion: https://postgr.es/m/20171222212921.hi6hg6pem2w2t36z@alvherre.pgsql Discussion: https://postgr.es/m/20171229230607.3iib6b62fn3uaf47@alvherre.pgsql Reviewed-by: Simon Riggs, Jesper Pedersen, Peter Eisentraut, Jaime Casanova, Amit Langote
1 parent 524d64e commit eb7ed3f

27 files changed

+907
-95
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3146,9 +3146,8 @@ CREATE TABLE measurement_y2006m02 PARTITION OF measurement
31463146
<para>
31473147
Create an index on the key column(s), as well as any other indexes you
31483148
might want, on the partitioned table. (The key index is not strictly
3149-
necessary, but in most scenarios it is helpful. If you intend the key
3150-
values to be unique then you should always create a unique or
3151-
primary-key constraint for each partition.) This automatically creates
3149+
necessary, but in most scenarios it is helpful.)
3150+
This automatically creates
31523151
one index on each partition, and any partitions you create or attach
31533152
later will also contain the index.
31543153

@@ -3270,15 +3269,15 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
32703269
<itemizedlist>
32713270
<listitem>
32723271
<para>
3273-
There is no way to create a primary key, unique constraint, or
3272+
There is no way to create a
32743273
exclusion constraint spanning all partitions; it is only possible
32753274
to constrain each leaf partition individually.
32763275
</para>
32773276
</listitem>
32783277

32793278
<listitem>
32803279
<para>
3281-
Since primary keys are not supported on partitioned tables, foreign
3280+
While primary keys are supported on partitioned tables, foreign
32823281
keys referencing partitioned tables are not supported, nor are foreign
32833282
key references from a partitioned table to some other table.
32843283
</para>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -412,6 +412,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
412412
disappear too.
413413
</para>
414414

415+
<para>
416+
Additional restrictions apply when unique or primary key constraints
417+
are added to partitioned tables; see <xref linkend="sql-createtable" />.
418+
</para>
419+
415420
<note>
416421
<para>
417422
Adding a constraint using an existing index can be helpful in
@@ -834,9 +839,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
834839
<para>
835840
This form attaches an existing table (which might itself be partitioned)
836841
as a partition of the target table. The table can be attached
837-
as a partition for specific values using <literal>FOR VALUES
838-
</literal> or as a default partition by using <literal>DEFAULT
839-
</literal>. For each index in the target table, a corresponding
842+
as a partition for specific values using <literal>FOR VALUES</literal>
843+
or as a default partition by using <literal>DEFAULT</literal>.
844+
For each index in the target table, a corresponding
840845
one will be created in the attached table; or, if an equivalent
841846
index already exists, will be attached to the target table's index,
842847
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
@@ -851,8 +856,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
851856
as the target table and no more; moreover, the column types must also
852857
match. Also, it must have all the <literal>NOT NULL</literal> and
853858
<literal>CHECK</literal> constraints of the target table. Currently
854-
<literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
855859
<literal>FOREIGN KEY</literal> constraints are not considered.
860+
<literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints
861+
from the parent table will be created in the partition, if they don't
862+
already exist.
856863
If any of the <literal>CHECK</literal> constraints of the table being
857864
attached is marked <literal>NO INHERIT</literal>, the command will fail;
858865
such a constraint must be recreated without the <literal>NO INHERIT</literal>

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,11 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
108108
insert or update data which would result in duplicate entries
109109
will generate an error.
110110
</para>
111+
112+
<para>
113+
Additional restrictions apply when unique indexes are applied to
114+
partitioned tables; see <xref linkend="sql-createtable" />.
115+
</para>
111116
</listitem>
112117
</varlistentry>
113118

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -546,8 +546,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
546546
</para>
547547

548548
<para>
549-
Partitioned tables do not support <literal>UNIQUE</literal>,
550-
<literal>PRIMARY KEY</literal>, <literal>EXCLUDE</literal>, or
549+
Partitioned tables do not support <literal>EXCLUDE</literal> or
551550
<literal>FOREIGN KEY</literal> constraints; however, you can define
552551
these constraints on individual partitions.
553552
</para>
@@ -786,6 +785,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
786785
primary key constraint defined for the table. (Otherwise it
787786
would just be the same constraint listed twice.)
788787
</para>
788+
789+
<para>
790+
When used on partitioned tables, unique constraints must include all the
791+
columns of the partition key.
792+
If any partitions are in turn partitioned, all columns of each partition
793+
key are considered at each level below the <literal>UNIQUE</literal>
794+
constraint.
795+
</para>
789796
</listitem>
790797
</varlistentry>
791798

@@ -814,6 +821,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
814821
about the design of the schema, since a primary key implies that other
815822
tables can rely on this set of columns as a unique identifier for rows.
816823
</para>
824+
825+
<para>
826+
<literal>PRIMARY KEY</literal> constraints share the restrictions that
827+
<literal>UNIQUE</literal> constraints have when placed on partitioned
828+
tables.
829+
</para>
830+
817831
</listitem>
818832
</varlistentry>
819833

‎src/backend/bootstrap/bootparse.y

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -322,6 +322,7 @@ Boot_DeclareIndexStmt:
322322
stmt,
323323
$4,
324324
InvalidOid,
325+
InvalidOid,
325326
false,
326327
false,
327328
false,
@@ -367,6 +368,7 @@ Boot_DeclareUniqueIndexStmt:
367368
stmt,
368369
$5,
369370
InvalidOid,
371+
InvalidOid,
370372
false,
371373
false,
372374
false,

‎src/backend/catalog/index.c

Lines changed: 45 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -691,6 +691,8 @@ UpdateIndexRelation(Oid indexoid,
691691
* nonzero to specify a preselected OID.
692692
* parentIndexRelid: if creating an index partition, the OID of the
693693
* parent index; otherwise InvalidOid.
694+
* parentConstraintId: if creating a constraint on a partition, the OID
695+
* of the constraint in the parent; otherwise InvalidOid.
694696
* relFileNode: normally, pass InvalidOid to get new storage. May be
695697
* nonzero to attach an existing valid build.
696698
* indexInfo: same info executor uses to insert into the index
@@ -722,6 +724,7 @@ UpdateIndexRelation(Oid indexoid,
722724
* (only if INDEX_CREATE_ADD_CONSTRAINT is set)
723725
* allow_system_table_mods: allow table to be a system catalog
724726
* is_internal: if true, post creation hook for new index
727+
* constraintId: if not NULL, receives OID of created constraint
725728
*
726729
* Returns the OID of the created index.
727730
*/
@@ -730,6 +733,7 @@ index_create(Relation heapRelation,
730733
const char *indexRelationName,
731734
Oid indexRelationId,
732735
Oid parentIndexRelid,
736+
Oid parentConstraintId,
733737
Oid relFileNode,
734738
IndexInfo *indexInfo,
735739
List *indexColNames,
@@ -742,7 +746,8 @@ index_create(Relation heapRelation,
742746
bits16 flags,
743747
bits16 constr_flags,
744748
bool allow_system_table_mods,
745-
bool is_internal)
749+
bool is_internal,
750+
Oid *constraintId)
746751
{
747752
Oid heapRelationId = RelationGetRelid(heapRelation);
748753
Relation pg_class;
@@ -989,6 +994,7 @@ index_create(Relation heapRelation,
989994
if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0)
990995
{
991996
char constraintType;
997+
ObjectAddress localaddr;
992998

993999
if (isprimary)
9941000
constraintType = CONSTRAINT_PRIMARY;
@@ -1002,14 +1008,17 @@ index_create(Relation heapRelation,
10021008
constraintType = 0; /* keep compiler quiet */
10031009
}
10041010

1005-
index_constraint_create(heapRelation,
1011+
localaddr = index_constraint_create(heapRelation,
10061012
indexRelationId,
1013+
parentConstraintId,
10071014
indexInfo,
10081015
indexRelationName,
10091016
constraintType,
10101017
constr_flags,
10111018
allow_system_table_mods,
10121019
is_internal);
1020+
if (constraintId)
1021+
*constraintId = localaddr.objectId;
10131022
}
10141023
else
10151024
{
@@ -1181,6 +1190,8 @@ index_create(Relation heapRelation,
11811190
*
11821191
* heapRelation: table owning the index (must be suitably locked by caller)
11831192
* indexRelationId: OID of the index
1193+
* parentConstraintId: if constraint is on a partition, the OID of the
1194+
* constraint in the parent.
11841195
* indexInfo: same info executor uses to insert into the index
11851196
* constraintName: what it say (generally, should match name of index)
11861197
* constraintType: one of CONSTRAINT_PRIMARY, CONSTRAINT_UNIQUE, or
@@ -1198,6 +1209,7 @@ index_create(Relation heapRelation,
11981209
ObjectAddress
11991210
index_constraint_create(Relation heapRelation,
12001211
Oid indexRelationId,
1212+
Oid parentConstraintId,
12011213
IndexInfo *indexInfo,
12021214
const char *constraintName,
12031215
char constraintType,
@@ -1212,6 +1224,9 @@ index_constraint_create(Relation heapRelation,
12121224
bool deferrable;
12131225
bool initdeferred;
12141226
bool mark_as_primary;
1227+
bool islocal;
1228+
bool noinherit;
1229+
int inhcount;
12151230

12161231
deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0;
12171232
initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0;
@@ -1246,6 +1261,19 @@ index_constraint_create(Relation heapRelation,
12461261
deleteDependencyRecordsForClass(RelationRelationId, indexRelationId,
12471262
RelationRelationId, DEPENDENCY_AUTO);
12481263

1264+
if (OidIsValid(parentConstraintId))
1265+
{
1266+
islocal = false;
1267+
inhcount = 1;
1268+
noinherit = false;
1269+
}
1270+
else
1271+
{
1272+
islocal = true;
1273+
inhcount = 0;
1274+
noinherit = true;
1275+
}
1276+
12491277
/*
12501278
* Construct a pg_constraint entry.
12511279
*/
@@ -1273,9 +1301,9 @@ index_constraint_create(Relation heapRelation,
12731301
NULL, /* no check constraint */
12741302
NULL,
12751303
NULL,
1276-
true, /* islocal */
1277-
0, /* inhcount */
1278-
true, /* noinherit */
1304+
islocal,
1305+
inhcount,
1306+
noinherit,
12791307
is_internal);
12801308

12811309
/*
@@ -1294,6 +1322,18 @@ index_constraint_create(Relation heapRelation,
12941322

12951323
recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL);
12961324

1325+
/*
1326+
* Also, if this is a constraint on a partition, mark it as depending
1327+
* on the constraint in the parent.
1328+
*/
1329+
if (OidIsValid(parentConstraintId))
1330+
{
1331+
ObjectAddress parentConstr;
1332+
1333+
ObjectAddressSet(parentConstr, ConstraintRelationId, parentConstraintId);
1334+
recordDependencyOn(&referenced, &parentConstr, DEPENDENCY_INTERNAL_AUTO);
1335+
}
1336+
12971337
/*
12981338
* If the constraint is deferrable, create the deferred uniqueness
12991339
* checking trigger. (The trigger will be given an internal dependency on

‎src/backend/catalog/pg_constraint.c

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -747,6 +747,43 @@ AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
747747
heap_close(conRel, RowExclusiveLock);
748748
}
749749

750+
/*
751+
* ConstraintSetParentConstraint
752+
* Set a partition's constraint as child of its parent table's
753+
*
754+
* This updates the constraint's pg_constraint row to show it as inherited, and
755+
* add a dependency to the parent so that it cannot be removed on its own.
756+
*/
757+
void
758+
ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
759+
{
760+
Relation constrRel;
761+
Form_pg_constraint constrForm;
762+
HeapTuple tuple,
763+
newtup;
764+
ObjectAddress depender;
765+
ObjectAddress referenced;
766+
767+
constrRel = heap_open(ConstraintRelationId, RowExclusiveLock);
768+
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(childConstrId));
769+
if (!HeapTupleIsValid(tuple))
770+
elog(ERROR, "cache lookup failed for constraint %u", childConstrId);
771+
newtup = heap_copytuple(tuple);
772+
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
773+
constrForm->conislocal = false;
774+
constrForm->coninhcount++;
775+
CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
776+
ReleaseSysCache(tuple);
777+
778+
ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
779+
ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
780+
781+
recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
782+
783+
heap_close(constrRel, RowExclusiveLock);
784+
}
785+
786+
750787
/*
751788
* get_relation_constraint_oid
752789
* Find a constraint on the specified relation with the specified name.
@@ -903,6 +940,45 @@ get_relation_constraint_attnos(Oid relid, const char *conname,
903940
return conattnos;
904941
}
905942

943+
/*
944+
* Return the OID of the constraint associated with the given index in the
945+
* given relation; or InvalidOid if no such index is catalogued.
946+
*/
947+
Oid
948+
get_relation_idx_constraint_oid(Oid relationId, Oid indexId)
949+
{
950+
Relation pg_constraint;
951+
SysScanDesc scan;
952+
ScanKeyData key;
953+
HeapTuple tuple;
954+
Oid constraintId = InvalidOid;
955+
956+
pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
957+
958+
ScanKeyInit(&key,
959+
Anum_pg_constraint_conrelid,
960+
BTEqualStrategyNumber,
961+
F_OIDEQ,
962+
ObjectIdGetDatum(relationId));
963+
scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId,
964+
true, NULL, 1, &key);
965+
while ((tuple = systable_getnext(scan)) != NULL)
966+
{
967+
Form_pg_constraint constrForm;
968+
969+
constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
970+
if (constrForm->conindid == indexId)
971+
{
972+
constraintId = HeapTupleGetOid(tuple);
973+
break;
974+
}
975+
}
976+
systable_endscan(scan);
977+
978+
heap_close(pg_constraint, AccessShareLock);
979+
return constraintId;
980+
}
981+
906982
/*
907983
* get_domain_constraint_oid
908984
* Find a constraint on the specified domain with the specified name.

‎src/backend/catalog/toasting.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -330,13 +330,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
330330
coloptions[1] = 0;
331331

332332
index_create(toast_rel, toast_idxname, toastIndexOid, InvalidOid,
333-
InvalidOid,
333+
InvalidOid, InvalidOid,
334334
indexInfo,
335335
list_make2("chunk_id", "chunk_seq"),
336336
BTREE_AM_OID,
337337
rel->rd_rel->reltablespace,
338338
collationObjectId, classObjectId, coloptions, (Datum) 0,
339-
INDEX_CREATE_IS_PRIMARY, 0, true, true);
339+
INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL);
340340

341341
heap_close(toast_rel, NoLock);
342342

0 commit comments

Comments
 (0)