Re: The contents of the pg_timezone_names view bring some surprises

On 5/22/21 10:55 AM, Peter J. Holzer wrote:
> On 2021-05-22 08:26:27 -0700, Adrian Klaver wrote:
>> On 5/22/21 3:09 AM, Peter J. Holzer wrote:
>>> On 2021-05-19 06:57:13 -0700, Adrian Klaver wrote:
>>>> On 5/18/21 11:31 PM, Bryn Llewellyn wrote:
>>>>> This claims (as I read it) that a time zone abbreviation uniquely
>>>>> determines an offset from UTC.
>>>>
>>>> It says no such thing
>>>
>>> Maybe that's the inherent ambiguity of the English language, but to me
>>> "Such a specification defines a particular offset from UTC" does imply a
>>> one-to-one mapping from abbreviation to offset.
>>
>> As your later post points out the reality is a given abbreviation has only
>> one offset, but an offset may have many abbreviations.
>
> Which is what Bryn wrote. (But I realize I'm getting into an argument
> about what another person meant - again. I should stop that. If Bryn
> thinks it is important to be understood he can explain himself.)
>
>
>>>> and would be lying if it did.
>>>
>>> As far as pg_timezone_abbrevs is concerned, it is correct. abbrev is
>>> unique in that view:
>>
>> 1) Go to Postgres cluster and in ~/share/timezonesets search in Defaults.txt
>> for the word C(c)onflict.
>>
>> 2)Then go here
>> https://www.postgresql.org/docs/current/datetime-config-files.html and see
>> how the below query could be made to change if someone is not aware of 1).
>
> Actually, no. If you introduce a conflict in a timezoneset file the
> database doesn't even start:
>
> 2021-05-22 19:47:17 CEST [399103]: [1-1] user=,db=,pid=399103 FATAL: time zone abbreviation "test" is multiply defined
> 2021-05-22 19:47:17 CEST [399103]: [2-1] user=,db=,pid=399103 DETAIL: Entry in time zone file "Default", line 634, conflicts with entry in file "Default", line 635.
> pg_ctl: could not start server
>
> So abbrev is a unique key on pg_timezone_abbrevs and the documentation
> is correct.

I see no unique key. That led me to the wrong assumption that duplicate
abbreviations could exist in the view.

I do see this, which I had not looked at before:

https://www.postgresql.org/docs/12/datetime-config-files.html

"The @OVERRIDE syntax indicates that subsequent entries in the file can
override previous entries (typically, entries obtained from included
files). Without this, conflicting definitions of the same timezone
abbreviation are considered an error."

"
Note

If an error occurs while reading the time zone abbreviation set, no new
value is applied and the old set is kept. If the error occurs while
starting the database, startup fails.
"

So the error I'm thinking, comes during the reading and processing of
the file contents in pg_timezone_abbrevs(), which is what the view is
built on.

I learned something, Postgres errors before allowing a conflict.

>
> hp
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com