Обсуждение: Issue with DateStyle and pgbouncer

Hello,

trawling on twitter and lurking on github I've intercepted some cries
of pain caused by psycopg+pgbouncer regarding DateStyle. psycopg only
works with ISO style dates: usually it knows the DateStyle on
connection [1] and changes it if the setting isn't compatible.
Unfortunately pgbouncer doesn't send the DateStyle setting on
connection, and psycopg conservatively sets DateStyle to ISO when the
information is missing: on pgbouncer this may mean an extra query
every query. Not amusing.

Because I'm going to release version 2.5 soon, so it's a good time to
break something. What I'd do is: if the DateStyle info is missing
(i.e. you are on pgbouncer or other "broken" middleware) just assume
the date style is correct. This would break dates adaptation for users
of a database with non-standard DateStyle (e.g. "German") running
through pgbouncer: for these users there is an easy fix: pass the
extra DateStyle=ISO option at connection, either in the connection
string:

    psycopg2.connect("host=here user=that options='-c DateStyle=ISO' ")

or as an extra connection parameter:

    psycopg2.connect(host="here" user="that" options="-c DateStyle=ISO")

or using an env variable:

    $ export PGOPTIONS='-c DateStyle=ISO'
    ...
    >>> psycopg2.connect(DSN) # unchanged

It's a one char change in the code and a quite tricky case to document...

Comments?

Thank you,

-- Daniele

[1] http://www.postgresql.org/docs/current/static/libpq-status.html#LIBPQ-PQPARAMETERSTATUS

On Mon, Mar 18, 2013 at 01:53:29PM +0000, Daniele Varrazzo wrote:
> trawling on twitter and lurking on github I've intercepted some cries
> of pain caused by psycopg+pgbouncer regarding DateStyle. psycopg only
> works with ISO style dates: usually it knows the DateStyle on
> connection [1] and changes it if the setting isn't compatible.
> Unfortunately pgbouncer doesn't send the DateStyle setting on
> connection, and psycopg conservatively sets DateStyle to ISO when the
> information is missing: on pgbouncer this may mean an extra query
> every query. Not amusing.

I assume you are talking about the bug fixed in PgBouncer 1.4?

Or are there problems with more recent versions of PgBouncer?

> Because I'm going to release version 2.5 soon, so it's a good time to
> break something. What I'd do is: if the DateStyle info is missing
> (i.e. you are on pgbouncer or other "broken" middleware) just assume
> the date style is correct.

Good idea.

> This would break dates adaptation for users
> of a database with non-standard DateStyle (e.g. "German") running
> through pgbouncer: for these users there is an easy fix: pass the
> extra DateStyle=ISO option at connection, either in the connection
> string:
>
>     psycopg2.connect("host=here user=that options='-c DateStyle=ISO' ")
>
> or as an extra connection parameter:
>
>     psycopg2.connect(host="here" user="that" options="-c DateStyle=ISO")
>
> or using an env variable:
>
>     $ export PGOPTIONS='-c DateStyle=ISO'
>     ...
>     >>> psycopg2.connect(DSN) # unchanged
>
> It's a one char change in the code and a quite tricky case to document...

This suggestion is useless for PgBouncer as it will refuse such
connection attempt.  But maybe it will work on whatever other
middleware you have in mind.

--
marko

On Mon, Mar 18, 2013 at 7:15 PM, Marko Kreen <markokr@gmail.com> wrote:
> On Mon, Mar 18, 2013 at 01:53:29PM +0000, Daniele Varrazzo wrote:
>> trawling on twitter and lurking on github I've intercepted some cries
>> of pain caused by psycopg+pgbouncer regarding DateStyle. psycopg only
>> works with ISO style dates: usually it knows the DateStyle on
>> connection [1] and changes it if the setting isn't compatible.
>> Unfortunately pgbouncer doesn't send the DateStyle setting on
>> connection, and psycopg conservatively sets DateStyle to ISO when the
>> information is missing: on pgbouncer this may mean an extra query
>> every query. Not amusing.
>
> I assume you are talking about the bug fixed in PgBouncer 1.4?
>
> Or are there problems with more recent versions of PgBouncer?

I assume whatever they use at Instagram:

https://github.com/Instagram/psycopg2/commit/6f02e8f214e751568f38140d747fb6f93063a847

I remember having reported the issue to PgBouncer a long time ago, but
apparently somebody's still affected.

-- Daniele

On Mon, Mar 18, 2013 at 7:15 PM, Marko Kreen <markokr@gmail.com> wrote:
> On Mon, Mar 18, 2013 at 01:53:29PM +0000, Daniele Varrazzo wrote:

>> Unfortunately pgbouncer doesn't send the DateStyle setting on
>> connection, and psycopg conservatively sets DateStyle to ISO when the
>> information is missing: on pgbouncer this may mean an extra query
>> every query. Not amusing.
>
> I assume you are talking about the bug fixed in PgBouncer 1.4?
>
> Or are there problems with more recent versions of PgBouncer?
>
>> Because I'm going to release version 2.5 soon, so it's a good time to
>> break something. What I'd do is: if the DateStyle info is missing
>> (i.e. you are on pgbouncer or other "broken" middleware) just assume
>> the date style is correct.
>
> Good idea.
>
>> This would break dates adaptation for users
>> of a database with non-standard DateStyle (e.g. "German") running
>> through pgbouncer: for these users there is an easy fix: pass the
>> extra DateStyle=ISO option at connection, either in the connection
>> string:
>>
>>     psycopg2.connect("host=here user=that options='-c DateStyle=ISO' ")
> [...]

> This suggestion is useless for PgBouncer as it will refuse such
> connection attempt.  But maybe it will work on whatever other
> middleware you have in mind.

Because the issue should have been fixed in recent PgBouncer versions
and the suggested workaround wouldn't work, I'd not rush releasing
such a change, at least not until we get more information about the
issue.

-- Daniele