From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: maximilian.chrzan@here.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
Date: Wed, 18 Jun 2025 11:21:51 -0400
Message-ID: <1081051.1750260111@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFiTN-vB9UHF0yJxFynKuFiLkw094z7KLyDw4EAfUeaj1pZ00g@mail.gmail.com>
References: <18959-f63b53b864bb1417@postgresql.org>
<CAFiTN-vB9UHF0yJxFynKuFiLkw094z7KLyDw4EAfUeaj1pZ00g@mail.gmail.com>
Dilip Kumar <dilipbalaut@gmail.com> writes:
> On Sat, Jun 14, 2025 at 3:15 PM PG Bug reporting form
> <noreply@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dilipbalaut@gmail.com, 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: <1081051.1750260111@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox