Re: FATAL: invalid value for parameter "TimeZone" after upgrade from 9.2 to 9.6

Don Seiler <don(at)seiler(dot)us> writes:
> On Fri, Nov 17, 2017 at 9:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Ah, gotcha. I believe that the FATAL case occurs if the client tries
>> to set an invalid parameter value within the connection startup packet,
>> rather than after connecting. But that behavior hasn't changed in
>> a long time either. I think what you need to be looking for is a
>> client-side change in how/when it sends a desired timezone setting to
>> the server.

> That's what I thought as well. However the only thing that changed
> yesterday was upgrading Postgres from 9.2.22 to 9.6.6. The app didn't
> change and the Postgres timezone stayed at 'US/Central' as it has been all
> year. I'm assuming David Wall had a similar scenario in his 8.3.3-to-9.3.4
> upgrade.

I think you need to look harder for what else you changed --- perhaps a
new JDBC driver version rode along with the core-PG update?

I can reproduce the FATAL case in psql+libpq:

$ PGTZ=PST psql
psql: FATAL: invalid value for parameter "TimeZone": "PST"

However, I can reproduce that back to 7.4.30 as well. I am entirely
confident that you are not looking at a server behavior change here.

> In our case the client app was running through Tomcat, and had a setenv.sh
> that had "-Duser.timezone=CST".

The relevant question then would be just when/how the JDBC driver tries
to propagate that setting to the server. I'm not up on their release
history, but it wouldn't surprise me a bit if they'd changed at some point
to send the timezone setting in the startup packet rather than later.
It'd be a sensible change to eliminate one network round trip during
connection startup. We made libpq do it that way in 7.4 when we
introduced the capability of having parameter settings in the startup
packet, but it likely would have taken awhile for other drivers to get
on board.

regards, tom lane