Re: Index creation fails with automatic names

Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch <flo(at)nigsch(dot)eu> wrote:

>> 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?

I'm going to go along with the suggestion that you explicitly name
them when you create the indices.idx file.  When these all start
together, they probably cannot see each others' catalog entries,
and so don't think they are choosing duplicate names.

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

I would not expect that to help; since system tables weren't using
MVCC snapshots when SSI was implemented, they were excluded from
serializable behavior.  It might be worth revisiting that now that
we have MVCC catalog access, but in this case it would just replace
one type of error with another.
 
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company