From 6f6b99d1335be8ea1b74581fc489a97b109dd08a Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 8 Sep 2017 17:28:04 -0400 Subject: Allow a partitioned table to have a default partition. Any tuples that don't route to any other partition will route to the default partition. Jeevan Ladhe, Beena Emerson, Ashutosh Bapat, Rahila Syed, and Robert Haas, with review and testing at various stages by (at least) Rushabh Lathia, Keith Fiske, Amit Langote, Amul Sul, Rajkumar Raghuanshi, Sven Kunze, Kyotaro Horiguchi, Thom Brown, Rafia Sabih, and Dilip Kumar. Discussion: http://postgr.es/m/CAH2L28tbN4SYyhS7YV1YBWcitkqbhSWfQCy0G=apRcC_PEO-bg@mail.gmail.com Discussion: http://postgr.es/m/CAOG9ApEYj34fWMcvBMBQ-YtqR9fTdXhdN82QEKG0SVZ6zeL1xg@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 11 +++++++++++ doc/src/sgml/ref/alter_table.sgml | 31 ++++++++++++++++++++++++++++--- doc/src/sgml/ref/create_table.sgml | 35 ++++++++++++++++++++++++++++++++--- 3 files changed, 71 insertions(+), 6 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 4f56188a1cf..4978b47f0e4 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4738,6 +4738,17 @@ SCRAM-SHA-256$<iteration count>:<salt>< The number of columns in partition key + + partdefid + oid + pg_class.oid + + The OID of the pg_class entry for the default partition + of this partitioned table, or zero if this partitioned table does not + have a default partition. + + + partattrs int2vector diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index dae63077eed..0fb385ece7a 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -34,7 +34,7 @@ ALTER TABLE [ IF EXISTS ] name ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] ALTER TABLE [ IF EXISTS ] name - ATTACH PARTITION partition_name FOR VALUES partition_bound_spec + ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } ALTER TABLE [ IF EXISTS ] name DETACH PARTITION partition_name @@ -765,11 +765,18 @@ ALTER TABLE [ IF EXISTS ] name - ATTACH PARTITION partition_name FOR VALUES partition_bound_spec + ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } This form attaches an existing table (which might itself be partitioned) - as a partition of the target table using the same syntax for + as a partition of the target table. The table can be attached + as a partition for specific values using FOR VALUES + or as a default partition by using DEFAULT + . + + + + A partition using FOR VALUES uses same syntax for partition_bound_spec as . The partition bound specification must correspond to the partitioning strategy and partition key of the @@ -806,6 +813,17 @@ ALTER TABLE [ IF EXISTS ] name (See the discussion in about constraints on the foreign table.) + + + When a table has a default partition, defining a new partition changes + the partition constraint for the default partition. The default + partition can't contain any rows that would need to be moved to the new + partition, and will be scanned to verify that none are present. This + scan, like the scan of the new partition, can be avoided if an + appropriate CHECK constraint is present. Also like + the scan of the new partition, it is always skipped when the default + partition is a foreign table. + @@ -1395,6 +1413,13 @@ ALTER TABLE cities ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b'); + + Attach a default partition to a partitioned table: + +ALTER TABLE cities + ATTACH PARTITION cities_partdef DEFAULT; + + Detach a partition from partitioned table: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a6ca5902499..824253de404 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -49,7 +49,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | table_constraint } [, ... ] -) ] FOR VALUES partition_bound_spec +) ] { FOR VALUES partition_bound_spec | DEFAULT } [ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] @@ -250,11 +250,13 @@ FROM ( { numeric_literal | - PARTITION OF parent_table FOR VALUES partition_bound_spec + PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT } Creates the table as a partition of the specified - parent table. + parent table. The table can be created either as a partition for specific + values using FOR VALUES or as a default partition + using DEFAULT. @@ -342,6 +344,26 @@ FROM ( { numeric_literal | + + If DEFAULT is specified, the table will be + created as a default partition of the parent table. The parent can + either be a list or range partitioned table. A partition key value + not fitting into any other partition of the given parent will be + routed to the default partition. There can be only one default + partition for a given parent table. + + + + When a table has an existing DEFAULT partition and + a new partition is added to it, the existing default partition must + be scanned to verify that it does not contain any rows which properly + belong in the new partition. If the default partition contains a + large number of rows, this may be slow. The scan will be skipped if + the default partition is a foreign table or if it has a constraint which + proves that it cannot contain rows which should be placed in the new + partition. + + A partition must have the same column names and types as the partitioned table to which it belongs. If the parent is specified WITH @@ -1679,6 +1701,13 @@ CREATE TABLE cities_ab CREATE TABLE cities_ab_10000_to_100000 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000); + + + Create a default partition: + +CREATE TABLE cities_partdef + PARTITION OF cities DEFAULT; + -- cgit v1.2.3