defect in postgre entity detection

Firstly sorry if the name isn't very descriptive, didn't really know what to call this!

The problem: I have a ton of mappings, I run create_all(), later I rename the public schema to something else ("old") to get it out of the way, and create a new public schema ("CREATE SCHEMA public;"), effectively taking the place of the old schema. With a totally new engine, environment, etc., I run create_all() again and I hit an error. SQLA isn't issuing the CREATE SEQUENCE for one of my explicit sequences any more.

A log of SQLA's actions the first time round:

2009-10-21 00:11:59,592 INFO sqlalchemy.engine.base.Engine.0x...86f0 SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%%' AND nspname != 'information_schema' AND relname = %(seqname)s);
2009-10-21 00:11:59,592 INFO sqlalchemy.engine.base.Engine.0x...86f0 {'seqname': 'proposal_id_seq'}
2009-10-21 00:11:59,592 DEBUG sqlalchemy.engine.base.Engine.0x...86f0 Col ('relname',)
2009-10-21 00:11:59,592 INFO sqlalchemy.engine.base.Engine.0x...86f0 CREATE SEQUENCE proposal_id_seq
2009-10-21 00:11:59,592 INFO sqlalchemy.engine.base.Engine.0x...86f0 {}
2009-10-21 00:11:59,608 INFO sqlalchemy.engine.base.Engine.0x...86f0 COMMIT
(CREATE TABLE follows this)

Second run, after the schema switch:

2009-10-21 00:13:38,244 INFO sqlalchemy.engine.base.Engine.0x...a710 SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%%' AND nspname != 'information_schema' AND relname = %(seqname)s);
2009-10-21 00:13:38,244 INFO sqlalchemy.engine.base.Engine.0x...a710 {'seqname': 'proposal_id_seq'}
2009-10-21 00:13:38,244 DEBUG sqlalchemy.engine.base.Engine.0x...a710 Col ('relname',)
(CREATE TABLE follows this)

It seems SQLA is falsely detecting that the sequence already exists and deciding not to issue the CREATE.

Inspecting the SQL that is used to do the check, the problem is clear:

WHERE nspname NOT LIKE 'pg_%%' AND nspname != 'information_schema'

SQLA correctly checks that we're not looking at entities in the system schemas - but this includes all of the user created schemas. This should be changed to check only the public schema, or the name of the schema specified in the table arguments (hopefully you're already doing the latter, I haven't checked).

Comments (2)