Re: [GENERAL] Index creation fails with automatic names
On Mon, Oct 14, 2013 at 6:31 AM, Florian Nigsch f...@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.
Re: [GENERAL] Index creation fails with automatic names
2013/10/14 Florian Nigsch f...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index creation fails with automatic names
On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch f...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index creation fails with automatic names
Merlin Moncure mmonc...@gmail.com wrote: On Mon, Oct 14, 2013 at 5:31 AM, Florian Nigsch f...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index creation fails with automatic names
Ian Lawrence Barwick barw...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index creation fails with automatic names
2013/10/18 Kevin Grittner kgri...@ymail.com: Ian Lawrence Barwick barw...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
