The following query doesn't work with my PostgreSQL 9.0.4 setup:
psycopg2.ProgrammingError: ('cannot cast type regclass to name
LINE 2: ... CASE WHEN contypid = 0 THEN conrelid::regclass::name
', "SELECT nspname AS schema,
CASE WHEN contypid = 0 THEN conrelid::regclass::name
ELSE contypid::regtype::name END AS table,
conname AS name,
CASE WHEN contypid != 0 THEN 'd' ELSE '' END AS target,
contype AS type, conkey AS keycols,
condeferrable AS deferrable,
condeferred AS deferred,
confrelid::regclass AS ref_table, confkey AS ref_cols,
consrc AS expression, confupdtype AS on_update,
confdeltype AS on_delete, amname AS access_method,
obj_description(c.oid, 'pg_constraint') AS description
FROM pg_constraint c
JOIN pg_namespace ON (connamespace = pg_namespace.oid)
LEFT JOIN pg_class on (conname = relname)
LEFT JOIN pg_am on (relam = pg_am.oid)
WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
ORDER BY schema, 2, name")
I executed the query against PG 9.0.6 using psql and didn't get an error. Can you please try doing that? Also, please tell me what version of psycopg2 you are using (I've tested with 2.4.4 on Python 2.6, 2.7 and 3.2).
Hmm... I can execute query via psql, but dbtoyaml fails. psycopg2 2.4.5, Python 2.7.2 on FreeBSD 8.2
That narrows it to a psycopg2 2.4.5 problem. I'll try to reproduce in a virtualenv.
Same with 2.4.4, 2.4.3 and PG 9.0.5
Also cannot reproduce from iPython shell using same version of psycopg2:
select conrelid::regclass::name from pg_constraint limit 10
works just fine.
I have tested in a virtualenv that had the following: Pyrseas master from GitHub, psycopg2 2.4.5, PyYAML 3.10, distribute 0.6.24 and Python 2.7, against PG 9.0.6. All unit tests passed, including tests/dbobjects/test_constraints.py. I also ran dbtoyaml against pyrseas_testdb after the latter tests (which leaves some primary key/foreign key constraints in the database), and got no errors.
I'm not sure if your comment "Same with 2.4.4, 2.4.3 and PG 9.0.5" means you did reproduce the problem with those versions or not. If it didn't reproduce, maybe you should try again with your original versions but in a different area/virtualenv.
Hello Andrey, please give me an update when you have a chance.
I've upgraded to Pg 9.1 and now it seems to work.
Closing issue since we can't reproduce.