Don't require dynamic timezone abbreviations to match underlying time zone

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 2 September 2016 16:30 UTC

Previously, we threw an error if a dynamic timezone abbreviation did not match any abbreviation recorded in the referenced IANA time zone entry. That seemed like a good consistency check at the time, but it turns out that a number of the abbreviations in the IANA database are things that Olson and crew made up out of whole cloth. Their current policy is to remove such names in favor of using simple numeric offsets. Perhaps unsurprisingly, a lot of these made-up abbreviations have varied in meaning over time, which meant that our commit b2cbced9e and later changes made them into dynamic abbreviations. So with newer IANA database versions that don't mention these abbreviations at all, we fail, as reported in bug #14307 from Neil Anderson. It's worse than just a few unused-in-the-wild abbreviations not working, because the pg_timezone_abbrevs view stops working altogether (since its underlying function tries to compute the whole view result in one call).

We considered deleting these abbreviations from our abbreviations list, but the problem with that is that we can't stay ahead of possible future IANA changes. Instead, let's leave the abbreviations list alone, and treat any "orphaned" dynamic abbreviation as just meaning the referenced time zone. It will behave a bit differently than it used to, in that you can't any longer override the zone's standard vs. daylight rule by using the "wrong" abbreviation of a pair, but that's better than failing entirely. (Also, this solution can be interpreted as adding a small new feature, which is that any abbreviation a user wants can be defined as referencing a time zone name.)

Back-patch to all supported branches, since this problem affects all of them when using tzdata 2016f or newer.

Report: <20160902031551.15674.67337@wrigleys.postgresql.org> Discussion: <6189.1472820913@sss.pgh.pa.us>

39b691f Don't require dynamic timezone abbreviations to match underlying time zone.
doc/src/sgml/catalogs.sgml | 7 +++
doc/src/sgml/datetime.sgml | 41 ++++++++++----
src/backend/utils/adt/datetime.c | 85 ++++++++++++++++++++++-------
src/test/regress/expected/timestamptz.out | 20 +++++++
src/test/regress/sql/timestamptz.sql | 11 ++++
5 files changed, 132 insertions(+), 32 deletions(-)

Upstream: git.postgresql.org


  • Share