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
