allow PG search path reflection behavior to be configurable

[Former user]

(original reporter: vrobin) 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

[Former user]

(original author: vrobin) PS: problem verified with sqlalchemy v0.9.1

[Mike Bayer]

test case

[Mike Bayer]

first, make sure you read: http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#remote-cross-schema-table-introspection

are you using an unusual search_path?

next, please run the attached test case. It includes your CREATE TABLE and code verbatim, it works fine for me. Please alter it so that it illustrates your problem.

Also note nothing has changed in 0.9 regarding how schemas are handled.

So I have a feeling your search path is not set appropriately for how you're using the "schema" identifier here.

[Former user]

(original author: vrobin) You were absolutely right, problem isn’t related to sqlalchemy upgrades (from 0.7.10 to 0.8.4, then 0.9.1). Both upgrade and search_path configuration were changed in a close period of time.

Current search_path configuration is: '"$user", public, mymainschema, secondaryschema,

I can't be sure, but I suspect that before, search_path looked like this: '"$user", mymainschema, public,

I've run attached test case, it fails with search_path set to "$user", public, mymainschema, secondaryschema;

But runs flawlesslywith search_path set to "$user", public

As you can see in the test log below, it seems that the schema parameter override schema specified in foreign key declaration.

There may be an intrinsic problem with this use of explicit schema (in DDL, in SQLAlchemy reflection) along with an implicit schema resolution (specific search_path) which is set at the db level, but I fail to see it.

Initially, my goal was to reflect only tables in "mymainschema" (reflecting linked to "secondaryschema" is a good behaviour in my case).

I hope this helps you understand/qualify the problem.

2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine select version()
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine select current_schema()
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine CREATE SCHEMA mymainschema
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine CREATE SCHEMA secondaryschema
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine 
    CREATE TABLE secondaryschema.tableinsecondarychema (
        city_code VARCHAR(2) PRIMARY KEY
    )

2014-01-28 10:37:25,417 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,447 INFO sqlalchemy.engine.base.Engine 
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
)

2014-01-28 10:37:25,447 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine SELECT relname FROM pg_class c WHERE relkind = 'r' AND 'mymainschema' = (select nspname from pg_namespace n where n.oid = c.relnamespace) 
2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,463 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')

2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {'table_name': u'myfirsttable', 'schema': u'mymainschema'}
2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine 
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                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

2014-01-28 10:37:25,463 INFO sqlalchemy.engine.base.Engine {'table_oid': 174750}
2014-01-28 10:37:25,509 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
            WHERE t.typtype = 'd'

2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,509 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_enum e ON t.oid = e.enumtypid
            WHERE t.typtype = 'e'
            ORDER BY "name", e.oid -- e.oid gives us label order

2014-01-28 10:37:25,509 INFO sqlalchemy.engine.base.Engine {}
2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine 
                SELECT a.attname
                FROM pg_attribute a JOIN (
                    SELECT unnest(ix.indkey) attnum,
                           generate_subscripts(ix.indkey, 1) ord
                    FROM pg_index ix
                    WHERE ix.indrelid = %(table_oid)s AND ix.indisprimary
                    ) k ON a.attnum=k.attnum
                WHERE a.attrelid = %(table_oid)s
                ORDER BY k.ord

2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_oid': 174750}
2014-01-28 10:37:25,542 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

2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_oid': 174750}
2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine 
          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = %(table)s AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1

2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table': 174750}
2014-01-28 10:37:25,542 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')

2014-01-28 10:37:25,542 INFO sqlalchemy.engine.base.Engine {'table_name': u'tableinsecondarychema', 'schema': u'mymainschema'}
XXXX Failing Query
            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)
            AND c.relname = :table_name AND c.relkind in ('r','v')
         tableinsecondarychema mymainschema
