[Sqlalchemy-tickets] Issue #4289: automatic index name: hash collision (zzzeek/sqlalchemy)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 4289: automatic index name: hash collision
https://bitbucket.org/zzzeek/sqlalchemy/issues/4289/automatic-index-name-hash-collision

Aurélien Campéas:

sqlalchemy 1.3.0b1, postgres 10, psycopg 2.7.4

```
#!python

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, ForeignKey
from sqlalchemy.dialects.postgresql import BYTEA, TIMESTAMP

name = 'gas.lng.country.trinidad_and_tobago.net_imports.daily'
ns = 'tsh-automatic.snapshot'

meta = MetaData()
table = Table(
    name, meta,
    Column('id', Integer, primary_key=True),
    Column('end', TIMESTAMP(timezone=True), index=True),
    Column('parent', Integer,
           ForeignKey('{}.{}.id'.format(ns, name)),
           index=True),
    schema=ns)

engine = create_engine('postgresql://localhost/fail', echo=True)

with engine.connect() as cn:
    cn.execute('drop schema if exists "{}" cascade'.format(ns))
    cn.execute('create schema if not exists "{}"'.format(ns))
with engine.connect() as cn:
    table.create(cn)

```

=>

```
#!shell

2018-06-26 10:48:04,996 INFO sqlalchemy.engine.base.Engine select version()
2018-06-26 10:48:04,996 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:04,998 INFO sqlalchemy.engine.base.Engine select current_schema()
2018-06-26 10:48:04,998 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:04,999 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-26 10:48:04,999 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:04,999 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-26 10:48:04,999 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,000 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2018-06-26 10:48:05,000 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,001 INFO sqlalchemy.engine.base.Engine drop schema if exists "tsh-automatic.snapshot" cascade
2018-06-26 10:48:05,001 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,003 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-26 10:48:05,010 INFO sqlalchemy.engine.base.Engine create schema if not exists "tsh-automatic.snapshot"
2018-06-26 10:48:05,010 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,012 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-26 10:48:05,014 INFO sqlalchemy.engine.base.Engine
CREATE TABLE "tsh-automatic.snapshot"."gas.lng.country.trinidad_and_tobago.net_imports.daily" (
	id SERIAL NOT NULL,
	"end" TIMESTAMP WITH TIME ZONE,
	parent INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(parent) REFERENCES "tsh-automatic.snapshot"."gas.lng.country.trinidad_and_tobago.net_imports.daily" (id)
)

2018-06-26 10:48:05,014 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,018 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-26 10:48:05,019 INFO sqlalchemy.engine.base.Engine CREATE INDEX "ix_tsh-automatic_snapshot_gas.lng.country.trinidad_and__e711" ON "tsh-automatic.snapshot"."gas.lng.country.trinidad_and_tobago.net_imports.daily" ("end")
2018-06-26 10:48:05,019 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,022 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-26 10:48:05,024 INFO sqlalchemy.engine.base.Engine CREATE INDEX "ix_tsh-automatic_snapshot_gas.lng.country.trinidad_and__e711" ON "tsh-automatic.snapshot"."gas.lng.country.trinidad_and_tobago.net_imports.daily" (parent)
2018-06-26 10:48:05,024 INFO sqlalchemy.engine.base.Engine {}
2018-06-26 10:48:05,026 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: relation "ix_tsh-automatic_snapshot_gas.lng.country.trinidad_and__e711" already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "fail.py", line 24, in <module>
    table.create(cn)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 779, in create
    checkfirst=checkfirst)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/ddl.py", line 796, in visit_table
    self.traverse_single(index)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/ddl.py", line 823, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/vq5355/src/sqlalchemy/lib/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "ix_tsh-automatic_snapshot_gas.lng.country.trinidad_and__e711" already exists
 [SQL: 'CREATE INDEX "ix_tsh-automatic_snapshot_gas.lng.country.trinidad_and__e711" ON "tsh-automatic.snapshot"."gas.lng.country.trinidad_and_tobago.net_imports.daily" (parent)'] (Background on this error at: http://sqlalche.me/e/f405)
```