Extension.query requires update for PostgreSQL 12

[declaresub]

In postgresql 12, the tables pg_namespace, pg_catalog now have oid columns. Thus the sql statement in Extension.query at pyrseas.dbobject.extension.py:37 now results in a psycopg2.errors.AmbiguousColumn exception.

[jmafc]

I guess you mean pg_roles instead of pg_catalog. In any case, the unqualified oid in that query I believe should be e.oid. In general, if you find further AmbiguousColumn exceptions due to similarly unqualified columns, if I'm not mistaken, they should be qualified with the alias of the first table in the FROM clause. I'm pointing all this out because I don't know how soon I'll be able to fix this.

[declaresub]

In postgresql 10, pg_namespace.oid, pg_catalog.oid were hidden attributes. They are no longer hidden in postgresql 12, hence the problem. I've tested the fix in my code, and did not encounter any other similar surprises when running dbtoyaml on a new database.

I'll try to send a pull request with the fix.

[jmafc]

I'm puzzled as to why you keep referring to pg_catalog as it were a table or view. pg_catalog is a namespace (aka schema). Anyway, a PR would be welcome. Thanks.

[declaresub]

I'm puzzled as well, since the changed tables are pg_namespace and pg_extension. Too much going on in my head, perhaps.

[declaresub]

As it turns out, I believe the current query is incorrect for pg version < 12. Here it is.

SELECT extname AS name, nspname AS schema, extversion AS version, rolname AS owner, obj_description(e.oid, 'pg_extension') AS description, oid FROM pg_extension e JOIN pg_roles r ON (r.oid = extowner) JOIN pg_namespace n ON (extnamespace = n.oid) WHERE nspname != 'information_schema' ORDER BY extname

Since pg_extension.oid and pg_namespace.oid were hidden, pg was assuming that oid means pg_roles.oid, which was public. One can confirm this by running the query. Presumably the intent was to return the oid of the extension.

I didn't see any bug reports that might be explained by this, so perhaps it was harmless. But I'll just go ahead and qualify everything to be explicit.

[jmafc]

Yes, the oid at the end of the select list was added to most queries by @dvarrazzo when he was working on the topological sort of all the objects for dependency tracking. See #119. I would prefer to use a "natural" key but that seems to be too difficult to achieve. I thought there were other unqualified oids but grepping shows only extension.py has one (at least as the last attribute in the select list, which IIRC was the way dvarrazzo added them).

[jmafc]

Fixed in PR #208