Thread: [MASSMAIL]postgres_fdw fails because GMT != UTC
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
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
> 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
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
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 diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 603e043af4..33e8054f64 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -671,10 +671,12 @@ configure_remote_session(PGconn *conn) * anyway. However it makes the regression test outputs more predictable. * * We don't risk setting remote zone equal to ours, since the remote - * server might use a different timezone database. Instead, use UTC - * (quoted, because very old servers are picky about case). + * server might use a different timezone database. Instead, use GMT + * (quoted, because very old servers are picky about case). That's + * guaranteed to work regardless of the remote's timezone database, + * because pg_tzset() hard-wires it (at least in PG 9.2 and later). */ - do_sql_command(conn, "SET timezone = 'UTC'"); + do_sql_command(conn, "SET timezone = 'GMT'"); /* * Set values needed to ensure unambiguous data output from remote. (This