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