While working on FKs pointing to partitioned tables, I noticed that in
PG11 we fail to produce a working dump in the case of a partitioned
table that doesn't have partitions.
The attached patch fixes that. In doing so, it breaks a test ... and
analyzing that, it turns out that the test was broken, it wasn't testing
what it was supposed to be testing. I patched it so that it continues
to work (and now it tests the correct thing). To be precise, the test
was doing this:
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 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);
with the expectation that the partition would not have a proper index on
which to run an INSERT INTO parted_conflict_1 ON CONFLICT (a), expecting
that partition parted_conflict_1_1 would not have the index. But in
reality parted_conflict_1_1 does have the index (and it only fails
because the index in its parent is marked invalid). So the patch moves
the CREATE TABLE parted_conflict_1_1 to before the indexes creation, so
that the partition really does not have the index, and then it gets the
expected error.
If you were to run the pg_upgrade test with my fks-to-partitioned-tables
patch, it will fail because of a PK on an partitionless partitioned
table being marked invalid after restore; but if you run it after
applying this patch, it should work (it works for me).
--
Álvaro Herrera 39°50'S 73°21'W
commit 444e92a363bafc65dde710d3c21a5f0b25b308e1[m
Author: Alvaro Herrera <alvhe...@alvh.no-ip.org>
AuthorDate: Mon Dec 3 19:30:37 2018 -0300
CommitDate: Mon Dec 3 19:31:04 2018 -0300
Don't mark partitioned indexes invalid unnecessarily
If the partitioned table doesn't have partitions, then an index created
with CREATE INDEX/ON ONLY doesn't have to be marked as invalid ... and
doing so breaks pg_dump for those tables, so don't.
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;
