Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 5/22/21 5:52 PM, Bryn Llewellyn 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.
regards, tom lane