Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commiteb7ed3f

Browse files
committed
Allow UNIQUE indexes on partitioned tables
If we restrict unique constraints on partitioned tables so that theymust always include the partition key, then our standard approach tounique indexes already works --- each unique key is forced to existwithin a single partition, so enforcing the unique restriction in eachindex individually is enough to have it enforced globally. Therefore wecan implement unique indexes on partitions by simply removing a fewrestrictions (and adding others.)Discussion:https://postgr.es/m/20171222212921.hi6hg6pem2w2t36z@alvherre.pgsqlDiscussion:https://postgr.es/m/20171229230607.3iib6b62fn3uaf47@alvherre.pgsqlReviewed-by: Simon Riggs, Jesper Pedersen, Peter Eisentraut, JaimeCasanova, Amit Langote
1 parent524d64e commiteb7ed3f

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
constchar*indexRelationName,
731734
OidindexRelationId,
732735
OidparentIndexRelid,
736+
OidparentConstraintId,
733737
OidrelFileNode,
734738
IndexInfo*indexInfo,
735739
List*indexColNames,
@@ -742,7 +746,8 @@ index_create(Relation heapRelation,
742746
bits16flags,
743747
bits16constr_flags,
744748
boolallow_system_table_mods,
745-
boolis_internal)
749+
boolis_internal,
750+
Oid*constraintId)
746751
{
747752
OidheapRelationId=RelationGetRelid(heapRelation);
748753
Relationpg_class;
@@ -989,6 +994,7 @@ index_create(Relation heapRelation,
989994
if ((flags&INDEX_CREATE_ADD_CONSTRAINT)!=0)
990995
{
991996
charconstraintType;
997+
ObjectAddresslocaladdr;
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(RelationheapRelation,
12001211
OidindexRelationId,
1212+
OidparentConstraintId,
12011213
IndexInfo*indexInfo,
12021214
constchar*constraintName,
12031215
charconstraintType,
@@ -1212,6 +1224,9 @@ index_constraint_create(Relation heapRelation,
12121224
booldeferrable;
12131225
boolinitdeferred;
12141226
boolmark_as_primary;
1227+
boolislocal;
1228+
boolnoinherit;
1229+
intinhcount;
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+
ObjectAddressparentConstr;
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(OidchildConstrId,OidparentConstrId)
759+
{
760+
RelationconstrRel;
761+
Form_pg_constraintconstrForm;
762+
HeapTupletuple,
763+
newtup;
764+
ObjectAddressdepender;
765+
ObjectAddressreferenced;
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
returnconattnos;
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(OidrelationId,OidindexId)
949+
{
950+
Relationpg_constraint;
951+
SysScanDescscan;
952+
ScanKeyDatakey;
953+
HeapTupletuple;
954+
OidconstraintId=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_constraintconstrForm;
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+
returnconstraintId;
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)
[8]ページ先頭
©2009-2025 Movatter.jp