Re: BUG #16634: Conflicting names of indexes for partitioned tables

I just noticed that I have made a typo in second CREATE INDEX statement. It
should be
CREATE INDEX parent_lower_surname_idx ON parent (LOWER(surname))
So the first index is on the `name` field and the second one is on
`surname`.
Regards,

Michał

czw., 24 wrz 2020 o 22:26 Michał Albrycht <michalalbrycht(at)gmail(dot)com>
napisał(a):

> I don't understand why you say that it has nothing to do with
> partitioning. From my perspective it has a lot to do with partitioning. On
> a non-partitioned table I can create 2 indexes in parallel without any
> problems. But for partitioned tables I have to know how Postgres is
> generating names for indexes on partitions, know that it is not good at
> generating unique names and check all my indexes to figure out whether I
> can create them in parallel or not. Of course you can say that you
> shouldn't create objects in parallel, but I was inspired by pg_repack which
> does exactly this.
>
> Regards,
>
> Michał Albrycht
>
> czw., 24 wrz 2020 o 16:08 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):
>
>> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>> > I think that whole problem is caused by auto-generated name for index
>> for
>> > partition tables. For parent table postgres is using name provided by
>> user,
>> > but for children tables it generates names automatically from name of
>> the
>> > table and fields that are used for index. But when we create index on
>> > function it uses just function name. So for both indexes it generates
>> name
>> > like `child_1_lower_idx` despite the fact that one index is using
>> > `lower(name)` and second one is using `lower(surname)`.
>> > Why did it work with single thread? Apparently there is mechanism that
>> tries
>> > to solve conflicts as first index will get name: child_1_lower_idx and
>> > second one will get child_1_lower_idx1 but for some reason this will not
>> > work when indexes are created in parallel sessions.
>>
>> Well, it won't work reliably anyway; that's inherent to the problem.
>> There's nothing here that's specific to partitioned tables, and I'm
>> afraid the answer is "don't do that". It's not very hard to find
>> other examples where parallel creation of objects can hit conflicts.
>>
>> regards, tom lane
>>
>