Re: postgres_fdw fails because GMT != UTC

Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> writes:
> On Thu, Apr 4, 2024 at 3:49 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>> On Thu, 2024-04-04 at 02:19 -0400, Tom Lane wrote:
>>> I am not quite clear on how broken an installation needs to be to
>>> reject "UTC" as a time zone setting, except that the breakage cannot
>>> be subtle. However, I notice that our code in pgtz.c and other
>>> places treats "GMT" as a hard-wired special case ... but not "UTC".
>>> I wonder if we ought to modify those places to force "UTC" down the
>>> same hard-wired paths. If we acted like that, this would have worked
>>> no matter how misconfigured the installation was.
>>>
>>> An alternative answer could be to change postgres_fdw to send "GMT"
>>> not "UTC". That's ugly from a standards-compliance viewpoint, but
>>> it would fix this problem even with a non-updated remote server,
>>> and I think postgres_fdw is generally intended to work with even
>>> very old remote servers.
>>>
>>> Or we could do both.

> +1 for both (assuming that the latter does not make the postgres_fdw
> code complicated).

I looked briefly at changing the server like this, and decided that
it would be a little invasive, if only because there would be
documentation and such to update. Example question: should we change
the boot-time default value of the timezone GUC from "GMT" to "UTC"?
Probably, but I doubt we want to back-patch that, nor does it seem
like something to be messing with post-feature-freeze. So I'm
in favor of working on that when the tree opens for v18, but not
right now.

However, we can change postgres_fdw at basically no cost AFAICS.
That's the more important part anyway I think. If your own server
burps because it's got a bad timezone database, you are probably in
a position to do something about that, while you may have no control
over a remote server. (As indeed the original complainant didn't.)

So I propose to apply and back-patch the attached, and leave
it at that for now.

regards, tom lane