Thread: pgsql: Update time zone data files to tzdata release 2024b.

Update time zone data files to tzdata release 2024b.

Historical corrections for Mexico, Mongolia, and Portugal.
Notably, Asia/Choibalsan is now an alias for Asia/Ulaanbaatar
rather than being a separate zone, mainly because the differences
between those zones were found to be based on untrustworthy data.

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/502e7bf7f09c458ce66ce043b94864b535c5d5d0

Modified Files
--------------
src/timezone/data/tzdata.zi     | 1653 +++++++++++++++++++--------------------
src/timezone/known_abbrevs.txt  |    2 -
src/timezone/tznames/Default    |    6 +-
src/timezone/tznames/Europe.txt |    6 +-
4 files changed, 824 insertions(+), 843 deletions(-)

Andrew Dunstan <andrew@dunslane.net> writes:
> Crake doesn't seem to like this for cross version upgrade, and the diff
> looks rather odd:

Oh, that's annoying.  I forgot to mention the side-effects that 2024b
had on PST8PDT and other SysV-derived timezones (probably because Paul
Eggert avoided mentioning that in his release notes ... thanks for
nothing Paul).  What we've got there is that sufficiently old
timestamps (pre 1890 or so) are now interpreted as local mean solar
time for Los Angeles rather than exactly UTC-8.

I would expect that the upgrade tests would pass now for upgrades
from v12 or later, thanks to b8ea0f675 et al, but I'm not real
sure what to do about testing the out-of-support branches.  Should
we back-patch b8ea0f675 as far down as 9.2?

            regards, tom lane

> On Oct 29, 2024, at 5:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Crake doesn't seem to like this for cross version upgrade, and the diff
>> looks rather odd:
>
> Oh, that's annoying.  I forgot to mention the side-effects that 2024b
> had on PST8PDT and other SysV-derived timezones (probably because Paul
> Eggert avoided mentioning that in his release notes ... thanks for
> nothing Paul).  What we've got there is that sufficiently old
> timestamps (pre 1890 or so) are now interpreted as local mean solar
> time for Los Angeles rather than exactly UTC-8.
>
> I would expect that the upgrade tests would pass now for upgrades
> from v12 or later, thanks to b8ea0f675 et al, but I'm not real
> sure what to do about testing the out-of-support branches.  Should
> we back-patch b8ea0f675 as far down as 9.2?
>
>

Or fix/remove the problem rows in AdjustUpgrade.pm I guess

Cheers

Andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> On Oct 29, 2024, at 5:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I would expect that the upgrade tests would pass now for upgrades
>> from v12 or later, thanks to b8ea0f675 et al, but I'm not real
>> sure what to do about testing the out-of-support branches.  Should
>> we back-patch b8ea0f675 as far down as 9.2?

> Or fix/remove the problem rows in AdjustUpgrade.pm I guess

I went to try to test this locally, and got nowhere, because:

    old cluster does not use data checksums but the new one does
    Failure, exiting

What have you done to get around that on crake?

            regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:
> On Wed, Oct 30, 2024 at 11:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I went to try to test this locally, and got nowhere, because:
>>     old cluster does not use data checksums but the new one does
>>     Failure, exiting
>> What have you done to get around that on crake?

> I did this:
> https://github.com/PGBuildFarm/client-code/commit/2ef5acefe5e6eee761ea2ee010c3d14e2d8fab60

Ah, thanks.  I installed that locally, and got

...
tester:HEAD          [16:25:31] saving files for cross-version upgrade check
tester:HEAD          [16:25:33] checking upgrade from REL9_2_STABLE to HEAD ...
tester:HEAD          [16:25:53] checking upgrade from REL_11_STABLE to HEAD ...
tester:HEAD          [16:26:31] checking upgrade from REL_12_STABLE to HEAD ...
tester:HEAD          [16:27:08] checking upgrade from REL_13_STABLE to HEAD ...
tester:HEAD          [16:27:46] checking upgrade from REL_14_STABLE to HEAD ...
tester:HEAD          [16:28:26] checking upgrade from REL_15_STABLE to HEAD ...
tester:HEAD          [16:29:05] checking upgrade from REL_16_STABLE to HEAD ...
tester:HEAD          [16:29:47] checking upgrade from HEAD to HEAD ...
tester:HEAD          [16:30:32] running make ecpg check ...
tester:HEAD          [16:30:40] OK

