tgl@xxxxxxxxxxxxx wrote:
adrian.klaver@xxxxxxxxxxx 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.
