MetaBase.create_all broken on parallel execution

[Eugeny Klementev]

Hi all,

I use sqlalchemy with postgresql backend.

On script starting i use code to initialize all table:

engine = create_engine(settings.database_engine)

Base.metadata.create_all(engine)

session = Session(bind = engine)

And i run two instances of this script.

One of instances broken with message:

Traceback (most recent call last):

  File "./datadb.py", line 40, in <module>

    Base.metadata.create_all(engine)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2515, in create_all

    tables=tables)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2234, in _run_visitor

    conn._run_visitor(visitorcallable, element, **kwargs)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1904, in _run_visitor

    **kwargs).traverse_single(element)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single

    return meth(obj, **kw)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 67, in visit_metadata

    self.traverse_single(table, create_ok=True)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single

    return meth(obj, **kw)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 86, in visit_table

    self.connection.execute(schema.CreateTable(table))

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in execute

    params)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1496, in _execute_ddl

    compiled

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context

    context)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context

    context)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "pg_type_typname_nsp_index"

DETAIL:  Key (typname, typnamespace)=(datadb_datapoint_id_seq, 2200) already exists.

 '\nCREATE TABLE datadb_datapoint (\n\tid SERIAL NOT NULL, \n\tunit_id INTEGER NOT NULL, \n\ttimestamp INTEGER NOT NULL, \n\tvalue_type VARCHAR NOT NULL, \n\tvalue VARCHAR NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}

I understand it is conflict of creation of tables from different instances at one time.

I hope that sqlalchemy has any locking mechanism, but my investigations are failed.

Anybody known solution of my problem?

[David Lemayian]

This happened to me as well. Happened after installing libsqlite3-dev. Fixed by running  apt-get remove libsqlite3-dev  .

Best,

David.