Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.

Dilip Kumar <dilipbalaut(at)gmail(dot)com> writes:
> On Sat, Jun 14, 2025 at 3:15 PM PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
>> If expressions or parent index names differ, partition-level index names
>> should be derived deterministically from:
>> * Parent index name (preferred) eg.: parent_idx_name_partition1
>> * Or a hash of the expression (as fallback)
>> This would avoid internal naming collisions and allow safe concurrent
>> execution of CREATE INDEX IF NOT EXISTS on partitioned tables.

> It seems beneficial to embed the parent index name within the names of
> its partitioned child indexes, although it would become tricky when
> building an index for a multi level partition hierarchy but we could
> simplify this by only referencing the top-level user-provided index
> name. This is my perspective, and I'm open to other ideas.

This seems very closely related to commit 3db61db48 [1], which fixed
a similar behavior for child foreign key constraints. Per that commit
message, it's a good idea for the child objects to have names related
to the parent objects, so we ought to change this behavior regardless
of any concurrent-failure considerations.

Having said that, I do not think that the OP's idea of fully
deterministic index name choice is workable. We don't constrain
partitions to be exactly like their parents; that means that an index
name that works fine at an upper level might conflict with some
pre-existing index on a child. So unless you prefer failure to
selecting a different name at the child level, it's necessary to
allow the child index names to sometimes be different.

But ... the code *does* have the ability to dodge conflicting
index names already; this is why you get
partition_name_expr_idx
partition_name_expr_idx1
partition_name_expr_idx2
and not immediate failure. If this isn't working reliably in
concurrent situations, that must mean that we are not obtaining
an exclusive lock before looking for pre-existing index names.
I'm not sure if that's a bug or intentional. My vague recollection
is that we intend to allow multiple CREATE INDEX in parallel, so it
may be that obtaining a lock would be a cure worse than the disease.

In any case, deriving the child index name(s) from the parent name
would reduce the scope of this problem, so I agree we ought to
make it do that.

regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3db61db48