[Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)

[sqlalchemy <mi...@zz...>]

#2922: Metadata.reflect failed on different schema foreign key constraint
(postgresql)
----------------------+-----------------------------------------
 Reporter:  vrobin    |           Owner:  zzzeek
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:
Component:  postgres  |        Severity:  no triage selected yet
 Keywords:            |  Progress State:  awaiting triage
----------------------+-----------------------------------------
 A program that worked nicely on SQLAlchemy 0.8.X have begun to fail with
 0.9.X upgrade.

 The code that fails is this one:



 {{{
 engine = create_engine("postgresql+psycopg2://xxxxxx")
 metadata = MetaData(bind=engine, schema="mymainschema")
 metadata.reflect()
 }}}


 Error thrown is:


 {{{
 packages/sqlalchemy/dialects/postgresql/base.py", line 1690, in
 get_table_oid
     raise exc.NoSuchTableError(table_name)
 sqlalchemy.exc.NoSuchTableError: tableinsecondarychema
 }}}


 Relashionship looks like (simplified):

 {{{
 CREATE TABLE mymainschema.myfirsttable
 (
   name character varying(255),
   city_id character varying(255),
   CONSTRAINT myfirsttable_pkey PRIMARY KEY (name),
   CONSTRAINT fkdc7e8de5969fc80 FOREIGN KEY (city_id)
       REFERENCES secondaryschema.tableinsecondarychema (city_code) MATCH
 SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
 )

 }}}


 I managed to workaround this problem with a quick and very dirty change in
 get_table_oid function ( dialects/postgresql/base.py ):


 {{{
         if schema is not None:
             schema = util.text_type(schema)
             # CHANGE BEGIN
             if table_name == 'tableinsecondarychema':
                 schema = u'secondaryschema'
             # CHANGE END
         s = sql.text(query).bindparams(table_name=sqltypes.Unicode)

 }}}

 But this is very ugly, and the problem looks like a bug. What do you think
 about it? If you need more information, just let me know.

 Regards,
 Robin


 PS: detailed trace of the problem

 {{{
 Traceback (most recent call last):
   File "import.py", line 10, in <module>
     from loid.connectors.xls_parser_monobloc import XlsReader
   File "/home/jerry/mygreatapp/loid/connectors/xls_parser_monobloc.py",
 line 35, in <module>
     from xls_schedule_parser_base import \
   File "/home/jerry/mygreatapp/loid/connectors/xls_parser_base.py", line
 328, in <module>
     from loid.dao import *
   File "/home/jerry/mygreatapp/loid/dao.py", line 237, in <module>
     metadata.reflect()
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 3015, in reflect
     Table(name, self, **reflect_opts)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 446, in _autoload
     self, include_columns, exclude_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1107, in run_callable
     return callable_(self, *args, **kwargs)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 308, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
     **reflection_options
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 446, in _autoload
     self, include_columns, exclude_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1107, in run_callable
     return callable_(self, *args, **kwargs)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 308, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
     **reflection_options
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 446, in _autoload
     self, include_columns, exclude_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1107, in run_callable
     return callable_(self, *args, **kwargs)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 308, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 555, in reflecttable
     **reflection_options
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 446, in _autoload
     self, include_columns, exclude_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1107, in run_callable
     return callable_(self, *args, **kwargs)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 308, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 563, in reflecttable
     **reflection_options
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 355, in __new__
     table._init(name, metadata, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 429, in _init
     self._autoload(metadata, autoload_with, include_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/sql/schema.py", line 446, in _autoload
     self, include_columns, exclude_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/base.py", line 1107, in run_callable
     return callable_(self, *args, **kwargs)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/default.py", line 308, in reflecttable
     return insp.reflecttable(table, include_columns, exclude_columns)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 464, in reflecttable
     for col_d in self.get_columns(table_name, schema, **tblkw):
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 277, in get_columns
     **kw)
   File "<string>", line 2, in get_columns
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 53, in cache
     ret = fn(self, con, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1774, in
 get_columns
     info_cache=kw.get('info_cache'))
   File "<string>", line 2, in get_table_oid
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/engine/reflection.py", line 53, in cache
     ret = fn(self, con, *args, **kw)
   File "/home/jerry/py-env/loid-env/local/lib/python2.7/site-
 packages/sqlalchemy/dialects/postgresql/base.py", line 1690, in
 get_table_oid
     raise exc.NoSuchTableError(table_name)
 sqlalchemy.exc.NoSuchTableError: tableinsecondarychema
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python