'text' instead of 'unknown' in Postgres 10

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: 'text' instead of 'unknown' in Postgres 10
Date: 2017-02-07 14:14:48
Message-ID: CA+mi_8ajbRGHwU5zEXVia-GZ92H_0KsrWWJ7UjXcVQAznpZ2dA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

testing with psycopg2 against Postgres 10 I've found a difference in
behaviour regarding literals, which are returned as text instead of
unknown. In previous versions:

In [2]: cnn = psycopg2.connect('')
In [3]: cur = cnn.cursor()
In [7]: cur.execute("select 'x'")
In [9]: cur.description[0][1]
Out[9]: 705

In pg10 master:

In [10]: cnn = psycopg2.connect('dbname=postgres host=localhost port=54310')
In [11]: cur = cnn.cursor()
In [12]: cur.execute("select 'x'")
In [13]: cur.description[0][1]
Out[13]: 25

what is somewhat surprising is that unknown seems promoted to text "on
the way out" from a query; in previous versions both columns of this
query would have been "unknown".

postgres=# select pg_typeof('x'), pg_typeof(foo) from (select 'x' as foo) x;
pg_typeof | pg_typeof
-----------+-----------
unknown | text

Is this behaviour here to stay? Is there documentation for this change?

In psycopg '{}'::unknown is treated specially as an empty array and
converted into an empty list, which allows empty lists to be passed to
the server as arrays and returned back to python. Without the special
case, empty lists behave differently from non-empty ones. It seems
this behaviour cannot be maintained on PG 10 and instead users need to
specify some form of cast for their placeholder. Previously this would
have worked "as expected" and the 4th argument would have been an
empty list:

cur.execute("SELECT %s, %s, %s, %s", (['x'], [42], [date(2017,1,1)],
[])); cur.fetchone()
(['x'], [42], [datetime.date(2017, 1, 1)], '{}')

Should I just take this test off from the test suite and document the
adapter as behaving differently on PG 10?

Thank you very much

-- Daniele

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-02-07 14:17:25 Re: [COMMITTERS] pgsql: Release note updates.
Previous Message Stephen Frost 2017-02-07 14:00:37 Re: GSoC 2017 - LAST CALL