Обсуждение: Index creation fails with automatic names
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.
On Mon, Oct 14, 2013 at 6:31 AM, Florian Nigsch <flo@nigsch.eu> wrote:
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?
looks like the auto-generated names for your indexes clash. Give them explicit names.
Also, subscribe to the list if you want help. this is not your private help desk.
2013/10/14 Florian Nigsch <flo@nigsch.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
On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch <flo@nigsch.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
Merlin Moncure <mmoncure@gmail.com> wrote: > On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch <flo@nigsch.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
Ian Lawrence Barwick <barwick@gmail.com> wrote: > 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 You seem to be creating the indexes one at a time, all on the same connection. The OP's problem occurs when starting five CREATE INDEX statements in five different sessions all at the same time. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2013/10/18 Kevin Grittner <kgrittn@ymail.com>: > Ian Lawrence Barwick <barwick@gmail.com> wrote: > >> 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 > > You seem to be creating the indexes one at a time, all on the same > connection. The OP's problem occurs when starting five CREATE > INDEX statements in five different sessions all at the same time. (reads original email again) ah yes, brain was not properly engaged.