relected other-schema foreign-key table not found

[Former user]

There's this multi-schema, single postgresql database (for now), application with lots of tables. Different schemas are being used to divide up some of the very different aspects of the application.

Its database structure is maintained with pgadminIII and we're using reflection with autoload=True in the Table() method.

When creating an object for the parent table in schema01, it fails to see the child table in schema02. There is a 'NoSuchTable' error.

I tried creating the child Table object first, but creation of the parent Table still raises the same error. Deleting and recreating the foreign key constraint also made no improvement. The setting of the postgresql search_path variable has also failed to work round this failure.

Very cut down example tables, not proper DDL -

Table01: players, in schema01

column01: name text

column02: hobby foreign key schema02.hobbies(id)

Table02: hobbies in schema02

column01: id int primary key

column02: term text

player=Table('players', meta, schema='schema01', autoload=True, autoload_with=engine) ...lots of feedback... NoSuchTableError: hobbies

hobby= Table('hobbies', meta, schema='schema02', autoload=True, autoload_with=enqine) succeeds. Then the previous call fails the same way.

Many thanks...

[Mike Bayer]

we have tests for this which pass across multiple schemas can you please provide minimal DDL scripts for the tables in question, as well as SQL output from using echo ='debug' so that the rows returned can be viewed ?

[Mike Bayer]

by "minimal" I mean, just the pk/foreign key columns involved in the relationship. not all the other columns and details.

[Former user]

Here's the other, real, pair of tables and a chunk of crash report.

The real child table's DDL, cut down a bit, is -

CREATE TABLE shelf.bloodgroups ( id integer NOT NULL DEFAULT nextval('bloodgroups_id_seq'::regclass), ... CONSTRAINT bloodgrouppk PRIMARY KEY (id) USING INDEX TABLESPACE pg_default ) WITH ( OIDS=FALSE ) TABLESPACE pg_default; ALTER TABLE shelf.bloodgroups OWNER TO postgres; GRANT various...;

Parent table - (curiously the create table SQL from pgadminIII says 'patients' not 'findings.patients').

