Thread: Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

I am posting to both PG and the JDBC group since I am using JDBC to
connect, so I'm not sure if this is a PG issue or a PG JDBC issue.

I am upgrading from 8.3.3 to 9.3.4.  I ran pg_dump on my 8.3.3 databases
(which ran on CentOS 5.2, Java 6) and ran pg_restore on my 9.3.4
databases (which runs on CentOS 6.4, Java 7.0.55, using JDBC
postgresql-9.3-1101.jdbc41.jar).  The new databases appear to be correct
in that I can run psql and query my tables and I don't see any obvious
issues.

In fact, I seem to be able to run several applications at the same time,
each connecting to its own database.

But I also see that sometimes I get this error in my postgresql.log file:

  FATAL:  invalid value for parameter "TimeZone": "PST"

I normally run about 35-40 webapps at a time in our dev environment
without issue, but when I try to start them all, it seems this error
occurs on nearly every application.  Yet when I only run about 10, I
don't seem to see the issue at all.  I have tried adding them in one at
a time to see if there was a "rogue" webapp, but when I find a newly
added one suffers the TimeZone FATAL error above, if I restart Tomcat,
it may then be fine.  Apps that run okay at one time, will exhibit the
error when I try to add the others in.

Does anybody have any ideas on what I might try?  I did a 'yum update'
to get the latest, rebooted, recompiled PG and reloaded the DBs, but
that made no difference.

Thanks for any tips if anybody has seen this!

David

On 5/31/2014 11:41 PM, David Wall wrote:
>
>  FATAL:  invalid value for parameter "TimeZone": "PST"

I'd be using "America/Los_Angeles" as the timezone rather than PST, as
the TLA timezones are ambiguous (CST is both Central Standard Time in
the USA, and China Standard Time).

but thats probably not the problem, hard to guess what is, sounds like
you have a lot going on.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

> On 5/31/2014 11:47 PM, John R Pierce wrote:
>> On 5/31/2014 11:41 PM, David Wall wrote:
>>>
>>>  FATAL:  invalid value for parameter "TimeZone": "PST"
>>
>> I'd be using "America/Los_Angeles" as the timezone rather than PST,
>> as the TLA timezones are ambiguous (CST is both Central Standard Time
>> in the USA, and China Standard Time).
>>
>> but thats probably not the problem, hard to guess what is, sounds
>> like you have a lot going on.
>

PG itself has the timezone US/Pacific set in postgresql.conf

In Linux, we show:
lrwxrwxrwx 1 root root 39 Apr 23 16:00 /etc/localtime ->
/usr/share/zoneinfo/America/Los_Angeles

Even when we use PST in our Java code, we use PST8PDT.

The exception occurs when JDBC tries to connect to PG:

2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException:
ConnectionPool.makeConnection(esf) to URL:
jdbc:postgresql://localhost.localdomain:25432/zingr:
2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid
value for parameter "TimeZone": "PST"
org.postgresql.util.PSQLException: FATAL: invalid value for parameter
"TimeZone": "PST"
         at
org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:574)
         at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:177)
         at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
         at
org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:138)
         at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
         at
org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
         at
org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
         at
org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
         at org.postgresql.Driver.makeConnection(Driver.java:410)
         at org.postgresql.Driver.connect(Driver.java:280)
         at java.sql.DriverManager.getConnection(DriverManager.java:571)
         at java.sql.DriverManager.getConnection(DriverManager.java:215)
         at
com.esignforms.db.ConnectionPool.makeConnection(ConnectionPool.java:302)

And of course none seems to explain why it works often enough, but then
fails as more apps are deployed.  Ugh!

On 6/1/2014 12:16 AM, David Wall wrote:
> at
> com.esignforms.db.ConnectionPool.makeConnection(ConnectionPool.java:302)

that sounds like some form of connection pool.

