Re: 回复：how to create index concurrently on partitioned table

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.

On Tue, Jun 16, 2020 at 10:02:21AM +0900, Michael Paquier wrote:
> CIC is an operation that exists while allowing read and writes to
> still happen in parallel, so that's fine IMO if it takes time. Now it
> is true that it may not scale well so we should be careful with the
> approach taken. Also, I think that the case of REINDEX would require
> less work overall because we already have some code in place to gather
> multiple indexes from one or more relations and work on these
> consistently, all at once.

I'm not sure if by reindex you mean my old 0002. That's now 0001, so if it can
be simplified somehow, that's great..

That gave me the idea to layer CIC on top of Reindex, since I think it does
exactly what's needed.

--
Justin