From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dilip Kumar <dilipbalaut@gmail.com>
cc: maximilian.chrzan@here.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18959: Name collisions of expression indexes during parallel
 Index creations on a pratitioned table.
In-reply-to: 
 <CAFiTN-vB9UHF0yJxFynKuFiLkw094z7KLyDw4EAfUeaj1pZ00g@mail.gmail.com>
References: <18959-f63b53b864bb1417@postgresql.org>
 <CAFiTN-vB9UHF0yJxFynKuFiLkw094z7KLyDw4EAfUeaj1pZ00g@mail.gmail.com>
Comments: In-reply-to Dilip Kumar <dilipbalaut@gmail.com>
	message dated "Wed, 18 Jun 2025 15:59:48 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1081050.1750260111.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 18 Jun 2025 11:21:51 -0400
Message-ID: <1081051.1750260111@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1081051.1750260111%40sss.pgh.pa.us>
Precedence: bulk

Dilip Kumar <dilipbalaut@gmail.com> writes:
> On Sat, Jun 14, 2025 at 3:15=E2=80=AFPM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> If expressions or parent index names differ, partition-level index name=
s
>> 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=3Dpostgresql.git&a=3Dcommitdiff&h=
=3D3db61db48