Skip to content

Commit 3bfea5c

Browse files
tglsfdctglsfdc
committed
Fix subtly-incorrect matching of parent and child partitioned indexes.
When creating a partitioned index, DefineIndex tries to identify any existing indexes on the partitions that match the partitioned index, so that it can absorb those as child indexes instead of building new ones. Part of the matching is to compare IndexInfo structs --- but that wasn't done quite right. We're comparing the IndexInfo built within DefineIndex itself to one made from existing catalog contents by BuildIndexInfo. Notably, while BuildIndexInfo will run index expressions and predicates through expression preprocessing, that has not happened to DefineIndex's struct. The result is failure to match and subsequent creation of duplicate indexes. The easiest and most bulletproof fix is to build a new IndexInfo using BuildIndexInfo, thereby guaranteeing that the processing done is identical. While here, let's also extract the opfamily and collation data from the new partitioned index, removing ad-hoc logic that duplicated knowledge about how those are constructed. Per report from Christophe Pettus. Back-patch to v11 where we invented partitioned indexes. Richard Guo and Tom Lane Discussion: https://postgr.es/m/8864BFAA-81FD-4BF9-8E06-7DEB8D4164ED@thebuild.com
1 parent 239c3ee commit 3bfea5c

File tree

3 files changed

+96
-8
lines changed

3 files changed

+96
-8
lines changed

‎src/backend/commands/indexcmds.c

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1222,18 +1222,27 @@ DefineIndex(Oid relationId,
12221222
int nparts = partdesc->nparts;
12231223
Oid *part_oids = palloc(sizeof(Oid) * nparts);
12241224
bool invalidate_parent = false;
1225+
Relation parentIndex;
12251226
TupleDesc parentDesc;
1226-
Oid *opfamOids;
12271227

12281228
pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_TOTAL,
12291229
nparts);
12301230

1231+
/* Make a local copy of partdesc->oids[], just for safety */
12311232
memcpy(part_oids, partdesc->oids, sizeof(Oid) * nparts);
12321233

1234+
/*
1235+
* We'll need an IndexInfo describing the parent index. The one
1236+
* built above is almost good enough, but not quite, because (for
1237+
* example) its predicate expression if any hasn't been through
1238+
* expression preprocessing. The most reliable way to get an
1239+
* IndexInfo that will match those for child indexes is to build
1240+
* it the same way, using BuildIndexInfo().
1241+
*/
1242+
parentIndex = index_open(indexRelationId, lockmode);
1243+
indexInfo = BuildIndexInfo(parentIndex);
1244+
12331245
parentDesc = RelationGetDescr(rel);
1234-
opfamOids = palloc(sizeof(Oid) * numberOfKeyAttributes);
1235-
for (i = 0; i < numberOfKeyAttributes; i++)
1236-
opfamOids[i] = get_opclass_family(classObjectId[i]);
12371246

