Re: [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
    Resolution:                            |  selected yet
Progress State:  needs questions answered  |   Keywords:
-------------------------------------------+-------------------------------

Comment (by zzzeek):

 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.

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