From: Tom Lane <tgl@sss.pgh.pa.us>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: postgres_fdw fails because GMT != UTC
In-reply-to: 
 <CAPmGK14WwfHhmHKbPiM0uU-vo4ifMNrM4cPAj9ZMeyKhCkSPYw@mail.gmail.com>
References: <465248.1712211585@sss.pgh.pa.us>
 <c6d1a1f8900965b915b2ff6a6eb8d0413a6ae67e.camel@cybertec.at>
 <CAPmGK14WwfHhmHKbPiM0uU-vo4ifMNrM4cPAj9ZMeyKhCkSPYw@mail.gmail.com>
Comments: In-reply-to Etsuro Fujita <etsuro.fujita@gmail.com>
	message dated "Wed, 10 Apr 2024 20:30:59 +0900"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <2233357.1713557670.0@sss.pgh.pa.us>
Date: Fri, 19 Apr 2024 16:14:56 -0400
Message-ID: <2233391.1713557696@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2233391.1713557696%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2233357.1713557670.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Thu, Apr 4, 2024 at 3:49=E2=80=AFPM Laurenz Albe <laurenz.albe@cybert=
ec.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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="make-postgres_fdw-send-GMT-not-UTC.patch";
	charset="us-ascii"
Content-ID: <2233357.1713557670.2@sss.pgh.pa.us>
Content-Description: make-postgres_fdw-send-GMT-not-UTC.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/conn=
ection.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 predictabl=
e.
 	 *
 	 * 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 =3D 'UTC'");
+	do_sql_command(conn, "SET timezone =3D 'GMT'");
 =

 	/*
 	 * Set values needed to ensure unambiguous data output from remote.  (Th=
is

------- =_aaaaaaaaaa0--