perhaps connections are being shared between client processes that
expect different timezones?

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 06/01/2014 12:16 AM, David Wall wrote:
>> On 5/31/2014 11:47 PM, John R Pierce wrote:
>>> On 5/31/2014 11:41 PM, David Wall wrote:
>>>>
>>>>  FATAL:  invalid value for parameter "TimeZone": "PST"
>>>
>>> I'd be using "America/Los_Angeles" as the timezone rather than PST,
>>> as the TLA timezones are ambiguous (CST is both Central Standard Time
>>> in the USA, and China Standard Time).
>>>
>>> but thats probably not the problem, hard to guess what is, sounds
>>> like you have a lot going on.
>>
>
> PG itself has the timezone US/Pacific set in postgresql.conf
>
> In Linux, we show:
> lrwxrwxrwx 1 root root 39 Apr 23 16:00 /etc/localtime ->
> /usr/share/zoneinfo/America/Los_Angeles
>
> Even when we use PST in our Java code, we use PST8PDT.
>
> The exception occurs when JDBC tries to connect to PG:
>
> 2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException:
> ConnectionPool.makeConnection(esf) to URL:
> jdbc:postgresql://localhost.localdomain:25432/zingr:
> 2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid
> value for parameter "TimeZone": "PST"
> org.postgresql.util.PSQLException: FATAL: invalid value for parameter
> "TimeZone": "PST"

> And of course none seems to explain why it works often enough, but then
> fails as more apps are deployed.  Ugh!

What version of Postgres JDBC are you using on your 8.3.3 machine?

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",
createPostgresTimeZone() },


  /**
      * Convert Java time zone to postgres time zone.
      * All others stay the same except that GMT+nn changes to GMT-nn and
      * vise versa.
      *
      * @return The current JVM time zone in postgresql format.
      */
     private String createPostgresTimeZone() {
         String tz = TimeZone.getDefault().getID();
         if (tz.length() <= 3 || !tz.startsWith("GMT")) {
             return tz;
         }
         char sign = tz.charAt(3);
         String start;
         if (sign == '+') {
             start = "GMT-";
         } else if (sign == '-') {
             start = "GMT+";
         } else {
             // unknown type
             return tz;
         }

         return start + tz.substring(4);

Ahh I see the problem.

 From here:
A time zone abbreviation, for example PST. Such a specification merely
defines a particular offset from UTC, in contrast to full time zone
names which can imply a set of daylight savings transition-date rules as
well. The recognized abbreviations are listed in the pg_timezone_abbrevs
view (see Section 47.70). You cannot set the
                                               ^^^^^^^^^^^^^^^^^^
configuration parameters TimeZone or log_timezone to a time zone
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
abbreviation, but you can use abbreviations in date/time input values
^^^^^^^^^^^^^
and with the AT TIME ZONE operator.


So:

test=> select version();
                                                            version



-----------------------------------------------------------------------------------------------------------------------------


  PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit

(1 row)

test=> set TimeZone='PST';
ERROR:  invalid value for parameter "TimeZone": "PST"
test=> set TimeZone='PST8PDT';
SET

The JDBC code above, if I am following correctly, is picking up a
default timezone of 'PST' and then in the first if returning that as the
tz value to SET TimeZone in the startup packet.

Two things.

1) Where is it getting PST from ?

2) Should the driver even be returning an abbreviation given that
Postgres will not accept it as a TimeZone value?




>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

David Wall <d.wall@computer.org> writes:
> The exception occurs when JDBC tries to connect to PG:

> 2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException:
> ConnectionPool.makeConnection(esf) to URL:
> jdbc:postgresql://localhost.localdomain:25432/zingr:
> 2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid
> value for parameter "TimeZone": "PST"
> org.postgresql.util.PSQLException: FATAL: invalid value for parameter
> "TimeZone": "PST"

Hm.  libpq will try to set TimeZone at connection time if it sees a value
for the environment variable "PGTZ"; so, if you were using libpq, this
would be explainable by different environment settings in different cases.
There is probably some comparable behavior in the JDBC driver, but I don't
know exactly what.

One hole in this type of theory is that it doesn't explain a behavioral
difference between 8.3.x and 9.3.x; unless maybe the JDBC driver's
behavior changed in this regard since then.  libpq has done the PGTZ thing
for a very long time.

            regards, tom lane

