Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Daniele Varrazzo]

On Mon, Feb 13, 2017 at 3:09 AM, Jim Nasby  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


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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Jim Nasby]

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)


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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Daniele Varrazzo]

On Tue, Feb 7, 2017 at 2:59 PM, Andreas Karlsson  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


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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Daniele Varrazzo]

On Tue, Feb 7, 2017 at 2:42 PM, Tom Lane  wrote:
> Daniele Varrazzo  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


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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Andreas Karlsson]

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



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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Tom Lane]

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


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

Re: [HACKERS] 'text' instead of 'unknown' in Postgres 10

[Pavel Stehule]

Hi

2017-02-07 15:14 GMT+01:00 Daniele Varrazzo :

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

[HACKERS] 'text' instead of 'unknown' in Postgres 10

[Daniele Varrazzo]

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


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