CREATE TABLE patients ( id bigserial NOT NULL, -- primary key ... bloodgroup integer, -- foreign key reference to bloodgroup instance for cardinal bloodgroup ... CONSTRAINT patientpk PRIMARY KEY (id) USING INDEX TABLESPACE pg_default, CONSTRAINT bloodgroup FOREIGN KEY (bloodgroup) REFERENCES shelf.bloodgroups (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ) TABLESPACE pg_default; ALTER TABLE patients OWNER TO postgres; GRANT various...;

python> from sqlalchemy import * engine = create_engine('postgresql://postgres:pwd419@localhost:5433/elab') meta=MetaData() pt=Table('patients', meta, schema='findings', autoload=True, autoload_with=engine)

---

NoSuchTableError Traceback (most recent call last)

/home/paulc/<ipython console> in <module>()

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/schema.pyc in new(cls, args, kw) 263 metadata._add_table(name, schema, table) 264 try: --> 265 table._init(name, metadata, args, **kw) 266 table.dispatch.after_parent_attach(table, metadata) 267 return table

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/schema.pyc in _init(self, name, metadata, args, *kwargs) 322 autoload_with.run_callable( 323 autoload_with.dialect.reflecttable, --> 324 self, include_columns 325 ) 326 else:

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/base.pyc in run_callable(self, callable_, args, kwargs) 2262 conn = self.contextual_connect() 2263 try: -> 2264 return conn.run_callable(callable_, args, **kwargs) 2265 finally: 2266 conn.close()

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/base.pyc in run_callable(self, callable_, args, kwargs) 1892 1893 """ -> 1894 return callable_(self, args, kwargs) 1895 1896 def _run_visitor(self, visitorcallable, element, kwargs):

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/default.pyc in reflecttable(self, connection, table, include_columns) 252 def reflecttable(self, connection, table, include_columns): 253 insp = reflection.Inspector.from_engine(connection) --> 254 return insp.reflecttable(table, include_columns) 255 256 def get_pk_constraint(self, conn, table_name, schema=None, **kw):

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/reflection.pyc in reflecttable(self, table, include_columns) 440 autoload=True, schema=referred_schema, 441 autoload_with=self.bind, --> 442 **reflection_options 443 ) 444 for column in referred_columns:

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/schema.pyc in new(cls, args, kw) 263 metadata._add_table(name, schema, table) 264 try: --> 265 table._init(name, metadata, args, **kw) 266 table.dispatch.after_parent_attach(table, metadata) 267 return table

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/schema.pyc in _init(self, name, metadata, args, *kwargs) 322 autoload_with.run_callable( 323 autoload_with.dialect.reflecttable, --> 324 self, include_columns 325 ) 326 else:

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/base.pyc in run_callable(self, callable_, args, kwargs) 1892 1893 """ -> 1894 return callable_(self, args, kwargs) 1895 1896 def _run_visitor(self, visitorcallable, element, kwargs):

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/default.pyc in reflecttable(self, connection, table, include_columns) 252 def reflecttable(self, connection, table, include_columns): 253 insp = reflection.Inspector.from_engine(connection) --> 254 return insp.reflecttable(table, include_columns) 255 256 def get_pk_constraint(self, conn, table_name, schema=None, **kw):

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/reflection.pyc in reflecttable(self, table, include_columns) 375 # columns

376         found_table = False

--> 377 for col_d in self.get_columns(table_name, schema, **tblkw): 378 found_table = True 379 table.dispatch.column_reflect(table, col_d)

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/reflection.pyc in get_columns(self, table_name, schema, kw) 228 col_defs = self.dialect.get_columns(self.bind, table_name, schema, 229 info_cache=self.info_cache, --> 230 kw) 231 for col_def in col_defs: 232 # make this easy and only return instances for coltype

/home/paulc/<string> in <lambda>(self, connection, table_name, schema, **kw)

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/reflection.pyc in cache(fn, self, con, args, kw) 44 ret = info_cache.get(key) 45 if ret is None: ---> 46 ret = fn(self, con, args, **kw) 47 info_cachekey = ret 48 return ret

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/dialects/postgresql/base.pyc in get_columns(self, connection, table_name, schema, **kw) 1118 1119 table_oid = self.get_table_oid(connection, table_name, schema, -> 1120 info_cache=kw.get('info_cache')) 1121 SQL_COLS = """ 1122 SELECT a.attname,

/home/paulc/<string> in <lambda>(self, connection, table_name, schema, **kw)

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/engine/reflection.pyc in cache(fn, self, con, args, kw) 44 ret = info_cache.get(key) 45 if ret is None: ---> 46 ret = fn(self, con, args, **kw) 47 info_cachekey = ret 48 return ret

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/dialects/postgresql/base.pyc in get_table_oid(self, connection, table_name, schema, **kw) 1032 table_oid = c.scalar() 1033 if table_oid is None: -> 1034 raise exc.NoSuchTableError(table_name) 1035 return table_oid 1036

NoSuchTableError: bloodgroups

Nice deep stack that. I hope there's something useful there. For the generated SQL, which method call should get the echo='debug'? I can't seem to find anything with dir() in Table, create_engine or MetaData.

[Mike Bayer]

OK, thanks for that, though I didn't need a stack trace. Need the SQL output.

Here's a test script that creates two tables in two remote schemas, foreign key between them, reflects fine. Note the echo='debug' which will cause it to dump all reflection queries emitted as well as what results came back. The script doesn't persist anything so perhaps you can adjust it to your environment and try it out. If you can then figure out what is different about your tables that is not present here, a modification to this script will allow me to understand the issue.

from sqlalchemy import *

e = create_engine('postgresql://scott:tiger@localhost/test', echo='debug')
c = e.connect()
t = c.begin()
try:
    c.execute("""
    CREATE TABLE test_schema.some_table (
        id SERIAL not null primary key
    )
    """)

    c.execute("""
    CREATE TABLE test_schema_2.some_other_table (
        id SERIAL not null primary key,
        sid INTEGER REFERENCES test_schema.some_table(id)
    )
    """)

    m1 = MetaData(c)

    t1 = Table('some_table', m1, schema="test_schema", autoload=True)
    t2 = Table('some_other_table', m1, schema="test_schema_2", autoload=True)

    assert t2.c.sid.references(t1.c.id)
finally:
    t.rollback()

[Former user]

I did the test interactively with ipython. The shell script failed strangely -

from: can't read /var/mail/sqlalchemy

./sa1: line 4: syntax error near unexpected token `('

line 4 was the create_engine call; checked and can't find any typos.

Anyhow, this is what happened. Looks like the inspection of the FK is not correctly getting the schema of the referenced table.

ipython

from sqlalchemy import *

e = create_engine('postgresql://postgres:trout3@localhost:5433/elab', echo='debug')

c = e.connect()

m1 = MetaData(c)

t1 = Table('patients', m1, schema="findings", autoload=True, autoload_with=e)

2011-08-09 21:41:00,916 INFO sqlalchemy.engine.base.Engine

        SELECT c.oid

        FROM pg_catalog.pg_class c

        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

        WHERE (n.nspname = %(schema)s)

        AND c.relname = %(table_name)s AND c.relkind in ('r','v')

2011-08-09 21:41:00,916 INFO sqlalchemy.engine.base.Engine {'table_name': u'patients', 'schema': u'findings'}

2011-08-09 21:41:01,063 DEBUG sqlalchemy.engine.base.Engine Col ('oid',)

2011-08-09 21:41:01,063 DEBUG sqlalchemy.engine.base.Engine Row (16390,)

2011-08-09 21:41:01,064 INFO sqlalchemy.engine.base.Engine

        SELECT a.attname,

          pg_catalog.format_type(a.atttypid, a.atttypmod),

          (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid)

            for 128)

            FROM pg_catalog.pg_attrdef d

           WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum

           AND a.atthasdef)

          AS DEFAULT,

          a.attnotnull, a.attnum, a.attrelid as table_oid

        FROM pg_catalog.pg_attribute a

        WHERE a.attrelid = %(table_oid)s

        AND a.attnum > 0 AND NOT a.attisdropped

        ORDER BY a.attnum

2011-08-09 21:41:01,065 INFO sqlalchemy.engine.base.Engine {'table_oid': 16390}

2011-08-09 21:41:01,192 DEBUG sqlalchemy.engine.base.Engine Col ('attname', 'format_type', 'default', 'attnotnull', 'attnum', 'table_oid')

2011-08-09 21:41:01,193 DEBUG sqlalchemy.engine.base.Engine Row (u'surname', u'character varying(30)', None, True, 1, 16390)

2011-08-09 21:41:01,193 DEBUG sqlalchemy.engine.base.Engine Row (u'forename', u'character varying(40)', None, True, 2, 16390)

2011-08-09 21:41:01,194 DEBUG sqlalchemy.engine.base.Engine Row (u'dob', u'date', None, False, 3, 16390)

2011-08-09 21:41:01,194 DEBUG sqlalchemy.engine.base.Engine Row (u'sex', u'character varying', None, True, 4, 16390)

2011-08-09 21:41:01,194 DEBUG sqlalchemy.engine.base.Engine Row (u'streetaddr', u'character varying(40)', None, False, 5, 16390)

2011-08-09 21:41:01,194 DEBUG sqlalchemy.engine.base.Engine Row (u'homeaddr2', u'character varying(40)', None, False, 6, 16390)

2011-08-09 21:41:01,194 DEBUG sqlalchemy.engine.base.Engine Row (u'homeaddr3', u'character varying(40)', None, False, 7, 16390)

2011-08-09 21:41:01,195 DEBUG sqlalchemy.engine.base.Engine Row (u'homeaddr4', u'character varying(40)', None, False, 8, 16390)

2011-08-09 21:41:01,195 DEBUG sqlalchemy.engine.base.Engine Row (u'city', u'character varying(40)', None, False, 9, 16390)

2011-08-09 21:41:01,195 DEBUG sqlalchemy.engine.base.Engine Row (u'postcode', u'character varying(10)', None, False, 10, 16390)

2011-08-09 21:41:01,195 DEBUG sqlalchemy.engine.base.Engine Row (u'nhsnr', u'bigint', None, False, 11, 16390)

2011-08-09 21:41:01,195 DEBUG sqlalchemy.engine.base.Engine Row (u'id', u'bigint', u"nextval('patients_id_seq'::regclass)", True, 12, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'iprn', u'character varying(12)', None, False, 13, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'gender', u'character varying', None, False, 14, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'validnhsnr', u'boolean', u'false', True, 15, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'bloodgroup', u'integer', None, False, 16, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'casenr', u'character varying(16)', None, False, 17, 16390)

2011-08-09 21:41:01,196 DEBUG sqlalchemy.engine.base.Engine Row (u'nhsTraced', u'character varying(8)', None, False, 18, 16390)

2011-08-09 21:41:01,197 DEBUG sqlalchemy.engine.base.Engine Row (u'approxdob', u'boolean', u'false', False, 19, 16390)

2011-08-09 21:41:01,197 DEBUG sqlalchemy.engine.base.Engine Row (u'age', u'interval', None, False, 22, 16390)

2011-08-09 21:41:01,198 INFO sqlalchemy.engine.base.Engine

        SELECT t.typname as "name",

           pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",

           not t.typnotnull as "nullable",

           t.typdefault as "default",

           pg_catalog.pg_type_is_visible(t.oid) as "visible",

           n.nspname as "schema"

        FROM pg_catalog.pg_type t

           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace

           LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid

        WHERE t.typtype = 'd'

2011-08-09 21:41:01,198 INFO sqlalchemy.engine.base.Engine {}

2011-08-09 21:41:01,223 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'attype', 'nullable', 'default', 'visible', 'schema')

2011-08-09 21:41:01,224 DEBUG sqlalchemy.engine.base.Engine Row (u'cardinal_number', u'integer', True, None, False, u'information_schema')

2011-08-09 21:41:01,224 DEBUG sqlalchemy.engine.base.Engine Row (u'character_data', u'character varying', True, None, False, u'information_schema')

2011-08-09 21:41:01,224 DEBUG sqlalchemy.engine.base.Engine Row (u'sql_identifier', u'character varying', True, None, False, u'information_schema')

2011-08-09 21:41:01,224 DEBUG sqlalchemy.engine.base.Engine Row (u'time_stamp', u'timestamp(2) with time zone', True, u"('now'::text)::timestamp(2) with time zone", False, u'information_schema')

2011-08-09 21:41:01,225 INFO sqlalchemy.engine.base.Engine

        SELECT t.typname as "name",

           -- no enum defaults in 8.4 at least

           -- t.typdefault as "default",

           pg_catalog.pg_type_is_visible(t.oid) as "visible",

           n.nspname as "schema",

           e.enumlabel as "label"

        FROM pg_catalog.pg_type t

             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace

             LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid

             LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid

        WHERE t.typtype = 'e'

        ORDER BY "name", e.oid -- e.oid gives us label order

2011-08-09 21:41:01,225 INFO sqlalchemy.engine.base.Engine {}

2011-08-09 21:41:01,233 DEBUG sqlalchemy.engine.base.Engine Col ('name', 'visible', 'schema', 'label')

2011-08-09 21:41:01,242 INFO sqlalchemy.engine.base.Engine

      SELECT attname FROM pg_attribute

      WHERE attrelid = (

         SELECT indexrelid FROM pg_index i

         WHERE i.indrelid = %(table_oid)s

         AND i.indisprimary = 't')

      ORDER BY attnum

2011-08-09 21:41:01,242 INFO sqlalchemy.engine.base.Engine {'table_oid': 16390}

2011-08-09 21:41:01,245 DEBUG sqlalchemy.engine.base.Engine Col ('attname',)

2011-08-09 21:41:01,245 DEBUG sqlalchemy.engine.base.Engine Row (u'id',)

2011-08-09 21:41:01,246 INFO sqlalchemy.engine.base.Engine

    SELECT conname

       FROM  pg_catalog.pg_constraint r

       WHERE r.conrelid = %(table_oid)s AND r.contype = 'p'

       ORDER BY 1

2011-08-09 21:41:01,246 INFO sqlalchemy.engine.base.Engine {'table_oid': 16390}

2011-08-09 21:41:01,249 DEBUG sqlalchemy.engine.base.Engine Col ('conname',)

2011-08-09 21:41:01,249 DEBUG sqlalchemy.engine.base.Engine Row (u'patientpk',)

2011-08-09 21:41:01,250 INFO sqlalchemy.engine.base.Engine

      SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef

      FROM  pg_catalog.pg_constraint r

      WHERE r.conrelid = %(table)s AND r.contype = 'f'

      ORDER BY 1

2011-08-09 21:41:01,251 INFO sqlalchemy.engine.base.Engine {'table': 16390}

2011-08-09 21:41:01,259 DEBUG sqlalchemy.engine.base.Engine Col ('conname', 'condef')

2011-08-09 21:41:01,259 DEBUG sqlalchemy.engine.base.Engine Row (u'bloodgroup', u'FOREIGN KEY (bloodgroup) REFERENCES bloodgroups(id)')

2011-08-09 21:41:01,263 INFO sqlalchemy.engine.base.Engine

        SELECT c.oid

        FROM pg_catalog.pg_class c

        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

        WHERE (n.nspname = %(schema)s)

        AND c.relname = %(table_name)s AND c.relkind in ('r','v')

2011-08-09 21:41:01,263 INFO sqlalchemy.engine.base.Engine {'table_name': u'bloodgroups', 'schema': u'findings'}

2011-08-09 21:41:01,265 DEBUG sqlalchemy.engine.base.Engine Col ('oid',)

---

NoSuchTableError Traceback (most recent call last)

/home/paulc/bin/<ipython console> in <module>()

/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-linux-i686.egg/sqlalchemy/schema.pyc in new(cls, args, *kw)

...ipython stack dump...

[Mike Bayer]

here's the problem, you're using a full schema search path while at the same time sending the "schema" argument to each table.

solution: set your search path to "public" and nothing else, or remove the usage of the "schema" argument from each Table.

Here's how to reproduce:

from sqlalchemy import *
from sqlalchemy import event

e = create_engine('postgresql://scott:tiger@localhost/test', echo='debug')

c = e.connect()
t = c.begin()
try:
    c.execute("set search_path to test_schema_2, test_schema, public")
    c.dialect.default_schema_name = "test_schema_2"

    c.execute("""
    CREATE TABLE test_schema.some_table (
        id SERIAL not null primary key
    )
    """)

    c.execute("""
    CREATE TABLE test_schema_2.some_other_table (
        id SERIAL not null primary key,
        sid INTEGER REFERENCES test_schema.some_table(id)
    )
    """)

    m1 = MetaData(c)

    t2 = Table('some_other_table', m1, schema="test_schema_2", autoload=True)
    t1 = Table('some_table', m1, schema="test_schema", autoload=True)

    assert t2.c.sid.references(t1.c.id)
finally:
    t.rollback()

[Mike Bayer]

good news, I can make the queries more accurate on my end to skip that specific issue

[Mike Bayer]

OK I'm pretty sure that is what you were seeing as you didn't see the explicit schema in your result, 8b505e7c241aba6afc32a83f870c8a0ca35c6f9c adds criteria to the FK query to get the actual remote schema which fixes the "table not found" problem.

However, your "bloodgroups" Table is still going to be reflected without the "schema" being set, and your explicit "shelf.bloodgroups" Table object isn't going to be what "findings.patients" refers to on the Python side - because PG reports all the schemas here as being in the search path and we can't really assume which tables should have "schema" and which shouldn't.

So still, things will go much more nicely, as well as work with any SQLAlchemy version, if you A. clean up your search_path to have just "public" in it, or B. don't use the "schema" argument for any schemas that are in your search path. The changeset includes new documentation to this effect.

[Mike Bayer]

Here's that doc: http://www.sqlalchemy.org/docs/dialects/postgresql.html#remote-cross-schema-table-introspection

[Former user]

Hey, that's very good learn. Problem solved. Thank you very much for your time.

I tried what you suggested and reduced the search_schema back to 'public'.

The Table() function then succeeded without complaint.

As background, I set the search_path to cover all the schemas when trying to get django to handle the same hook-up of tables.

[Mike Bayer]

Removing milestone: 0.7.3 (automated comment)