Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign ke
Brought to you by:
zzzeek
Menu
▾
▴
Re: [Sqlalchemy-tickets] [sqlalchemy] #2922: Metadata.reflect failed on different schema foreign key constraint (postgresql)
|
From: sqlalchemy <mi...@zz...> - 2014-01-28 10:03:52
|
#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 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
}}}
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2922#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|
