Horton

Thread

Commits

  1. Make postgres_fdw request remote time zone 'GMT' not 'UTC'.

  1. postgres_fdw fails because GMT != UTC

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-04-04T06:19:45Z

    Over at [1] we have a complaint of postgres_fdw failing with
a remote-server error

> ERROR:  invalid value for parameter "TimeZone": "UTC"

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.

Thoughts?

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org

  2. Re: postgres_fdw fails because GMT != UTC

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-04-04T06:48:57Z

    On Thu, 2024-04-04 at 02:19 -0400, Tom Lane wrote:
> > ERROR:  invalid value for parameter "TimeZone": "UTC"
> 
> 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.

I think the first is desirable for reasons of general sanity, and the
second for best compatibility with old versions.

So I vote for "both".

Yours,
Laurenz Albe

  3. Re: postgres_fdw fails because GMT != UTC

    Daniel Gustafsson <daniel@yesql.se> — 2024-04-04T09:08:32Z

    > On 4 Apr 2024, at 08:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Over at [1] we have a complaint of postgres_fdw failing with
> a remote-server error
> 
>> ERROR:  invalid value for parameter "TimeZone": "UTC"
> 
> 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.

+1. It makes little sense to support GMT like that but not UTC.

> 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.

There is always a risk in accomodating broken installations that it might hide
other subtle bugs, but off the cuff that risk seems quite low in this case.

--
Daniel Gustafsson

  4. Re: postgres_fdw fails because GMT != UTC

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2024-04-10T11:30:59Z

    On Thu, Apr 4, 2024 at 3:49 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Thu, 2024-04-04 at 02:19 -0400, Tom Lane wrote:
> > > ERROR:  invalid value for parameter "TimeZone": "UTC"
> >
> > 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.
>
> I think the first is desirable for reasons of general sanity, and the
> second for best compatibility with old versions.
>
> So I vote for "both".

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

Best regards,
Etsuro Fujita

  5. Re: postgres_fdw fails because GMT != UTC

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-04-19T20:14:56Z

    Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Thu, Apr 4, 2024 at 3:49 PM Laurenz Albe <laurenz.albe@cybertec.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