From 14f67a8ee282ebc0de78e773fbd597f460ab4a54 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Thu, 5 Oct 2017 13:06:46 -0400 Subject: [PATCH] On attach, consider skipping validation of subpartitions individually. If the table attached as a partition is itself partitioned, individual partitions might have constraints strong enough to skip scanning the table even if the table actually attached does not. This is pretty cheap to check, and possibly a big win if it works out. Amit Langote, with test case changes by me. Discussion: http://postgr.es/m/1f08b844-0078-aa8d-452e-7af3bf77d05f@lab.ntt.co.jp --- src/backend/commands/tablecmds.c | 15 +++++++++++++++ src/test/regress/expected/alter_table.out | 14 ++++++++++++++ src/test/regress/sql/alter_table.sql | 14 ++++++++++++++ 3 files changed, 43 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d90c739952..2d4dcd7556 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -13683,6 +13683,21 @@ ValidatePartitionConstraints(List **wqueue, Relation scanrel, /* There can never be a whole-row reference here */ if (found_whole_row) elog(ERROR, "unexpected whole-row reference found in partition key"); + + /* Can we skip scanning this part_rel? */ + if (PartConstraintImpliedByRelConstraint(part_rel, my_partconstr)) + { + if (!validate_default) + ereport(INFO, + (errmsg("partition constraint for table \"%s\" is implied by existing constraints", + RelationGetRelationName(part_rel)))); + else + ereport(INFO, + (errmsg("updated partition constraint for default partition \"%s\" is implied by existing constraints", + RelationGetRelationName(part_rel)))); + heap_close(part_rel, NoLock); + continue; + } } /* Grab a work queue entry. */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 807eb913f6..cda8ce556c 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3474,6 +3474,20 @@ DETAIL: "part_5" is already a child of "list_parted2". ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); ERROR: circular inheritance not allowed DETAIL: "list_parted2" is already a child of "list_parted2". +-- If the partitioned table being attached does not have a constraint that +-- would allow validation scan to be skipped, but an individual partition +-- does, then the partition's validation scan is skipped. +CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); +CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b); +CREATE TABLE quuux_default1 PARTITION OF quuux_default ( + CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1) +) FOR VALUES IN ('b'); +CREATE TABLE quuux1 (a int, b text); +ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! +CREATE TABLE quuux2 (a int, b text); +ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation +INFO: updated partition constraint for default partition "quuux_default1" is implied by existing constraints +DROP TABLE quuux; -- -- DETACH PARTITION -- diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 37cca72620..d0daacf3e9 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2285,6 +2285,20 @@ ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); +-- If the partitioned table being attached does not have a constraint that +-- would allow validation scan to be skipped, but an individual partition +-- does, then the partition's validation scan is skipped. +CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a); +CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b); +CREATE TABLE quuux_default1 PARTITION OF quuux_default ( + CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1) +) FOR VALUES IN ('b'); +CREATE TABLE quuux1 (a int, b text); +ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1); -- validate! +CREATE TABLE quuux2 (a int, b text); +ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2); -- skip validation +DROP TABLE quuux; + -- -- DETACH PARTITION -- -- 2.40.0