Thread: [HACKERS] 'text' instead of 'unknown' in Postgres 10
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
Hi
2017-02-07 15:14 GMT+01:00 Daniele Varrazzo <daniele.varrazzo@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Daniele Varrazzo <daniele.varrazzo@gmail.com> writes: > testing with psycopg2 against Postgres 10 I've found a difference in > behaviour regarding literals, which are returned as text instead of > unknown. ... > Is this behaviour here to stay? Is there documentation for this change? Yup, see https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e7c4bb0049732ece651d993d03bb6772e5d281a The expectation is that clients will never see "unknown" output columns anymore. > 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. I think you need to rethink that anyway, because in the old code, whether such a value came back as text or unknown was dependent on context, for example regression=# select pg_typeof(x) from (select '' as x) ss;pg_typeof -----------unknown (1 row) regression=# select pg_typeof(x) from (select distinct '' as x) ss;pg_typeof -----------text (1 row) HEAD yields "text" for both of those cases, which seems a much saner behavior to me. I don't have enough context to suggest a better definition for psycopg ... but maybe you could pay some attention to the Python type of the value you're handed? > It seems > this behaviour cannot be maintained on PG 10 and instead users need to > specify some form of cast for their placeholder. Well, no version of PG has ever allowed this without a cast: regression=# select array[]; ERROR: cannot determine type of empty array so I'm not sure it's inconsistent for the same restriction to apply in the case you're describing. I'm also unclear on why you are emphasizing the point of the array being empty, because '{1,2,3}'::unknown would have the same behavior. regards, tom lane
On 02/07/2017 03:14 PM, Daniele Varrazzo wrote: > 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)], '{}') As Tom wrote this is the result of an intentional change, but no matter if that change is a good thing or not the above behavior sounds rather fragile. To me it does not seem safe to by default just assume that '{}' means the empty array, it might also have been intended to be the Python string "{}", the empty JSON object, or entirely something different. Andreas
On Tue, Feb 7, 2017 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Daniele Varrazzo <daniele.varrazzo@gmail.com> writes: >> testing with psycopg2 against Postgres 10 I've found a difference in >> behaviour regarding literals, which are returned as text instead of >> unknown. ... >> Is this behaviour here to stay? Is there documentation for this change? > > Yup, see > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1e7c4bb0049732ece651d993d03bb6772e5d281a > > The expectation is that clients will never see "unknown" output columns > anymore. Ok thank you, I'll document the change in behaviour. > I don't have enough context to suggest a better definition for psycopg > ... but maybe you could pay some attention to the Python type of the value > you're handed? In python the only type is the list, there is no specific "list of integer" or such. >> It seems >> this behaviour cannot be maintained on PG 10 and instead users need to >> specify some form of cast for their placeholder. > > Well, no version of PG has ever allowed this without a cast: > > regression=# select array[]; > ERROR: cannot determine type of empty array > > so I'm not sure it's inconsistent for the same restriction to apply in the > case you're describing. I'm also unclear on why you are emphasizing the > point of the array being empty, because '{1,2,3}'::unknown would have the > same behavior. The inconsistency is on our side: on python list [1,2,3] we generate 'ARRAY[1,2,3]', and empty lists are instead converted to '{}' precisely because there is no such thing like unknown[] - nor we can generate array[]::int[] because the Python list is empty and we don't know if it would have contained integers or other stuff. Of course this only works because we merge arguments in the adapter: moving to use PQexecParams we couldn't allow that anymore and the user should be uniformly concerned with adding casts to their queries (this is a non-backward compatible change so only planned for a mythical psycopg3 version I've long desired to write but for which I have no resource). Thank you for the clarification: I will assume the behaviour cannot be maintained on PG 10 and think whether the treatment of '{}' is too magical and drop it instead. -- Daniele
On Tue, Feb 7, 2017 at 2:59 PM, Andreas Karlsson <andreas@proxel.se> wrote: > On 02/07/2017 03:14 PM, Daniele Varrazzo wrote: >> >> 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)], '{}') > > > As Tom wrote this is the result of an intentional change, but no matter if > that change is a good thing or not the above behavior sounds rather fragile. > To me it does not seem safe to by default just assume that '{}' means the > empty array, it might also have been intended to be the Python string "{}", > the empty JSON object, or entirely something different. Yes, it could be actually the case to drop it. The case for it is quite thin anyway: if something comes from a query it will usually have a type attached. -- Daniele
On 2/7/17 9:16 AM, Daniele Varrazzo wrote: > Thank you for the clarification: I will assume the behaviour cannot be > maintained on PG 10 and think whether the treatment of '{}' is too > magical and drop it instead. BTW, I would hope that passing '{}' into a defined array field still works, since an empty array isn't treated the same as NULL, which means you need some way to create an empty array. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On Mon, Feb 13, 2017 at 3:09 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > On 2/7/17 9:16 AM, Daniele Varrazzo wrote: >> >> Thank you for the clarification: I will assume the behaviour cannot be >> maintained on PG 10 and think whether the treatment of '{}' is too >> magical and drop it instead. > > > BTW, I would hope that passing '{}' into a defined array field still works, > since an empty array isn't treated the same as NULL, which means you need > some way to create an empty array. Yes, that didn't change. The issue was only reading data from postgres to python. There is a beta of next psycopg version available on testpypi which implements the new behaviour, if you want to take a look at it. You can use pip install -i https://testpypi.python.org/pypi psycopg2==2.7b1 to install it. -- Daniele