From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Munro <thomas.munro@gmail.com>
cc: Michael Paquier <michael@paquier.xyz>,
        Vik Fearing <vik@postgresfriends.org>,
 cary huang <hcary328@gmail.com>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: Add support for AT LOCAL
In-reply-to: 
 <CA+hUKGK=DOC+hE-62FKfZy=Ybt5uLkrg3zCZD-jFykM-iPn8yw@mail.gmail.com>
References: 
 <169541920143.2315938.17412414971559687288.pgcf@coridan.postgresql.org>
 <dbea9877-f39b-1a5b-6e5b-5aad2bbc2821@postgresfriends.org>
 <ZRZ8ePmlhx_1LvVV@paquier.xyz>
 <c2c6f2c3-49e3-c1fe-65ca-49f275af41c2@postgresfriends.org>
 <ZR-jmKYU5BDQEAe4@paquier.xyz>
 <e3b4c42d-739d-4375-9401-2f62ad59d326@postgresfriends.org>
 <ZSTUWON7DOfo-2ES@paquier.xyz>
 <a2bd6444-7fe4-44cd-8cb6-d7ac13f4d3e0@postgresfriends.org>
 <ZSjCZJrxGL0-wv-G@paquier.xyz>
 <65b620bd-4f05-4381-97c3-5ec9ac39d5b9@postgresfriends.org>
 <ZSjQj34RZBY_HY0c@paquier.xyz>
 <CA+hUKGK=DOC+hE-62FKfZy=Ybt5uLkrg3zCZD-jFykM-iPn8yw@mail.gmail.com>
Comments: In-reply-to Thomas Munro <thomas.munro@gmail.com>
	message dated "Mon, 16 Oct 2023 10:16:47 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2129575.1697407071.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 15 Oct 2023 17:57:51 -0400
Message-ID: <2129576.1697407071@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2129576.1697407071%40sss.pgh.pa.us>
Precedence: bulk

Thomas Munro <thomas.munro@gmail.com> writes:
> One of the AIX animals gave a strange result here:
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=3Dhornet&dt=3D20=
23-10-15%2011%3A40%3A01

> If you ignore the diffs due to change in column width, the interesting
> change seems to be:

> -  23:59:00-07    | 06:59:00+00    | 06:59:00+00    | 06:59:00+00
> -  23:59:59.99-07 | 06:59:59.99+00 | 06:59:59.99+00 | 06:59:59.99+00
> +  23:59:00-07    | 4294966103:4294967295:00+00 |
> 4294966103:4294967295:00+00 | 4294966103:4294967295:00+00
> +  23:59:59.99-07 | 4294966103:00:00.01+00      |
> 4294966103:00:00.01+00      | 4294966103:00:00.01+00

> But the other AIX animal 'sungazer' was OK with it.  They're both on
> the same AIX7.1 host IIRC, both 64 bit builds, but the former is using
> xlc and the latter gcc.  I don't immediately see what would cause that
> underflow on that old compiler but not elsewhere.  I have a shell
> there (cfarm111) if someone has an idea...

Hmm.  Seems like the error has to be creeping in during this part
of timetz_zone():

	result->time =3D t->time + (t->zone - tz) * USECS_PER_SEC;
	while (result->time < INT64CONST(0))
		result->time +=3D USECS_PER_DAY;
	while (result->time >=3D USECS_PER_DAY)
		result->time -=3D USECS_PER_DAY;

According to my machine, the initial computation of result->time
(for the '23:59:59.99-07' input) yields 111599990000, and then we
iterate the second loop once to get 25199990000, which is the right
answer.  If I force a second iteration to get -61200010000, I get

# select '23:59:59.99-07'::timetz at local;
        timezone        =

------------------------
 4294967279:00:00.01+00
(1 row)

which doesn't quite match hornet's result but it seems
suggestively close.

Another line of thought is that while the time fields are int64,
t->zone and tz are only int32.  Multiplying by the INT64CONST
USECS_PER_SEC ought to be enough to make the compiler widen
the subtraction result to int64, but maybe that's screwing up?
I'm tempted to wonder if this helps:

-	result->time =3D t->time + (t->zone - tz) * USECS_PER_SEC;
+	result->time =3D t->time + (int64) (t->zone - tz) * USECS_PER_SEC;

Forcing the wrong thing to happen there doesn't produce a match
to hornet's result either, so I don't have a lot of hope for that
theory, but it seems like the explanation has to be somewhere here.

			regards, tom lane