Date: Mon, 18 Dec 2017 13:02:30 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>,
	Maksim Milyutin <milyutinma@gmail.com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Message-ID: <20171218160230.hdq2k23hheud6arv@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmobJJwKeBON4H2kb3pnh0-mtJ_MuzOvYCeOgh0w2k_7TCw@mail.gmail.com>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

After this discussion, this is how I see things working:

1. pg_dump
   a) creates indexes on partitions normally
   b) once all existing indexes are done, index on parent is created,
      with ONLY.  No cascading occurs, no indexes are attached.
   c) ATTACH is run for each existing partition index.  After each
      ATTACH, we check that all indexes exist.  If so, the parent is
      marked valid.
   d) if not all indexes existed in partitions, index on parent remains
      invalid.  (It was invalid in the dumped database, so this is
      correct.)

2. other uses
   Normal CREATE INDEX (without ONLY) recurses and attaches the first
   matching index it finds (no duplicate indexes are created);
   partitions without a matching index get one created.

3. ALTER INDEX DETACH is not provided.  Therefore: once index is valid,
   it remains so forever.

I think this satisfies all concerns.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services