12381247
/*
12391248
* For each partition, scan all existing indexes; if one matches
@@ -1304,9 +1313,9 @@ DefineIndex(Oid relationId,
13041313
cldIdxInfo = BuildIndexInfo(cldidx);
13051314
if (CompareIndexInfo(cldIdxInfo, indexInfo,
13061315
cldidx->rd_indcollation,
1307-
collationObjectId,
1316+
parentIndex->rd_indcollation,
13081317
cldidx->rd_opfamily,
1309-
opfamOids,
1318+
parentIndex->rd_opfamily,
13101319
attmap))
13111320
{
13121321
Oid cldConstrOid = InvalidOid;
@@ -1433,6 +1442,8 @@ DefineIndex(Oid relationId,
14331442
free_attrmap(attmap);
14341443
}
14351444

1445+
index_close(parentIndex, lockmode);
1446+
14361447
/*
14371448
* The pg_index row we inserted for this index was marked
14381449
* indisvalid=true. But if we attached an existing index that is

‎src/test/regress/expected/indexing.out

Lines changed: 65 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -377,7 +377,7 @@ drop table idxpart;
377377
-- When a table is attached a partition and it already has an index, a
378378
-- duplicate index should not get created, but rather the index becomes
379379
-- attached to the parent's index.
380-
create table idxpart (a int, b int, c text) partition by range (a);
380+
create table idxpart (a int, b int, c text, d bool) partition by range (a);
381381
create index idxparti on idxpart (a);
382382
create index idxparti2 on idxpart (b, c);
383383
create table idxpart1 (like idxpart including indexes);
@@ -388,6 +388,7 @@ create table idxpart1 (like idxpart including indexes);
388388
a | integer | | |
389389
b | integer | | |
390390
c | text | | |
391+
d | boolean | | |
391392
Indexes:
392393
"idxpart1_a_idx" btree (a)
393394
"idxpart1_b_c_idx" btree (b, c)
@@ -414,6 +415,7 @@ alter table idxpart attach partition idxpart1 for values from (0) to (10);
414415
a | integer | | |
415416
b | integer | | |
416417
c | text | | |
418+
d | boolean | | |
417419
Partition of: idxpart FOR VALUES FROM (0) TO (10)
418420
Indexes:
419421
"idxpart1_a_idx" btree (a)
@@ -433,6 +435,68 @@ select relname, relkind, inhparent::regclass
433435
idxparti2 | I |
434436
(6 rows)
435437

438+
-- While here, also check matching when creating an index after the fact.
439+
create index on idxpart1 ((a+b)) where d = true;
440+
\d idxpart1
441+
Table "public.idxpart1"
442+
Column | Type | Collation | Nullable | Default
443+
--------+---------+-----------+----------+---------
444+
a | integer | | |
445+
b | integer | | |
446+
c | text | | |
447+
d | boolean | | |
448+
Partition of: idxpart FOR VALUES FROM (0) TO (10)
449+
Indexes:
450+
"idxpart1_a_idx" btree (a)
451+
"idxpart1_b_c_idx" btree (b, c)
452+
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
453+
454+
select relname, relkind, inhparent::regclass
455+
from pg_class left join pg_index ix on (indexrelid = oid)
456+
left join pg_inherits on (ix.indexrelid = inhrelid)
457+
where relname like 'idxpart%' order by relname;
458+
relname | relkind | inhparent
459+
-------------------+---------+-----------
460+
idxpart | p |
461+
idxpart1 | r |
462+
idxpart1_a_idx | i | idxparti
463+
idxpart1_b_c_idx | i | idxparti2
464+
idxpart1_expr_idx | i |
465+
idxparti | I |
466+
idxparti2 | I |
467+
(7 rows)
468+
469+
create index idxparti3 on idxpart ((a+b)) where d = true;
470+
\d idxpart1
471+
Table "public.idxpart1"
472+
Column | Type | Collation | Nullable | Default
473+
--------+---------+-----------+----------+---------
474+
a | integer | | |
475+
b | integer | | |
476+
c | text | | |
477+
d | boolean | | |
478+
Partition of: idxpart FOR VALUES FROM (0) TO (10)
479+
Indexes:
480+
"idxpart1_a_idx" btree (a)
481+
"idxpart1_b_c_idx" btree (b, c)
482+
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
483+
484+
select relname, relkind, inhparent::regclass
485+
from pg_class left join pg_index ix on (indexrelid = oid)
486+
left join pg_inherits on (ix.indexrelid = inhrelid)
487+
where relname like 'idxpart%' order by relname;
488+
relname | relkind | inhparent
489+
-------------------+---------+-----------
490+
idxpart | p |
491+
idxpart1 | r |
492+
idxpart1_a_idx | i | idxparti
493+
idxpart1_b_c_idx | i | idxparti2
494+
idxpart1_expr_idx | i | idxparti3
495+
idxparti | I |
496+
idxparti2 | I |
497+
idxparti3 | I |
498+
(8 rows)
499+
436500
drop table idxpart;
437501
-- Verify that attaching an invalid index does not mark the parent index valid.
438502
-- On the other hand, attaching a valid index marks not only its direct

‎src/test/regress/sql/indexing.sql

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -192,7 +192,7 @@ drop table idxpart;
192192
-- When a table is attached a partition and it already has an index, a
193193
-- duplicate index should not get created, but rather the index becomes
194194
-- attached to the parent's index.
195-
create table idxpart (a int, b int, c text) partition by range (a);
195+
create table idxpart (a int, b int, c text, d bool) partition by range (a);
196196
create index idxparti on idxpart (a);
197197
create index idxparti2 on idxpart (b, c);
198198
create table idxpart1 (like idxpart including indexes);
@@ -203,6 +203,19 @@ select relname, relkind, inhparent::regclass
203203
where relname like 'idxpart%' order by relname;
204204
alter table idxpart attach partition idxpart1 for values from (0) to (10);
205205
\d idxpart1
206+
select relname, relkind, inhparent::regclass
207+
from pg_class left join pg_index ix on (indexrelid = oid)
208+
left join pg_inherits on (ix.indexrelid = inhrelid)
209+
where relname like 'idxpart%' order by relname;
210+
-- While here, also check matching when creating an index after the fact.
211+
create index on idxpart1 ((a+b)) where d = true;
212+
\d idxpart1
213+
select relname, relkind, inhparent::regclass
214+
from pg_class left join pg_index ix on (indexrelid = oid)
215+
left join pg_inherits on (ix.indexrelid = inhrelid)
216+
where relname like 'idxpart%' order by relname;
217+
create index idxparti3 on idxpart ((a+b)) where d = true;
218+
\d idxpart1
206219
select relname, relkind, inhparent::regclass
207220
from pg_class left join pg_index ix on (indexrelid = oid)
208221
left join pg_inherits on (ix.indexrelid = inhrelid)

0 commit comments

Comments
 (0)