Date: Sun, 29 Nov 2020 14:22:21 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: =?utf-8?B?5p2O5p2wKOaFjui/vSk=?= <adger.lj@alibaba-inc.com>,
	pgsql-hackers <pgsql-hackers@lists.postgresql.org>,
	=?utf-8?B?5pu+5paH5peMKOS5ieS7jik=?= <wenjing.zwj@alibaba-inc.com>,
	Alvaro Herrera <alvherre@2ndquadrant.com>
Subject: Re: CREATE INDEX CONCURRENTLY on partitioned index
Message-ID: <20201129202221.GA9477@telsasoft.com>
References: <20201031063117.GF3080@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="cWoXeonUoKmBZSoM"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20201031063117.GF3080@telsasoft.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--cWoXeonUoKmBZSoM
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Sat, Oct 31, 2020 at 01:31:17AM -0500, Justin Pryzby wrote:
> Forking this thread, since the existing CFs have been closed.
> https://www.postgresql.org/message-id/flat/20200914143102.GX18552%40telsasoft.com#58b1056488451f8594b0f0ba40996afd
> 
> The strategy is to create catalog entries for all tables with indisvalid=false,
> and then process them like REINDEX CONCURRENTLY.  If it's interrupted, it
> leaves INVALID indexes, which can be cleaned up with DROP or REINDEX, same as
> CIC on a plain table.
> 
> On Sat, Aug 08, 2020 at 01:37:44AM -0500, Justin Pryzby wrote:
> > On Mon, Jun 15, 2020 at 09:37:42PM +0900, Michael Paquier wrote:
> > > On Mon, Jun 15, 2020 at 08:15:05PM +0800, 李杰(慎追) wrote:
> > > > As shown above, an error occurred while creating an index in the second partition. 
> > > > It can be clearly seen that the index of the partitioned table is invalid 
> > > > and the index of the first partition is normal, the second partition is invalid, 
> > > > and the Third Partition index does not exist at all.
> > > 
> > > That's a problem.  I really think that we should make the steps of the
> > > concurrent operation consistent across all relations, meaning that all
> > > the indexes should be created as invalid for all the parts of the
> > > partition tree, including partitioned tables as well as their
> > > partitions, in the same transaction.  Then a second new transaction
> > > gets used for the index build, followed by a third one for the
> > > validation that switches the indexes to become valid.
> > 
> > Note that the mentioned problem wasn't serious: there was missing index on
> > child table, therefor the parent index was invalid, as intended.  However I
> > agree that it's not nice that the command can fail so easily and leave behind
> > some indexes created successfully and some failed some not created at all.
> > 
> > But I took your advice initially creating invalid inds.
> ...
> > That gave me the idea to layer CIC on top of Reindex, since I think it does
> > exactly what's needed.
> 
> On Sat, Sep 26, 2020 at 02:56:55PM -0500, Justin Pryzby wrote:
> > On Thu, Sep 24, 2020 at 05:11:03PM +0900, Michael Paquier wrote:
> > > It would be good also to check if
> > > we have a partition index tree that maps partially with a partition
> > > table tree (aka no all table partitions have a partition index), where
> > > these don't get clustered because there is no index to work on.
> > 
> > This should not happen, since a incomplete partitioned index is "invalid".

@cfbot: rebased over recent changes to indexcmds.c

-- 
Justin

--cWoXeonUoKmBZSoM
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v11-0001-Allow-CREATE-INDEX-CONCURRENTLY-on-partitioned-t.patch"