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

On Thu, Jun 19, 2025 at 7:38 PM Chrzan, Maximilian
<maximilian(dot)chrzan(at)here(dot)com> wrote:
>
> 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:

I believe this is fundamentally the same issue we're addressing here.
We're observing duplicate index name creation on child tables. If the
first transaction remains open, the second transaction waits for it to
commit or roll back because it's attempting to insert the same index
name key into the catalog. Once the first transaction commits, the
second will roll back due to a unique key violation. Conversely, if
the first transaction rolls back, the second will succeed.

--
Regards,
Dilip Kumar
Google