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.
Thanks in advance for looking into it.
Best regards,
Max Chrzan
