Occasional InvalidRequestError: Table 'xxx' is already defined for this MetaData instance.

[Joseph Casale]

I have a module that is imported by several Python scripts run by an application
that fires up a new interpreter session for each invocation. When not under load or
running the code manually at the cli things work fine but once the concurrency
raises and the application starts seeing some load it emits InvalidRequestError
exceptions on one table.

After searching I am not sure the results relate to my issue or maybe my lack
of familiarity with SQLAlchemy has the better of me.

Any guidance would be greatly appreciated,
jlc

[Michael Bayer]

the MetaData object holds one Table object per unique name given.   If you use the Table constructor more than once with the same name and the same MetaData, you get this error.

That's how the error is caused, then the fact that the error is "occasional" points strongly to a race condition of some kind, more than one thread both calling the constructor for Table with the same name.   Patterns that could cause this could be some kind of unsynchronized global registry or singleton object that when called produces a new Table object.

The recommended pattern is for Table objects (as well as mapped classes) to generally be declared at the module level so that these names are produced only at module import time, which itself should occur before the application starts any threads in addition to the main application thread.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Joseph Casale]

Hi Michael,
I spoke with the developer of the application that launches the plugins I am writing and each

python script is run by starting an entirely new process with the interpreter and each plugin.

It should be a clean environment. From the plugins, I simply import which ever tables from

the module below that I would need.

In the event I am doing something wrong, my code looks like:

from os import path

from sqlalchemy import (

    Column, CheckConstraint, DDL, ForeignKey, Index, Integer, String,

    create_engine, event

)

from sqlalchemy.engine import Engine

from sqlalchemy.ext.declarative import declared_attr, declarative_base

from sqlalchemy.orm import sessionmaker

db_name = path.join('path', 'config', 'sqlite.db')

engine = create_engine('sqlite:///{}'.format(db_name), echo=False)

Session = sessionmaker(bind=engine, autoflush=False, autocommit=False)

Base = declarative_base()

@event.listens_for(Engine, 'connect')

def set_sqlite_pragma(dbapi_connection, connection_record):

    cursor = dbapi_connection.cursor()

    cursor.execute('PRAGMA foreign_keys=ON')

    cursor.close()

table defs...

if __name__ == '__main__':

    Base.metadata.create_all(engine)

Thanks so much for the help,

jlc

[Michael Bayer]

how about a stack trace?

[Joseph Casale]

Hey Michael,
For reasons beyond my control at the moment, logging was utterly butchered in a completely

incomprehensible means by another developer.

Traceback (most recent call last):

  File "D:\app_folder\plugin\idm_rw.py", line 6, in <module>

    from idm_config import Attribute, Session

  File "D:\app_folder\include\idm_config.py", line 478, in <module>

    class PortalConfig(Base, EnvironmentMixin):

  File "D:\Python33\lib\site-packages\sqlalchemy\ext\declarative\api.py", line 50, in __init__

    _as_declarative(cls, classname, cls.__dict__)

  File "D:\Python33\lib\site-packages\sqlalchemy\ext\declarative\base.py", line 222, in

    _as_declarative **table_kw)

  File "D:\Python33\lib\site-packages\sqlalchemy\schema.py", line 319, in

    __new__ "existing Table object." % key)

sqlalchemy.exc.InvalidRequestTable 'attribute_config' is already

    defined for this MetaData instance.  Specify 'extend_existing=True' to redefine

    options and columns on an existing Table object.

I think thats what it looked like before he had his way:)

Thanks!
jlc

[Michael Bayer]

OK what's idm_rw.py, and is it the only module in the app that specifies a table named "attribute_config"?  is idm_rw.py always imported right when the app starts up?  or is there some kind of loading going on ?

[Joseph Casale]

Hi Michael,
idm_rw.py is simply a class that is fed stdin and processes and returns the input via stdout.

The sqlalchemy imports at the top are leveraged within the classes init method for configuration

queries.

There are two plugins like this, each one is invoked in a fresh process and loads the sqlalchemy

classes. During normal running, the plugins are fired up several times repeatedly and process

many streams of stdin, so the main code from my module in the earlier post where I construct the

tables gets imported into each plugin every startup.

The plugins are isolated from each other as the interpreter is launched in its own process every

time stdin is required to be processed. I am certain neither knows of the other.

Thanks for all the patience!
jlc

[Michael Bayer]

well it's either that module is being run twice within one process somehow, or you have that table name repeated somewhere else.  its one of those two things or some variant thereof.   you might want to put some logging into that module to see if this is happening.