From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksander Alekseev <aleksander@timescale.com>
cc: Michael Paquier <michael@paquier.xyz>, pgsql-bugs@lists.postgresql.org
Subject: Re: TimestampTz->Text->TimestampTz casting fails with DateStyle
 'Postgres'
In-reply-to: <394372.1733884998@sss.pgh.pa.us>
References: 
 <CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com>
 <3416600.1730987836@sss.pgh.pa.us>
 <CAJ7c6TMzJDLutYt+pcm1Jt+sDSRHjvjezM9o8heEoo_LwgP75A@mail.gmail.com>
 <CAJ7c6TMpBUMLqep4ycxySN+SFsf-eG2v-nUaVSzu2jSd5eDMAA@mail.gmail.com>
 <Z1eKmoJHXVd8_oz1@paquier.xyz> <4167039.1733796752@sss.pgh.pa.us>
 <394372.1733884998@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 10 Dec 2024 21:43:18 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <521764.1733949428.1@sss.pgh.pa.us>
Date: Wed, 11 Dec 2024 15:37:08 -0500
Message-ID: <521765.1733949428@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/521765.1733949428%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> As I set it up here, we first check the timezone abbreviation list,
> then look into the session timezone to see if it has an entry.
> I don't expect that this lookup will succeed for anything except LMT,
> because every other abbreviation that TZDB knows about is already
> listed in our standard abbreviation list.  But in the future we
> could imagine removing entries from the abbreviation list so that
> this code path takes more of the burden.

I had a second thought here.  The original problem is not really
restricted to "LMT", though that case tends to give an obvious
"no such timezone abbreviation" failure.  There is a more insidious
possibility that the cast-back-and-forth succeeds but yields a changed
timestamp value, because the abbreviation emitted by timestamptz_out
is recognized but interpreted differently by timestamptz_in.
I believe that that's at least theoretically possible today, because
what timestamptz_out prints for the abbreviation comes out of the
prevailing zone's TZDB entry, but what timestamptz_in consults is the
timezone_abbreviations list.  If our list is out of sync with TZDB,
even for just part of the history of a zone, we've got problems.

It may well be that there are no live problems today, especially
since TZDB has gotten rid of a lot of abbreviations that they
decided were made-up rather than in real-world usage.  I don't
feel like that's a great bet though, and even if it's true today
it might not be in the future.

So that leads me to wonder if we should flip the lookup order
and consult the prevailing zone's TZDB entry first, falling back
to timezone_abbreviations only if the abbrev is not known in the
current zone.

There are certainly reasons not to do that, one being that there
would be no way to override TZDB's opinion if you don't like it.
And I'd not propose it for back-patch.  But it's something to
consider.

			regards, tom lane