Re: 'text' instead of 'unknown' in Postgres 10

Hi

2017-02-07 15:14 GMT+01:00 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>:

> 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
>

I see similar issue in plpgsql_check

create function test_t(OUT t) returns t AS $$
begin
$1 := null;
end;
$$ language plpgsql;

Now the "null" is text type implicitly ("unknown" was before)

select * from plpgsql_check_function('test_t()', performance_warnings :=
true);

plpgsql_check_function................................
--------------------------------------------------------------------------------------
warning:42804:3:assignment:target type is different type than source type
Detail: cast "text" value to "integer" type
Hint: The input expression type does not have an assignment cast to the
target type.
(3 rows)

It is a regression from my view - unknown had more sense in this case.

Regards

Pavel

>
> -- Daniele
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>