Date: Fri, 5 Apr 2024 11:22:32 +0200
From: Adnan Dautovic <daut@mailbox.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Subject: Re: Failure of postgres_fdw because of TimeZone setting
Message-ID: <8ACDD3C3-A339-4197-B918-F842921D5079@mailbox.org>
References: <5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org>
 <461604.1712210044@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: <461604.1712210044@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/8ACDD3C3-A339-4197-B918-F842921D5079%40mailbox.org>
Precedence: bulk

Hi Tom,

thank you for your reply!

Tom Lane <tgl@sss.pgh.pa.us> wrote:
>You realize of course that PG 9.4.x is four years past EOL, and that
>the last release in that series was 9.4.26, so that your remote is
>missing three or so years' worth of bug fixes even before its EOL.
>The underlying macOS platform looks a bit hoary as well.

Yes, but this is really out of my hands. My task is to perform
analysis on the remote database and my current attempt is to
interface with the data using postgres_fdw in my own database,
where I can put views and functions.

>(You gain exactly zero points for good maintenance practice on the
>local side either, since PG 13's current release is 13.14.  If you're
>going to install Postgres and then ignore bug-fix releases for
>multiple years, I counsel not starting from a dot-zero release.
>However, that doesn't seem to be related to your immediate problem.)

Agreed. :-) I inherited this docker container and am in the
process of getting it updated as we speak.

>My best guess is that the
>remote was built with a --with-system-tzdata setting that's not
>actually valid for its platform.

Interesting! From what I gathered, I can check the options that
were used by running `pg_config --configure`. Indeed, it appears
that the remote compiled their own Postgres and this is the
output of the above command:

'--prefix=/Applications/...' '--with-includes=/Applications/.../libopenssl/include:/Applications/.../libxml2/include' '--with-libraries=/Applications/.../libopenssl/lib:/Applications/.../libxml2/lib' '--enable-thread-safety' '--with-openssl' '--with-gssapi' '--with-bonjour' '--with-libxml' '--with-libxslt' '--with-python' '--with-readline' '--with-uuid=e2fs' 'CFLAGS=-Wno-error=implicit-function-declaration'

Alas, there does not seem to be a setting regarding the timezone.

Adrian Klaver's comment lead me to find out some more
information. Since this thread sort of split, I would continue
there. Please chime in in the other thread if you have further
suggestions.

Kind regards,

Adnan Dautovic