Index creation fails with automatic names

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general