> t...@sss.pgh.pa.us <mailto:t...@sss.pgh.pa.us> wrote: > >> adrian.kla...@aklaver.com <mailto:adrian.kla...@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".
Thank you very much, Tom. I should have taken more care with my wording. You said it very well, of course, here. It seems to me, against the background that we've been discussing, that the pg_timezone_abbrevs view serves just this purpose: to provide a list of text strings that can be used as the argument for "at time zone" and as the tz specification in a timestamptz literal. Unfortunately, but for reasons that I can only accept, the pg_timezone_abbrevs view translates abbrev key values that are found into an offset value. But it doesn't necessarily error when presented with a key that it doesn't have. This, too, has been discussed at length in this exchanges. These examples make the point. set timezone = 'UTC'; select '2021-05-23 19:00:00 foo42bar'::timestamptz; select '2021-05-23 19:00:00'::timestamptz at time zone 'bar99foo'; Sadly, there's no simple way to enforce a practice for applications that want to avoid this risk unless, maybe, every such plain timestamp expression is constructed programmatically. There is hope for this: set timezone = 'bar99foo'; brought by the possibility of writing one's own set_timezone() function that checks the input values against pg_timezone_names.name. But it would win only half the battle.