XXXX
Traceback (most recent call last):
  File "J:\project\loid\schema_reflect_test.py", line 43, in <module>
    metadata.reflect()
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 2809, in reflect
    Table(name, self, **reflect_opts)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 413, in _autoload
    self, include_columns, exclude_columns
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\base.py", line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\default.py", line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\reflection.py", line 513, in reflecttable
    **reflection_options
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 332, in __new__
    table._init(name, metadata, *args, **kw)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 396, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\schema.py", line 413, in _autoload
    self, include_columns, exclude_columns
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\base.py", line 1118, in run_callable
    return callable_(self, *args, **kwargs)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\default.py", line 262, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\reflection.py", line 414, in reflecttable
    for col_d in self.get_columns(table_name, schema, **tblkw):
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\reflection.py", line 254, in get_columns
    **kw)
  File "<string>", line 1, in <lambda>
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\reflection.py", line 49, in cache
    ret = fn(self, con, *args, **kw)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 1683, in get_columns
    info_cache=kw.get('info_cache'))
  File "<string>", line 1, in <lambda>
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\engine\reflection.py", line 49, in cache
    ret = fn(self, con, *args, **kw)
  File "J:\project\python-venv\loid\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 1598, in get_table_oid
    raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: tableinsecondarychema

[Mike Bayer]

Replying to vrobin:

There may be an intrinsic problem with this use of explicit schema (in DDL, in SQLAlchemy reflection) along with an implicit schema resolution (specific search_path) which is set at the db level, but I fail to see it.

there is, and it is exactly what is being referred to in the document I linked: http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html#remote-cross-schema-table-introspection

Postgresql does not tell us the schema of the constraint if that schema is in the search path. E.g., a particular query that normally tells us "refers to someotherschema.sometable.somecolumn" now tells us only "sometable.somecolumn". That's it, game over, we are not told what the schema name is. A lot of work went into figuring this out. I'll go over your test later but pretty much this issue is a "wontfix".

[Former user]

(original author: vrobin)

Postgresql *does not tell us the schema of the constraint* if that schema is in the search path.

Ok, this is clear! As you said "game over". I could try to look around if I can find some mean to circumvent this limitation, but it would be a bit presomptuous of me to think that I can find a way where you didn't :).

Would it be possible that backup/change/introspect/restore search_path could solve this issue? (maybe it would break many, many other things?)

