Re: Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST" - Mailing list pgsql-general

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