Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

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