Re: The contents of the pg_timezone_names view bring some surprises

tgl@sss.pgh.pa.us wrote:

adrian.klaver@aklaver.com wote:

Bryn wrote:

(1) In the context of discussing timezones, the English word "abbreviation" maps to two distinct notions in PG: "pg_timezone_names.abbrev"; and "pg_timezone_abbrevs.abbrev". Tom earlier said « there are two views [because there are] two sources of truth involved ». This really means that these two columns list different classes of facts. It's therefore unfortunate that they both have the same name.

They are reporting the same facts, just in different context. 

No, Bryn's right really. pg_timezone_names.abbrev reports out what the IANA (tzdb) database says is the abbreviation in use at the current time in the indicated time zone. pg_timezone_abbrevs.abbrev reports about names defined by the timezone_abbreviations configuration file. While those ought to be generally in sync, there's no expectation that they necessarily match one-to-one; they really can't given that the same abbreviation can be in use to mean different things in different parts of the world. IANA will happily report such inconsistent abbreviations, but timezone_abbreviations has to treat names as unique keys, so at most one meaning of a given abbreviation will be available there. Indeed there might be *no* entry there for an abbreviation that appears in some row of pg_timezone_names, particularly if an installation has chosen to trim the timezone_abbreviations contents in order to reduce the potential for bad data entry. Conversely, there are quite a few entries in our standard timezone_abbreviations list that match no entry in tzdb. (Most of them used to, but IANA has been on a kick lately of removing abbreviations that they invented.) And that's before you even get into the question of dubious custom mods of timezone_abbreviations. Or custom mods of the tzdb files, for that matter. Or timezone_abbreviations files that aren't in sync with the tzdb database that's in use, which is an extremely common situation given that we recommend use of "--with-system-tzdata".