Horton

Re: 002_types.pl fails on some timezones on windows

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>, pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>, Petr Jelinek <petr@2ndquadrant.com>
Date: 2021-09-30T20:31:36Z
Lists: pgsql-hackers 
Hi,

On 2021-09-30 16:03:15 -0400, Tom Lane wrote:
> I wrote:
> > ... sure enough, 002_types.pl
> > falls over with TZ=Africa/Casablanca on my Linux machine, too.
> 
> Independently of whether Africa/Casablanca is a sane translation of
> that Windows zone name, it'd be nice if 002_types.pl weren't so
> sensitive to the prevailing zone.  I looked into exactly why it's
> falling over, and the answer seems to be this bit:

> 		(2, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '2 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[2,3]", "[20,30]"}'),
> 		(3, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '3 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[3,4]"}'),
> 		(4, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '4 days', 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz), '{"[4,5]", NULL, "[40,50]"}'),
> 
> The problem with this is the blithe assumption that "minus N days"
> is an immutable computation.  It ain't.  As bad luck would have it,
> these intervals all manage to cross a Moroccan DST boundary
> (Ramadan, I assume):

For a minute I was confused, because of course we should still get the same
result on the subscriber as on the publisher. But then I re-re-re-realized
that the comparison data is a constant in the test script...


> What I'm inclined to do about that is get rid of the totally-irrelevant-
> to-this-test interval subtractions, and just write the desired timestamps
> as constants.

Sounds like a plan.

Greetings,

Andres Freund

Commits

  1. Doc: improve timezone/README's recipe for tracking Windows zones.

  2. Update our mapping of Windows time zone names some more.

  3. Update our mapping of Windows time zone names using CLDR info.

  4. Re-alphabetize the win32_tzmap[] array.

  5. Remove gratuitous environment dependency in 002_types.pl test.