Re: Index creation fails with automatic names

2013/10/14 Florian Nigsch <flo(at)nigsch(dot)eu>:
> Hi all,
>
> I am not sure if this is a bug or a misuse on my part.
>
> I am creating a number of indices in parallel on a table by using xargs. To
> do that, I write all my indices in a file indices.idx, and then have the
> indices build in parallel (in this case with 5 concurrent processes)
>
> cat indices.idx | xargs -P5 -I# psql -1 -c '#'
>
> indices.idx contains lines like this:
>
> ALTER TABLE schema.table1 ADD CONSTRAINT pk_activity PRIMARY KEY (field_sk);
>
> CREATE INDEX ON schema.table1 ((LOWER(field2)));
> CREATE INDEX ON schema.table1 ((LOWER(field3)));
> CREATE INDEX ON schema.table1 (field4, field5);
> CREATE INDEX ON schema.table1 (field4, field6, field5);
>
>
> Upon running the above command, I see the following error:
>
> ALTER TABLE
> CREATE INDEX
> ERROR: duplicate key value violates unique constraint
> "pg_class_relname_nsp_index"
> DETAIL: Key (relname, relnamespace)=(table1_lower_idx, 2064404) already
> exists.
>
> My question is then - where does this error come from? Is is because
> Postgres allocates the same name (table1_lower_idx) twice when the index
> begins building, because at that time there's no index present with that
> name? But if one index finishes earlier, then the second one can't be
> committed because it has the same name as an already present index?

It works fine for me on Pg 9.3.1:

postgres=# CREATE TABLE foo(val1 text, val2 text);
CREATE TABLE
postgres=# CREATE INDEX on foo((lower(val1)));
CREATE INDEX
postgres=# CREATE INDEX on foo((lower(val2)));
CREATE INDEX
postgres=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+------+-----------
val1 | text |
val2 | text |
Indexes:
"foo_lower_idx" btree (lower(val1))
"foo_lower_idx1" btree (lower(val2))

Which PostgreSQL version are you using? Are you sure there's not an index
with the offending name already?

Regards

Ian Barwick