On Fri, Sep 10, 2021 at 12:40:11PM +0200, Christoph Zwerschke wrote:
> On 10.09.2021 00:41, Justin Pryzby wrote:
> > python3 -c "import pg; db=pg.DB('postgres'); q=db.query('SELECT
> > array_agg(column_name) AS cols FROM
> > information_schema.columns').getresult(); print(q[0][0][0])"
> >
> > I think it should treat this as array[text] rather than text.
> >
> > But I'm certain that it shouldn't cause errors in the logs:
>
> Hi Justin, thanks for reporting. I filed an issue for this here:
> https://github.com/PyGreSQL/PyGreSQL/issues/65
Thanks - sql_identifier is just an example.
The issue is that pygres tries to use typname::regtype, which fails when the
typnamespace (schema) is not in the search path.
For example, with default search path, these fail:
postgres=# SELECT typname FROM pg_type WHERE
typnamespace='information_schema'::regnamespace;
postgres=# SELECT typname::regtype FROM pg_type WHERE
oid='information_schema.views'::regtype;
ERROR: type "views" does not exist
[pryzbyj@telsasoft ~]$ strace -fe sendto,recvfrom -s333 python3 -c "import pg;
db=pg.DB('postgres'); q=db.query(\"SELECT
'{1}'::information_schema.cardinal_number[] AS a \").getresult();
print(q[0][0][0])"
... 0Mtype \"_cardinal_number\" does not exist\0Wunnamed portal with
parameters: $1 = '13" ...
{
But works like this:
strace -fe sendto,recvfrom -s333 python3 -c "import pg; db=pg.DB('postgres');
db.query(\"SET search_path=information_schema\"); q=db.query(\"SELECT
'{1}'::information_schema.cardinal_number[] AS a \").getresult();
print(q[0][0][0])"
1
The rest work fine.
SELECT typname::regtype FROM pg_type WHERE typname!='any' AND NOT
oid::regtype::text~'information_schema|any';
Even for user types, this works:
new=# CREATE TABLE t();
new=# SELECT 't'::regtype;
regtype | t
But not:
new=# CREATE SCHEMA s; CREATE TABLE s.t2(); SELECT 't2'::regtype;
ERROR: type "t2" does not exist
LINE 1: SELECT 't2'::regtype;
The solution is to use oid::regtype (rather than typname::regtype).
self._query_pg_type = (
"SELECT oid, typname, oid::regtype,"
The docs show that's available since v9.3
https://www.postgresql.org/docs/9.3/catalog-pg-type.html
But I checked that it exists since 8.4, but wasn't documented until
160701f6a935d5b0440751c3cb3f70bb59cf5eb1
BTW, I think the casts like ::regtype should be written as
::pg_catalog.regtype, same as psql describe.c and pg_dump.c
--
Justin
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo/pygresql
