Обсуждение: [ADMIN] FATAL: invalid value for parameter "TimeZone" after upgrade from 9.2to 9.6
Good morning.
--
My issue is more or less similar to http://www.postgresql-archive.org/Upgrading-from-PG-8-3-3-to-9-3-4-FATAL-invalid-value-for-parameter-quot-TimeZone-quot-quot-PST-quot-td5805665.html
However what I'm looking to answer is why this wasn't fatal in 9.2? From that other thread, it seems like the change occurred in 9.3, and in the 9.3 release notes I see this:
Sync our copy of the timezone library with IANA release tzcode2017c (Tom Lane)
This fixes various issues; the only one likely to be user-visible is that the default DST rules for a POSIX-style zone name, if no posixrules file exists in the timezone data directory, now match current US law rather than what it was a dozen years ago.
Would this explain why the app server was able to connect with timezone='CST' (in my case) in 9.2 but not in 9.6? Our workaround was either to use CST6CDT or just not set it altogether (which is what we chose since we're always in US/Central).
For what it's worth this is on CentOS 6.
Don.
Don Seiler
www.seiler.us
www.seiler.us
Don Seiler <don@seiler.us> writes: > My issue is more or less similar to > http://www.postgresql-archive.org/Upgrading-from-PG-8-3-3-to-9-3-4-FATAL-invalid-value-for-parameter-quot-TimeZone-quot-quot-PST-quot-td5805665.html > However what I'm looking to answer is why this wasn't fatal in 9.2? It was. I don't think PG has ever accepted just "PST" (or "CDT") as a timezone name. There's no such entry in the IANA tz database, and it's not a legal POSIX zone specification --- you'd need at least "PST8" for it to go through that code path. I tried it just now on 7.4.30, which is the oldest version I have on this machine (about 2 years older than 9.2.0) and it failed as expected: regression=# set timezone = 'PST'; ERROR: unrecognized time zone name: "PST" Now, it's barely conceivable that you strong-armed 9.2 into accepting it by making a file or symlink named "PST" in the timezone database directory of the old installation ... but otherwise, I'm quite certain it would not have taken it. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
I agree that it isn't valid, and I get that same error when I try to set the timezone to CST after I connect.
The difference though is when the client sets that timezone when connecting, that's when the error is listed as FATAL and the connections fail in 9.6 (and also 9.3 according to the previous thread), where as the connections didn't fail in 9.2 and earlier. That's the behavior change I'm looking to account for.
Don.
On Fri, Nov 17, 2017 at 9:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Don Seiler <don@seiler.us> writes:
> My issue is more or less similar to
> http://www.postgresql-archive.org/Upgrading-from-PG-8-3-3- to-9-3-4-FATAL-invalid-value- for-parameter-quot-TimeZone- quot-quot-PST-quot-td5805665. html
> However what I'm looking to answer is why this wasn't fatal in 9.2?
It was. I don't think PG has ever accepted just "PST" (or "CDT") as a
timezone name. There's no such entry in the IANA tz database, and it's
not a legal POSIX zone specification --- you'd need at least "PST8" for
it to go through that code path. I tried it just now on 7.4.30, which is
the oldest version I have on this machine (about 2 years older than 9.2.0)
and it failed as expected:
regression=# set timezone = 'PST';
ERROR: unrecognized time zone name: "PST"
Now, it's barely conceivable that you strong-armed 9.2 into accepting
it by making a file or symlink named "PST" in the timezone database
directory of the old installation ... but otherwise, I'm quite certain
it would not have taken it.
regards, tom lane
Don Seiler
www.seiler.us
www.seiler.us
Don Seiler <don@seiler.us> writes:
> The difference though is when the client sets that timezone when
> connecting, that's when the error is listed as FATAL and the connections
> fail in 9.6 (and also 9.3 according to the previous thread), where as the
> connections didn't fail in 9.2 and earlier. That's the behavior change I'm
> looking to account for.
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.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On Fri, Nov 17, 2017 at 9:36 AM, Tom Lane <tgl@sss.pgh.pa.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.
In our case the client app was running through Tomcat, and had a setenv.sh that had "-Duser.timezone=CST". That wasn't causing any known issues until we upgraded our dev database yesterday, and our pre-prod and prod app servers have that same setting and are up and running vs 9.2.22 still. As I said before, we have a workaround/fix (using CST6CDT or just removing that parameter altogether from the options string), but given that everything else is the same, I have to believe something changed in how Postgres handles those connections in 9.3 and beyond. I'm just very curious to know for certain what that was. I'm sure there's more than a few people on Postgres <= 9.2 that might hit this when upgrading in the near future (now that 9.2 is EOL).
Don.
--
Don.
Don Seiler
www.seiler.us
www.seiler.us
On Fri, Nov 17, 2017 at 9:43 AM, Don Seiler <don@seiler.us> wrote:
On Fri, Nov 17, 2017 at 9:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.given that everything else is the same, I have to believe something changed in how Postgres handles those connections in 9.3 and beyond.
I may have spoken too soon. We did upgrade the Postgres JDBC drivers on that client app server yesterday, from 9.1-903 to 42.1.4 for Java 1.6. Perhaps they changed something along the way there.
Don.
Don Seiler
www.seiler.us
www.seiler.us
Don Seiler <don@seiler.us> writes:
> On Fri, Nov 17, 2017 at 9:36 AM, Tom Lane <tgl@sss.pgh.pa.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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On Fri, Nov 17, 2017 at 9:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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?
Yes this did change yesterday as well, I noted in a follow-up.
However, looking at the catalina.out file, I see these fatal timezone parameter messages back to October of last year, then nothing until yesterday. My guess is they got this same error, then fixed it somehow that apparently didn't stay fixed.
--
Don Seiler
www.seiler.us
www.seiler.us
On Fri, Nov 17, 2017 at 10:05 AM, Don Seiler <don@seiler.us> wrote:
On Fri, Nov 17, 2017 at 9:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: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?Yes this did change yesterday as well, I noted in a follow-up.
Looks like this is definitely related to JDBC driver change. We stopped the app and put the 9.1 jdbc jar back into place, and put the "-Duser.timezone=CST" back into setenv.sh, and it started and connected just fine. We do the same thing with the 42.1.4 jdbc jar and we get the FATAL errors until we take that user.timezone parameter out. Everything else stays equal (DB is 9.6.6).
My working guess right now is that the older JDBC was ignoring that user.timezone parameter when the connection was made. Would be interested in finding out when change happened to confirm.
Don.
Don Seiler
www.seiler.us
www.seiler.us
On Fri, Nov 17, 2017 at 10:31 AM, Don Seiler <don@seiler.us> wrote:
> I ask because a look at the PG JDBC code shows this, which did not
> show up until Sept 22, 2011. Not sure what release, but it looks like
> 9.2+:
>
> // Construct and send a startup packet.
> String[][] params = {
> { "user", user },
> { "database", database },
> { "client_encoding", "UTF8" },
> { "DateStyle", "ISO" },
> { "extra_float_digits", "2" },
> { "TimeZone",
My working guess right now is that the older JDBC was ignoring that user.timezone parameter when the connection was made. Would be interested in finding out when change happened to confirm.
Looks like this was actually answered in that other thread:
> show up until Sept 22, 2011. Not sure what release, but it looks like
> 9.2+:
>
> // Construct and send a startup packet.
> String[][] params = {
> { "user", user },
> { "database", database },
> { "client_encoding", "UTF8" },
> { "DateStyle", "ISO" },
> { "extra_float_digits", "2" },
> { "TimeZone",
> createPostgresTimeZone() },
Looks like that is in ConnectionFactoryImpl.java but the current git repo history only goes back to 2015. But at this point I'm satisfied in knowing that upgrading from JDBC 9.1 to a newer JDBC introduced the change we see.
Thanks,
On Fri, Nov 17, 2017 at 10:40 AM, Don Seiler <don@seiler.us> wrote:
Looks like this was actually answered in that other thread:> I ask because a look at the PG JDBC code shows this, which did not
> show up until Sept 22, 2011. Not sure what release, but it looks like
> 9.2+:
>
Just FYI that I confirmed this code appears in pgjdbc 9.2-1004, and is NOT in 9.1-903.
Mystery solved. Thanks Tom for talking me off the ledge.