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

On Sat, Jun 14, 2025 at 3:15 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      18959
> Logged by:          Maximilian Chrzan
> Email address:      maximilian.chrzan@here.com
> PostgreSQL version: 17.4
> Operating system:   x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.
> Description:
>
> Dear PostgreSQL team,
> We encountered a reproducible issue when creating expression indexes on a
> partitioned table using:
> CREATE INDEX IF NOT EXISTS ... ON partitioned_table ((expression));
> When such statements are executed in parallel (e.g., via separate
> transactions or threads), the PostgreSQL engine attempts to propagate the
> index to each child partition using internally generated names like:
> partition_name_expr_idx
> partition_name_expr_idx1
> partition_name_expr_idx2
> ...
> These internal names are not derived from the index expression or parent
> index name, but instead appear to be based on a counter of existing
> expression indexes.
> The Issue:
> When multiple expressions are indexed in parallel on the same partitioned
> table, even with distinct expressions and parent index names, the system may
> generate the same internal name for partition-level indexes, causing:
> (Postgres <17): ERROR: duplicate key value violates unique constraint
> "pg_class_relname_nsp_index" 23505
> (Postgres 17): relation "{index_name}" already exists 42P07
> This occurs even though the parent-level index names are unique and
> expressions differ.
> Reproducer (simplified):
> -- In separate sessions concurrently:
> CREATE INDEX IF NOT EXISTS idx_expr1 ON parent_table (((jsondata -> 'a' ->
> 'b')));
> CREATE INDEX IF NOT EXISTS idx_expr2 ON parent_table (((jsondata -> 'x' ->
> 'y')));
> Internally, PostgreSQL attempts to create something like:
> CREATE INDEX parent_table_partition1_expr_idx ON ...
> CREATE INDEX parent_table_partition1_expr_idx ON ... -- collision
> Expected behavior:
> 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.
> This issue limits scalability when programmatically creating multiple
> JSON-path expression indexes on partitioned tables, and complicates use of
> parallelism. While advisory locking is a possible workaround, it is not
> ideal.

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.

-- 
Regards,
Dilip Kumar
Google