From 37798a8e83db978c7d92f2491935c6ba96b95fbc Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 5 Dec 2018 13:31:55 -0300 Subject: [PATCH] Don't mark partitioned indexes invalid unnecessarily MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit When an indexes is created on a partitioned table using ONLY (don't recurse to partitions), it gets marked invalid until index partitions are attached for each table partition. But there's no reason to do this if there are no partitions ... and moreover, there's no way to get the index to become valid afterwards, because all partitions that get created/attached get their own index partition already attached to the parent index, so there's no chance to do ALTER INDEX ... ATTACH PARTITION that would make the parent index valid. Fix by not marking the index as invalid to begin with. This is very similar to 9139aa19423b, but the pg_dump aspect does not appear to be relevant until we add FKs that can point to PKs on partitioned tables. (I tried to cause the pg_upgrade test to break by leaving some of these bogus tables around, but wasn't able to.) Making this change means that an index that was supposed to be invalid in the insert_conflict regression test is no longer invalid; reorder the DDL so that the test continues to verify the behavior we want it to. Author: Álvaro Herrera Reviewed-by: Amit Langote Discussion: https://postgr.es/m/20181203225019.2vvdef2ybnkxt364@alvherre.pgsql --- src/backend/commands/indexcmds.c | 12 +++++++++++- src/test/regress/expected/insert_conflict.out | 2 +- src/test/regress/sql/insert_conflict.sql | 2 +- 3 files changed, 13 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 3975f62c00..965b9f0d23 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -834,8 +834,18 @@ DefineIndex(Oid relationId, flags |= INDEX_CREATE_PARTITIONED; if (stmt->primary) flags |= INDEX_CREATE_IS_PRIMARY; + + /* + * If the table is partitioned, and recursion was declined but partitions + * exist, mark the index as invalid. + */ if (partitioned && stmt->relation && !stmt->relation->inh) - flags |= INDEX_CREATE_INVALID; + { + PartitionDesc pd = RelationGetPartitionDesc(rel); + + if (pd->nparts != 0) + flags |= INDEX_CREATE_INVALID; + } if (stmt->deferrable) constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE; diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 27cf5a01b3..a28611745c 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -876,10 +876,10 @@ drop table parted_conflict; -- partition create table parted_conflict (a int, b text) partition by range (a); create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); +create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); create unique index on only parted_conflict_1 (a); create unique index on only parted_conflict (a); alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx; -create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); insert into parted_conflict values (40, 'forty'); insert into parted_conflict_1 values (40, 'cuarenta') on conflict (a) do update set b = excluded.b; diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index c677d70fb7..c68013e179 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -551,10 +551,10 @@ drop table parted_conflict; -- partition create table parted_conflict (a int, b text) partition by range (a); create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000) partition by range (a); +create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); create unique index on only parted_conflict_1 (a); create unique index on only parted_conflict (a); alter index parted_conflict_a_idx attach partition parted_conflict_1_a_idx; -create table parted_conflict_1_1 partition of parted_conflict_1 for values from (0) to (500); insert into parted_conflict values (40, 'forty'); insert into parted_conflict_1 values (40, 'cuarenta') on conflict (a) do update set b = excluded.b; -- 2.40.0