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

We are working with very large partitioned tables (500M+ rows, >1 TB of data) and need to create multiple expression indexes on them.

To avoid the issues with parallel index creation, we switched to sequential execution: as soon as one index finishes (usually after 1–2 hours), we immediately start the next (typically within a second). In this setup, there is no actual parallelism — yet we occasionally still hit this error:

ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
Detail: Key (relname, relnamespace) = (…) already exists.

This suggests that the issue is not limited to concurrent execution. It can also occur when index creation happens in quick succession.

Additionally, we noticed that two parallel index creations on a partitioned table will block each other — even if they target different expressions. Here's a simplified example:

CREATE TABLE test (
jsondata JSONB,
version BIGINT NOT NULL DEFAULT 9223372036854775807
) PARTITION BY RANGE (version);

CREATE TABLE test_p0 PARTITION OF test FOR VALUES FROM (0) TO (100000);

Transaction 1:

DO $$
BEGIN
CREATE INDEX IF NOT EXISTS idx_1 ON test
(((jsondata -> 'properties') -> 'foo1') ASC NULLS LAST);
PERFORM pg_sleep(10);
END;
$$;

Transaction 2 (started in parallel):

DO $$
BEGIN
CREATE INDEX IF NOT EXISTS idx_2 ON test
(((jsondata -> 'properties') -> 'foo2') ASC NULLS LAST);
END;
$$;

Transaction 2 will block until Transaction 1 completes — and then fail with:

ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
Detail: Key (relname, relnamespace) = (test_p1_expr_idx, 2200) already exists.

If the same indexes are created directly on the partition "test_p0", the second index is created immediately — without blocking or error.
________________________________
Von: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Gesendet: Mittwoch, 18. Juni 2025 18:46
An: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Cc: Chrzan, Maximilian <maximilian(dot)chrzan(at)here(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Betreff: [EXTERNAL] Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.

[Sie erhalten nicht h?ufig E-Mails von tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Weitere Informationen, warum dies wichtig ist, finden Sie unter https://aka.ms/LearnAboutSenderIdentification ]

LEARN FAST: This email originated outside of HERE.
Please do not click on links or open attachments unless you recognize the sender and know the content is safe. Thank you.

I wrote:
> 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.

I experimented with the attached, which borrows a couple of ideas
from 3db61db48 to produce names like "parent_index_2" when cloning
indexes. While it should help with the immediate problem, I'm not
sure if this is acceptable, because there are a *lot* of ensuing
changes in the regression tests, many more than 3db61db48 caused.
(Note that I didn't bother to fix places where the tests rely on
a generated name that has changed; the delta in the test outputs
is merely meant to give an idea of how much churn there is.
I didn't check non-core test suites, either.)

Also, looking at the error message changes, I'm less sure that
this is a UX improvement than I was about 3db61db48. Do people
care which partition a uniqueness constraint failed in? In
the current behavior, the index name will reflect that, but
with this behavior, not so much.

Anyway, maybe this is a good idea or maybe it isn't. Thoughts?

regards, tom lane