Re: Index creation fails with automatic names

On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch <flo(at)nigsch(dot)eu> wrote:
> 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?
>
> Any clarifications would be greatly appreciated!

hm. what happens when you set transaction isolation to serializable?

merlin