The following bug has been logged on the website:
Bug reference: 15177
Logged by: Bugs Bunny
Email address: eponymousalias@yahoo.com
PostgreSQL version: 9.6.8
Operating system: Linux (CentOS 6.9)
Description:
We recently upgraded our PostgreSQL install from 9.6.5 to 9.6.8,
and now we have a serious problem that blocks access to the database.
We tracked it down to a change in PostgreSQL 9.6.7, wherein support for
the US/Pacific-New timezone was dropped. This timezone *must* be restored
to a standard-release database, in spite of the prior release notes that
dismissed it as just an alias for another timezone. Let me explain.
Our application uses a large stack of third-party code that we don't
directly control. Part of that code creates connections to the database,
and part of that work involves conditioning the individual connection,
presumably with a "SET TIME ZONE {timezone}" command. Unfortunately,
the 3/p code is selecting US/Pacific-New as that timezone. And that
causes PostgreSQL to reject the conditioning with an error message,
as seen in the postmaster.log file:
FATAL: invalid value for parameter "TimeZone": "US/Pacific-New"
Now, this is an unusual timezone; you may ask, why did the 3/p code
select it? In fact, my system timezone is set to America/Los_Angeles,
as can be seen by:
% cat /etc/sysconfig/clock
ZONE="America/Los_Angeles"
Alas, setting the timezone has evolved over time and between different
Linux distros, so the 3/p code doesn't know about that method for finding
the timezone name on my particular release (CentOS release 6.9). Instead,
it finds the /etc/localtime file, and uses that data. Unfortunately,
the tzdata file format is effectively broken, in that it contains all the
data for time calculation, but fails to include the actual timezone name.
So the 3/p code scans the /usr/share/zoneinfo/... file tree looking for
a match to the data it found in the /etc/localtime file. As soon as it
finds a matching file, it takes that trailing pathname as the name of
the timezone, and uses it to condition the PostgreSQL connection.
Now, it so happens that on my machine, US/Pacific-New is the first
matching timezone it finds. This timezone is part of the standard Olson
timezone database, found in the tzdata package under Linux. We have no
control over the order in which the 3/p code does directory traversals
to find the first matching file; that is all up to the order in which
the filesystem presents entries in a directory scan. This will vary from
machine to machine, even on the same Linux distro and release. The ext4
filesystem, for instance, normally lists files in some kind of hash-key
order, where some details of the hash are defined when the filesystem
is first created. Different ext4 filesystems may have different hash
seeds, and so might present the files in different orders even if all
of the files in the two filesystems are identical.
The upshot of all this is that removing the US/Pacific-New timezone from
PostgreSQL was a breaking change, and it has to be reversed. I understand
the desire to drop this supposedly deprecated and little-used timezone,
but it is critical for present real-world systems. The way to get rid of
it is to press the Olson timezone database maintainers to first drop it
on their end. And then ten years later, once that change has percolated
throughout the computing ecosystem, you'll finally be able to drop it
from PostgreSQL. Until then, it has to stay available.
Secondly, now I have a PostgreSQL 9.6.8 install without this timezone
name, and I need to add it to the pg_timezone_names view to sidestep
this silliness. Unfortunately, the direct approach doesn't work:
mydb=# INSERT INTO pg_timezone_names SELECT 'US/Pacific-New', abbrev,
utc_offset, is_dst FROM pg_timezone_names WHERE name = 'US/Pacific';
ERROR: cannot insert into view "pg_timezone_names"
DETAIL: Views that do not select from a single table or view are not
automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT
trigger or an unconditional ON INSERT DO INSTEAD rule.
So, how do I go about that? The documentation on System Views
(https://www.postgresql.org/docs/9.6/static/views-overview.html) says
"Except where noted, all the views described here are read-only.".
Further, digging into the definition of this view shows it is implemented
with some kind of function call rather than by SELECTing from various
tables:
mydb=# select definition from pg_views where viewname='pg_timezone_names';
definition
----------------------------------------------------------------------------------
SELECT pg_timezone_names.name,
+
pg_timezone_names.abbrev,
+
pg_timezone_names.utc_offset,
+
pg_timezone_names.is_dst
+
FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset,
is_dst);
(1 row)
So I don't even think that an INSTEAD OF INSERT trigger or an
unconditional ON INSERT DO INSTEAD rule will work.
Help! I'm stuck with no way forward and no good way back. The timezone
matching fails, the database connection fails, and finally our whole
application fails as a result of this seemingly trivial change and the
unfortunate interaction of multiple system components not under our
direct control.
Hint:
cd .../postgresql/share/timezone/US
cp -p Pacific Pacific-New
may be adequate, even without restarting the database (though I suppose
a dynamic change is subject to potentially nasty race conditions between
writing and reading the new file). But I'd like official blessing.
Finally, we can't be the only people in this boat, so whatever advice
is given here to address the problem in a deployed system needs to be
reflected in the documentation for 9.6.7 and 9.6.8 (and for equivalent
other 9.X.X and 10.X releases).