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 <table>
(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

5 files changed, 80 insertions, 3 deletions

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
@@ -2996,6 +2996,29 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
     </varlistentry>
 
     <varlistentry>
+     <term><symbol>DEPENDENCY_INTERNAL_AUTO</symbol> (<literal>I</literal>)</term>
+     <listitem>
+      <para>
+       The dependent object was created as part of creation of the
+       referenced object, and is really just a part of its internal
+       implementation.  A <command>DROP</command> of the dependent object
+       will be disallowed outright (we'll tell the user to issue a
+       <command>DROP</command> against the referenced object, instead).
+       While a regular internal dependency will prevent
+       the dependent object from being dropped while any such dependencies
+       remain, <literal>DEPENDENCY_INTERNAL_AUTO</literal> 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.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
      <term><symbol>DEPENDENCY_EXTENSION</symbol> (<literal>e</literal>)</term>
      <listitem>
       <para>
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
 <synopsis>
 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
+ALTER INDEX <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">index_name</replaceable>
 ALTER INDEX <replaceable class="parameter">name</replaceable> DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">storage_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
 ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
@@ -76,6 +77,19 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
    </varlistentry>
 
    <varlistentry>
+    <term><literal>ATTACH PARTITION</literal></term>
+    <listitem>
+     <para>
+      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.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>DEPENDS ON EXTENSION</literal></term>
     <listitem>
      <para>
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 ] <replaceable class="parameter">name</replaceable>
       as a partition of the target table. The table can be attached
       as a partition for specific values using <literal>FOR VALUES
       </literal> or as a default partition by using <literal>DEFAULT
-      </literal>.
+      </literal>.  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 <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
      </para>
 
      <para>
@@ -866,7 +869,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
      <para>
       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.
      </para>
     </listitem>
    </varlistentry>
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
 
  <refsynopsisdiv>
 <synopsis>
-CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
+CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
     ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
     [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ]
     [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
@@ -152,6 +152,16 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
      </varlistentry>
 
      <varlistentry>
+      <term><literal>ONLY</literal></term>
+      <listitem>
+       <para>
+        Indicates not to recurse creating indexes on partitions, if the
+        table is partitioned.  The default is to recurse.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><replaceable class="parameter">table_name</replaceable></term>
       <listitem>
        <para>
@@ -546,6 +556,27 @@ Indexes:
   </para>
 
   <para>
+   When <literal>CREATE INDEX</literal> 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 <literal>ONLY</literal> option is specified, no recursion
+   is done, and the index is marked invalid
+   (<command>ALTER INDEX ... ATTACH PARTITION</command> turns the index
+   valid, once all partitions acquire the index.)  Note, however, that
+   any partition that is created in the future using
+   <command>CREATE TABLE ... PARTITION OF</command> will automatically
+   contain the index regardless of whether this option was specified.
+  </para>
+
+  <para>
    For index methods that support ordered scans (currently, only B-tree),
    the optional clauses <literal>ASC</literal>, <literal>DESC</literal>, <literal>NULLS
    FIRST</literal>, and/or <literal>NULLS LAST</literal> can be specified to modify
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 } <replacea
    reindex anything.
   </para>
 
+  <para>
+   Reindexing partitioned tables or partitioned indexes is not supported.
+   Each individual partition can be reindexed separately instead.
+  </para>
+
  </refsect1>
 
  <refsect1>