KeyError when table has domain column types

[Former user]

Under Postgres, SQLAlchemy throws a KeyError for columns that are created with domains for column types. The error goes away when the column is switched back to a conventional type, like varchar.

I will attach a demonstration program.

[Former user]

Please ignore setup.2.sql. I accidentally uploaded that file twice.

You'll notice in test_sa_class.py that the error is occurring in the table autoload. If you comment out the 'test' table, and use the 'testconv' table, the error goes away.

[Mike Bayer]

this is a reflection issue, you may be able to work around it for now by explicitly specifying the desired column in the Table definition, i.e.

http://www.sqlalchemy.org/docs/metadata.html#metadata_tables_reflecting_overriding

although if reflecttable() itself is failing then you might have to forego using autoload=True altogether for this table.

[Former user]

Sounds good. Thanks a lot!

[Mike Bayer]

actually might be nice if someone wants to patch up postgres.py to look for the correct columns on this condition.

[Mike Bayer]

can re-milestone once someone provides a patch

[Former user]

I've attached a patch that adds rudimentary support for domains for PostgreSQL. The datatype, nullability and default value are loaded. CHECK-constrains are not dealt with. I also added an exception UnknownColumnTypeError that's more informative than KeyError :-)

For every table that's reflected, it loads the available domains. This can probably improve so the domains are cached on a per uri and scheme. Also, I haven't had time to test specifying a schema yet -- probably doing that tomorrow, as well as adding support for when use_information_schema is true.

• Alex Brasetvik [e-mail] (Feel free to add me to the CC, I don't have access to modify it.)

[Mike Bayer]

id like the "no column type found" to produce a warning (using the warnings module) and to use the NullType in that case (also maybe we should rename "NullType"...but thats a different issue). in particular, psycopg2 handles python types very well so usually no type instrumentation is needed.

patch looks fantastic overall, nice unit tests.

[Former user]

Thanks a lot, guys! I was writing a small project as part of a personal "learning python" kick, so I'm in no position to fix this myself. I'm really impressed with the language and SQLAlchemy overall, but my databases use lots of domains. Fortunately, that's no longer an issue! Once 0.3.9 comes out, I suspect you've just gained (another) loyal user.

-Dave

[Former user]

Updated patch respects schemes and uses NullType for unknown data types. Added tests for schema-awareness that requires the user of the test database to have access to create objects in the "alt_schema"-schema.

As discussed on IRC, I'm ignoring the information_schema-way of reflecting the table, since querying pg_catalog directly seems to be the favorable way of doing it.

• Alex Brasetvik [e-mail]

[Former user]

Adds basic support for domains in PostgreSQL

[Former user]

Adds basic support for domains in PostgreSQL

[Former user]

Two unit tests had the same name, so updated the patch to fix that -- managed to not check the replace-box first time around. Sorry about that.

[Mike Bayer]

this worked out great, and is committed in changeset:2815. note that I am caching the "domains" collection per dialect once its reflected (note that a Dialect is created per-engine/connection pool). this is to avoid adding latency to a reflection of many tables. also removed superfluous "schema" argument from the query, and ensured that the correct type is getting reflected in the unit tests.

[Mike Bayer]

Removing milestone: 0.3.9 (automated comment)