From 8b08f7d4820fd7a8ef6152a9dd8c6e3cb01e5f99 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 19 Jan 2018 11:49:22 -0300 Subject: Local partitioned indexes When CREATE INDEX is run on a partitioned table, create catalog entries for an index on the partitioned table (which is just a placeholder since the table proper has no data of its own), and recurse to create actual indexes on the existing partitions; create them in future partitions also. As a convenience gadget, if the new index definition matches some existing index in partitions, these are picked up and used instead of creating new ones. Whichever way these indexes come about, they become attached to the index on the parent table and are dropped alongside it, and cannot be dropped on isolation unless they are detached first. To support pg_dump'ing these indexes, add commands CREATE INDEX ON ONLY (which creates the index on the parent partitioned table, without recursing) and ALTER INDEX ATTACH PARTITION (which is used after the indexes have been created individually on each partition, to attach them to the parent index). These reconstruct prior database state exactly. Reviewed-by: (in alphabetical order) Peter Eisentraut, Robert Haas, Amit Langote, Jesper Pedersen, Simon Riggs, David Rowley Discussion: https://postgr.es/m/20171113170646.gzweigyrgg6pwsg4@alvherre.pgsql --- doc/src/sgml/catalogs.sgml | 23 +++++++++++++++++++++++ doc/src/sgml/ref/alter_index.sgml | 14 ++++++++++++++ doc/src/sgml/ref/alter_table.sgml | 8 ++++++-- doc/src/sgml/ref/create_index.sgml | 33 ++++++++++++++++++++++++++++++++- doc/src/sgml/ref/reindex.sgml | 5 +++++ 5 files changed, 80 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 3f02202cafb..71e20f27409 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2995,6 +2995,29 @@ SCRAM-SHA-256$<iteration count>:&l + + DEPENDENCY_INTERNAL_AUTO (I) + + + The dependent object was created as part of creation of the + referenced object, and is really just a part of its internal + implementation. A DROP of the dependent object + will be disallowed outright (we'll tell the user to issue a + DROP against the referenced object, instead). + While a regular internal dependency will prevent + the dependent object from being dropped while any such dependencies + remain, DEPENDENCY_INTERNAL_AUTO will allow such + a drop as long as the object can be found by following any of such + dependencies. + Example: an index on a partition is made internal-auto-dependent on + both the partition itself as well as on the index on the parent + partitioned table; so the partition index is dropped together with + either the partition it indexes, or with the parent index it is + attached to. + + + + DEPENDENCY_EXTENSION (e) diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index e54237272c6..c0606689f06 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation ALTER INDEX [ IF EXISTS ] name RENAME TO new_name ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name +ALTER INDEX name ATTACH PARTITION index_name ALTER INDEX name DEPENDS ON EXTENSION extension_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) @@ -75,6 +76,19 @@ ALTER INDEX ALL IN TABLESPACE name + + ATTACH PARTITION + + + Causes the named index to become attached to the altered index. + The named index must be on a partition of the table containing the + index being altered, and have an equivalent definition. An attached + index cannot be dropped by itself, and will automatically be dropped + if its parent index is dropped. + + + + DEPENDS ON EXTENSION diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 686bb2c11c5..286c7a85897 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -805,7 +805,10 @@ ALTER TABLE [ IF EXISTS ] name 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 - . + . For each index in the target table, a corresponding + one will be created in the attached table; or, if an equivalent + index already exists, will be attached to the target table's index, + as if ALTER INDEX ATTACH PARTITION had been executed. @@ -866,7 +869,8 @@ ALTER TABLE [ IF EXISTS ] name This form detaches specified partition of the target table. The detached partition continues to exist as a standalone table, but no longer has any - ties to the table from which it was detached. + ties to the table from which it was detached. Any indexes that were + attached to the target table's indexes are detached. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 025537575b8..5137fe63832 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ] +CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace_name ] @@ -151,6 +151,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + + ONLY + + + Indicates not to recurse creating indexes on partitions, if the + table is partitioned. The default is to recurse. + + + + table_name @@ -545,6 +555,27 @@ Indexes: linkend="xindex"/>. + + When CREATE INDEX is invoked on a partitioned + table, the default behavior is to recurse to all partitions to ensure + they all have matching indexes. + Each partition is first checked to determine whether an equivalent + index already exists, and if so, that index will become attached as a + partition index to the index being created, which will become its + parent index. + If no matching index exists, a new index will be created and + automatically attached; the name of the new index in each partition + will be determined as if no index name had been specified in the + command. + If the ONLY option is specified, no recursion + is done, and the index is marked invalid + (ALTER INDEX ... ATTACH PARTITION turns the index + valid, once all partitions acquire the index.) Note, however, that + any partition that is created in the future using + CREATE TABLE ... PARTITION OF will automatically + contain the index regardless of whether this option was specified. + + For index methods that support ordered scans (currently, only B-tree), the optional clauses ASC, DESC, NULLS diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 79f6931c6a2..1c21fafb80e 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -231,6 +231,11 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } + + Reindexing partitioned tables or partitioned indexes is not supported. + Each individual partition can be reindexed separately instead. + + -- cgit v1.2.3