(This client instance doesn't have back-branch runs for v9.3--v10.)

So now I'm even more confused.  Why does it work here and not there?
I can't think of any buildfarm options that would affect this.

            regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:
> On Wed, Oct 30, 2024 at 4:53 PM Andrew Dunstan <andrew@dunslane.net> wrote:
>> My 9.2 saved instance is quite old ... Nov 2018.
>> But I don't see why that should affect it, so I'm confused too.

> Further data point - drongo and fairywren are not showing this issue.

My 9.2 reference instance is relatively new, from Mar 2024.

I tried looking at the relevant data in the saved dump:

$ grep 1697-02 upgrade.tester/REL9_2_STABLE/origin-REL9_2_STABLE.sql
1697-02-16 17:32:01
1697-02-16 20:35:59-04:56:02

(The first match is presumably out of timestamp_tbl, the second
out of timestamptz_tbl.)  It's the same in the saved dumps for
later versions too, which is why I'm not getting upgrade test
failures.  But how come?

After contemplating this for awhile, I thought of the beginnings
of an explanation: pg_regress selects the test timezone by
setting environment variable PGTZ, not by using ALTER DATABASE SET
or anything like that.  So the effects extend only to the
regression test runs themselves, and if you look at the contents
of the regression database afterwards, data will be shown
according to your default timezone established by initdb.
In particular, if we run pg_dump without taking any special care,
we'll get timestamps dumped in the initdb-determined zone.

So the reason I'm not seeing a failure is that my dumps were
all taken under my system's default zone, America/New_York,
which would report this timestamp value as '20:35:59-04:56:02'
and has done so for a long time.  I speculate that on crake,
the system timezone changed sometime since you made that 2018
dump.  If it had changed from a SysV zone like PST8PDT to one of
the modern geographical zones, that would fit the symptoms.
However, I see in crake's initdb tests

selecting default time zone ... EST5EDT

and I see crake claims to be on Fedora 40, which has not adopted
tzdata 2024b yet (at least, it hasn't here), so the fact that
EST5EDT will change behavior whenever that does happen should
not be relevant yet.  So I'm fuzzy about the exact details of
what has happened on crake, but I'm pretty sure that it boils
down to "the dump timezone changed since 2018".

I'm inclined to propose that we should modify the pg_upgrade test
so that it forces the comparison dumps to be taken with PGTZ=UTC,
thereby taking system-timezone changes out of the picture.  This'd
require remaking the old comparison dumps on (most?) upgrade-testing
animals, of course.  In the meantime you could probably make crake
happy by forcing re-runs of the old branches.

            regards, tom lane

I wrote:
> ... So I'm fuzzy about the exact details of
> what has happened on crake, but I'm pretty sure that it boils
> down to "the dump timezone changed since 2018".

Oh ... never mind, I was thinking in terms of what would happen
with --use-system-timezone, but of course the buildfarm doesn't
build that way.

So the problem is precisely that *our* interpretation of EST5EDT
changed when we adopted tzdata 2024b, and that is affecting how
we dump these old timestamps.  Or at least, that seems like what
should be happening, but then why is only crake showing a failure?

> I'm inclined to propose that we should modify the pg_upgrade test
> so that it forces the comparison dumps to be taken with PGTZ=UTC,
> thereby taking system-timezone changes out of the picture.

Whatever the details exactly, this still seems like a good
future-proof fix.  I'm not quite sure where we need to make the change
though --- if I edit pg_upgrade/t/002_pg_upgrade.pl, will that affect
how the buildfarm tests these things, or is it different code?

            regards, tom lane

I wrote:
> So the problem is precisely that *our* interpretation of EST5EDT
> changed when we adopted tzdata 2024b, and that is affecting how
> we dump these old timestamps.  Or at least, that seems like what
> should be happening, but then why is only crake showing a failure?

Oh, got it: of the machines in question, only crake is selecting
EST5EDT as default timezone.  I can see from the buildfarm logs
that drongo and fairywren are using UTC, and as said, my test
instance is selecting America/New_York.  Both UTC and America/New_York
would have rendered these old timestamps the same way all along,
but EST5EDT just changed its interpretation of them.

That means that simply forcing a re-run of the old branches on
crake won't fix it, because pre-v12 branches will still think
that EST5EDT means what it used to.  We need to make sure that
the dumps are taken in a completely stable zone, i.e. UTC.

            regards, tom lane