On 6/1/2014 9:05 AM, Adrian Klaver 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",
> createPostgresTimeZone() },
>
>
>  /**
>      * Convert Java time zone to postgres time zone.
>      * All others stay the same except that GMT+nn changes to GMT-nn and
>      * vise versa.
>      *
>      * @return The current JVM time zone in postgresql format.
>      */
>     private String createPostgresTimeZone() {
>         String tz = TimeZone.getDefault().getID();
>         if (tz.length() <= 3 || !tz.startsWith("GMT")) {
>             return tz;
>         }
>         char sign = tz.charAt(3);
>         String start;
>         if (sign == '+') {
>             start = "GMT-";
>         } else if (sign == '-') {
>             start = "GMT+";
>         } else {
>             // unknown type
>             return tz;
>         }
>
>         return start + tz.substring(4);
>
> Ahh I see the problem.
>
> From here:
> A time zone abbreviation, for example PST. Such a specification merely
> defines a particular offset from UTC, in contrast to full time zone
> names which can imply a set of daylight savings transition-date rules
> as well. The recognized abbreviations are listed in the
> pg_timezone_abbrevs view (see Section 47.70). You cannot set the
>                                               ^^^^^^^^^^^^^^^^^^
> configuration parameters TimeZone or log_timezone to a time zone
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> abbreviation, but you can use abbreviations in date/time input values
> ^^^^^^^^^^^^^
> and with the AT TIME ZONE operator.
>
>
> So:
>
> test=> select version();
>                                                            version
>
>
-----------------------------------------------------------------------------------------------------------------------------

>
>  PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
> 4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit
> (1 row)
>
> test=> set TimeZone='PST';
> ERROR:  invalid value for parameter "TimeZone": "PST"
> test=> set TimeZone='PST8PDT';
> SET
>
> The JDBC code above, if I am following correctly, is picking up a
> default timezone of 'PST' and then in the first if returning that as
> the tz value to SET TimeZone in the startup packet.
>
> Two things.
>
> 1) Where is it getting PST from ?
>
> 2) Should the driver even be returning an abbreviation given that
> Postgres will not accept it as a TimeZone value?
>


Thanks for the extra help, Adrian.

It led me to investigate the 35 webapps we deploy on Tomcat and I found
2 rogue apps that set their timezone to "PST".  Once I fixed these two,
all is working great again.

I guess there's no bug, per se, except in our configuration.  Once we
changed it to PST8PDT, all was good again.

Thanks,
David

On 06/01/2014 11:42 AM, David Wall wrote:
>
> On 6/1/2014 9:05 AM, Adrian Klaver wrote:

>> The JDBC code above, if I am following correctly, is picking up a
>> default timezone of 'PST' and then in the first if returning that as
>> the tz value to SET TimeZone in the startup packet.
>>
>> Two things.
>>
>> 1) Where is it getting PST from ?
>>
>> 2) Should the driver even be returning an abbreviation given that
>> Postgres will not accept it as a TimeZone value?
>>
>
>
> Thanks for the extra help, Adrian.
>
> It led me to investigate the 35 webapps we deploy on Tomcat and I found
> 2 rogue apps that set their timezone to "PST".  Once I fixed these two,
> all is working great again.
>
> I guess there's no bug, per se, except in our configuration.  Once we
> changed it to PST8PDT, all was good again.

Not so much a bug as a misplaced error. Given that TimeZone does not
accept abbreviations, it would seem that JDBC code should throw an
exception at that point. It would not change the end result, but make it
easier to determine where the error is occurring. In the long run it may
be moot though, because of this:

http://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html

Three-letter time zone IDs
For compatibility with JDK 1.1.x, some other three-letter time zone IDs
(such as "PST", "CTT", "AST") are also supported. However, their use is
deprecated because the same abbreviation is often used for multiple time
zones (for example, "CST" could be U.S. "Central Standard Time" and
"China Standard Time"), and the Java platform can then only recognize
one of them.

>
> Thanks,
> David
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com