Re: CREATE INDEX CONCURRENTLY on partitioned index

On 28.05.2024 07:05, Alexander Pyhalov wrote:
> Ilya Gladyshev писал(а) 2024-05-28 02:52:
>
>>> Also I'd like to note that in new patch version there's a strange
>>> wording in documentation:
>>>
>>> "This can be very convenient as not only will all existing
>>> partitions be
>>>  indexed, but any future partitions will be as well.
>>>  <command>CREATE INDEX ... CONCURRENTLY</command> can incur long
>>> lock times
>>>  on huge partitioned tables, to avoid that you can
>>>  use <command>CREATE INDEX ON ONLY</command> the partitioned table,
>>> which
>>>  creates the new index marked as invalid, preventing automatic
>>> application
>>>  to existing partitions."
>>>
>>> All the point of CIC is to avoid long lock times. So it seems this
>>> paragraph should be rewritten in the following way:
>>>
>>> "To avoid long lock times, you can use CREATE INDEX CONCURRENTLY or
>>> CREATE INDEX ON ONLY</command> the partitioned table..."
>>
>>
>> True, the current wording doesn't look right. Right now CREATE INDEX
>> ON ONLY is described as a workaround for the missing CIC. I think it
>> rather makes sense to say that it gives more fine-grained control of
>> partition locking than both CIC and ordinary CREATE INDEX. See the
>> updated patch.
>
> Hi.
>
> Not sure if it's worth removing mentioning of CIC in
>
>       creates the new index marked as invalid, preventing automatic
> application
>       to existing partitions.  Instead, indexes can then be created
> individually
> -     on each partition using <literal>CONCURRENTLY</literal> and
> +     on each partition and
>       <firstterm>attached</firstterm> to the partitioned index on the
> parent
>       using <command>ALTER INDEX ... ATTACH PARTITION</command>.  Once
> indexes for
>       all the partitions are attached to the parent index, the parent
> index will
>
> but at least now it looks better.

The current patch version locks all the partitions in the first
transaction up until each of them is built, which makes for long lock
times for partitions that are built last. Having looked at the
implementation of REINDEX CONCURRENTLY for partitioned tables, I think
we can improve this by using the same approach of just skipping the
relations that we find out are dropped when trying to lock them.
Incidentally, this implementation in the new patch version is also simpler.

In addition, I noticed that progress tracking is once again broken for
partitioned tables, while looking at REINDEX implementation, attaching
the second patch to fix it.