Index creation fails with automatic names

From: Florian Nigsch <flo(at)nigsch(dot)eu>
To: pgsql-general(at)postgresql(dot)org
Subject: Index creation fails with automatic names
Date: 2013-10-14 10:31:23
Message-ID: 20131014123123.Horde.TDXAQ4gkH9F3E7AZI16ojQ4@webmail.df.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Best,

Flo

P.S. Please CC me, I am not currently subscribed.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Crawford 2013-10-14 16:19:23 Re: Forms for entering data into postgresql
Previous Message Philipp Kraus 2013-10-14 10:02:37 Re: trigger without trigger call