Anyway, thanks for digging into this "non issue". I will probably just redefine search_path at connection level. Maybe this ticket will be useful for other people that hit similar search_path problems (I've seen report like this one on debian mailing-list that were left unanswered).

To conclude, many many thanks for the great job you're doing on SqlAlchemy.

[Mike Bayer]

so this is what the issue is - there is probably a way to fix this, which would mean we'd have to no longer rely upon a particular PG function we're using. if you use 'echo="debug"' you can see the rows coming back. Here's with the schemas in the search path:

          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = %(table)s AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1

{'table': 538795}
Col ('conname', 'condef', 'conschema')
Row (u'fkdc7e8de5969fc80', u'FOREIGN KEY (city_id) REFERENCES tableinsecondarychema(city_code)', u'secondaryschema')

here's with a plain search_path:

          SELECT r.conname,
                pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
                n.nspname as conschema
          FROM  pg_catalog.pg_constraint r,
                pg_namespace n,
                pg_class c

          WHERE r.conrelid = %(table)s AND
                r.contype = 'f' AND
                c.oid = confrelid AND
                n.oid = c.relnamespace
          ORDER BY 1

{'table': 538812}
Col ('conname', 'condef', 'conschema')
Row (u'fkdc7e8de5969fc80', u'FOREIGN KEY (city_id) REFERENCES secondaryschema.tableinsecondarychema(city_code)', u'secondaryschema')

so basically pg_get_constraintdef() takes the search path into account. We'd need to not use that function anymore. I'm not sure why that hasn't been tried yet.

[Mike Bayer]

looking more closely, it seems like the issue is not as much that we don't know the schema, as it is actually in both results, but that we don't know how to interpret this schema compared to how the user has set up the Table defs. That is, someone might have schema A, B, C in their search path, and they want to use Table in a schemaless fashion across all three. So when we reflect a constraint from A to B, we want the correct "B" Table - e.g. the one with or without the schema.

So i think the issue is more like we had to make a choice how to appease people using table reflection in different ways with different search paths.

[Mike Bayer]

OK, you've convinced me, we have enough infrastructure in our reflection process now to make this configurable. patch is attached.

[Mike Bayer]

the two test cases are:

# test one
metadata.reflect(postgresql_ignore_search_path=True)

# test two
myfirsttable = Table('myfirsttable', metadata, autoload=True, postgresql_ignore_search_path=True)

tests will be specifically in test.dialect.postgresql.test_reflection-> test_schema_reflection_multi_search_path_ignore / _dontignore.

[Former user]

(original author: vrobin) Wow, this is what I call premium service! You're impressive! :)

I've applied your patch and I can confirm that the test case now works perfectly with postgresql_ignore_search_path=True parameter (and fails as before when parameter is set to false).

Strangely, with the database at my hand (a pgsql 9.2 / windows 7 x64), the test case fails before the patch and works with the patch, but the orignal code with the original database... doesn't fail (even before patch is applied). I'll try tomorrow with the failing database (pgsql 9.1 / linux) so I can confirm the full resolution of the problem (though I don't really doubt the problem will be solved)

[Former user]

(original author: vrobin) With failing environment (pgsql 9.1 / linux), patch fails to correct the problem with the real database and application.

Behavior is rather weird to me because the parameter "postgresql_ignore_search_path" changes during introspection.

This can be seen after adding this trace in dialects/postgresql/base.py (line 1981):

    @reflection.cache
    def get_foreign_keys(self, connection, table_name, schema=None, postgresql_ignore_search_path=False, **kw):
        print "XXXXXY", postgresql_ignore_search_path, table_name, schema

and calling this:

metadata = MetaData(bind=engine, schema=SCHEMA)
metadata.bind.echo = True
print sqlalchemy.__version__
print "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
metadata.reflect(postgresql_ignore_search_path=True)   ### <= Here
print "ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ"

The result is the following XXXXXY True table1 mymainschema XXXXXY True table2 mymainschema XXXXXY True table3 mymainschema XXXXXY True table4 mymainschema XXXXXY False table5 mymainschema ... XXXXXY False tableX mymainschema

Change from True to False, doesn't occur in a table having a dependency in secondary schema.

If I change default value from def get_foreign_keys(self, connection, table_name, schema=None, postgresql_ignore_search_path=False, kw): to def get_foreign_keys(self, connection, table_name, schema=None, postgresql_ignore_search_path=True, kw):

Things naturally works.

PS: It is possible that 9222.patch is incomplete because when I manually patched my sqlalchemy sourcetree, It didn't have class PGDialect(default.DefaultDialect): "using": False, "where": None, "ops": {} (that may have been since 0.9.1 release: http://www.sqlalchemy.org/trac/changeset/1af8e2491dcbed723d2cdafd44fd37f1a6908e91#file5 )

PPS: I applied patch on 0.9.1 pypi package, not against trunk checkout

[Mike Bayer]

updated, try again

[Mike Bayer]

standalone test of the feature

[Mike Bayer]

patch is updated please try again

[Former user]

(original author: vrobin) After adding missing line:

reflection_options = ('postgresql_ignore_search_path', )

The value of postgresql_ignore_search_path remains coherent between all tables and the patch now also works with failing environment.

AFAICT, the patch, once correctly applied, solves the problem.

One last question about this patch... did you really meant

       (schema.Table, { 
            "ignore_search_path": False

And not

       (schema.Table, { 
            "postgresql_ignore_search_path": False

?

[Mike Bayer]

Feel free to try it both ways and see what results you get :). (short answer: yes)

[Former user]

(original author: vrobin) Replying to zzzeek:

Feel free to try it both ways and see what results you get :). (short answer: yes)

Grep didn't find any other occurence to "ignore_search_path" so it seems pretty obvious that the answer was 'yes', but... you know, sometimes, black magic happens.

[Mike Bayer]

0326f3cf014ffb4928b4c6051d rewrote the docs also

[Mike Bayer]

Removing milestone: 0.9.2 (automated comment)