David Gardner wrote:
> Ran across a weird issue this morning, not sure if its even a SA issue,
> may just be PostgreSQL (8.3) being weird.
> I recently merged two pgsql databases into one database with two
> schemas, and a foreign key connecting two of the tables.
> he first schema is in the search path, the second is not. The problem
> occurred when I specified the schema='schamaA' in my Table() calls then
> SA wasn't able to see any foreign keys referencing it, but things worked
> just fine when I removed them.
>
> I suspect the problem is that even though I am specifying the schema
> name in my "add constraint ... foreign key... references
> schema_name.table.column", PostgreSQL only seems to store the table
> name, I believe this is because that schema is in the search path.
Yeah, that seems to be the case. The allegedly fully qualified paths
we're reading during reflection are sensitive to the search path. There
are a couple other options:
1) don't use a schema= for the Tables on the search_path. SQLAlchemy
interprets 'schema=None' to mean 'anything that can be referenced
without a schema qualifier'- could be 'public', could be anything in the
path.
2) remove the other schemas from the search path prior to reflection,
and restore them after:
con = engine.connect()
con.execute('set search_path to public')
tbl_a = Table('nodehierarchy', metadata,
autoload=True, autoload_with=con)
tbl_b = Table(...)
con.execute('set search_path to public,asset')
3) provide a column override for that foreign key:
tbl_b = Table('job', metadata,
Column('outdir_assetuid', Integer,
ForeignKey('alt_schema.nodehierarchy.uid')),
schema='alt_schema_2',
autoload=True)
It may be that there's an improvement that can be made to the
cross-schema reflection, but I think the methodology of #1 above usually
works out pretty well.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
