Thread: [MASSMAIL]Failure of postgres_fdw because of TimeZone setting
Hi everyone, I have some trouble using postgres_fdw in order to display some data from a Postgres database I do not control in a Postgresdatabase that I do control. I filled out the form from the wiki below and would appreciate any tips. * A description of what you are trying to achieve and what results you expect.: I am trying to import the public schema of a Postgres instance I do not control (I will call it "remote"), but have read-onlyaccess to, into a Postgres instance I fully control (I will call it "local"), using the foreign data wrapper postgres_fdw. The result I expect is that the import runs successfully and that I have access to the foreign tables. However, I currently fail to achieve this. I always get an error message regarding the TimeZone setting. I also tried, withoutsuccess, different versions for the local instance, namely 9.4.14 so I would be as close as possible to the remoteversion and 13.14 to try an up-to-date version of Postgres 13. * PostgreSQL version number you are running: remote: "PostgreSQL 9.4.13 on x86_64-apple-darwin20.2.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit" local: "PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit" * How you installed PostgreSQL: remote: I do not know. It is an instance that runs as part of a software I have no further insight in. local: I am using the official PostgreSQL docker image. * Changes made to the settings in the postgresql.conf file: remote: "name" "current_setting" "source" "application_name" "pgAdmin 4 - CONN:2930678" "client" "bdr.log_conflicts_to_table" "on" "configuration file" "bytea_output" "hex" "session" "checkpoint_completion_target" "0.9" "configuration file" "checkpoint_segments" "32" "configuration file" "client_encoding" "UNICODE" "session" "client_min_messages" "notice" "session" "DateStyle" "ISO, MDY" "session" "default_text_search_config" "pg_catalog.english" "configuration file" "dynamic_shared_memory_type" "posix" "configuration file" "effective_cache_size" "19046MB" "configuration file" "lc_messages" "C" "configuration file" "lc_monetary" "C" "configuration file" "lc_numeric" "C" "configuration file" "lc_time" "C" "configuration file" "listen_addresses" "*" "configuration file" "log_line_prefix" "%t [%d]" "configuration file" "log_timezone" "Europe/Berlin" "configuration file" "logging_collector" "on" "configuration file" "maintenance_work_mem" "1587MB" "configuration file" "max_connections" "100" "configuration file" "max_replication_slots" "10" "configuration file" "max_stack_depth" "2MB" "environment variable" "max_wal_senders" "10" "configuration file" "max_worker_processes" "10" "configuration file" "port" "5432" "configuration file" "shared_buffers" "7936MB" "configuration file" "ssl" "on" "configuration file" "ssl_ca_file" "root.crt" "configuration file" "ssl_cert_file" "server.crt" "configuration file" "ssl_key_file" "server.key" "configuration file" "tcp_keepalives_idle" "10" "configuration file" "TimeZone" "Europe/Berlin" "configuration file" "track_commit_timestamp" "on" "configuration file" "wal_level" "logical" "configuration file" "wal_receiver_timeout" "20min" "configuration file" "wal_sender_timeout" "20min" "configuration file" "wal_sync_method" "fsync_writethrough" "configuration file" "work_mem" "81264kB" "configuration file" local: "name" "current_setting" "source" "application_name" "pgAdmin 4 - CONN:511122" "client" "bytea_output" "hex" "session" "client_encoding" "UNICODE" "session" "client_min_messages" "notice" "session" "DateStyle" "ISO, MDY" "session" "default_text_search_config" "pg_catalog.english" "configuration file" "dynamic_shared_memory_type" "posix" "configuration file" "lc_messages" "en_US.utf8" "configuration file" "lc_monetary" "en_US.utf8" "configuration file" "lc_numeric" "en_US.utf8" "configuration file" "lc_time" "en_US.utf8" "configuration file" "listen_addresses" "*" "configuration file" "log_timezone" "Etc/UTC" "configuration file" "max_connections" "100" "configuration file" "max_stack_depth" "2MB" "environment variable" "max_wal_size" "1GB" "configuration file" "min_wal_size" "80MB" "configuration file" "shared_buffers" "128MB" "configuration file" "TimeZone" "Etc/UTC" "configuration file" * Operating system and version: remote: Mac mini (2018), running macOS Monterey 12.5.1, output of uname -a: "Darwin mac 21.6.0 Darwin Kernel Version 21.6.0:Wed Aug 10 14:25:27 PDT 2022; root:xnu-8020.141.5-2/RELEASE_X86_64 x86_64 local: A Linux VM running Debian 11.3 ARM64, output of uname -a: "Linux debian-gnu-linux-11 5.10.0-28-arm64 #1 SMP Debian5.10.209-2 (2024-01-31) aarch64 GNU/Linux" * What program you're using to connect to PostgreSQL: pgAdmin 4 * Is there anything relevant or unusual in the PostgreSQL server logs?: No, not that I know of. * What you were doing when the error happened / how to cause the error: First, I successfully enabled the postgres_fdw extension, created the remote server configuration and the user mapping. Then, I tried to import the public schema locally, using: IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public; * The EXACT TEXT of the error message you're getting, if there is one: ERROR: invalid value for parameter "TimeZone": "UTC" CONTEXT: remote SQL command: SET timezone = 'UTC' SQL state: 22023 Now, I gather that the postgres_fdw extension sets this option for its connection here https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677 but I really can not figure out why it fails in this scenario and what I could do about it. I thought my next step might be to look into how to create my own foreign data wrapper and basically use a modified versionof postgres_fdw where I remove the TimeZone setting. It seems like there should be an easier way, though. :-) Does anyone have an idea for me? Kind regards, Adnan Dautovic
Adnan Dautovic <daut@mailbox.org> writes:
> I have some trouble using postgres_fdw in order to display some data from a Postgres database I do not control in a
Postgresdatabase that I do control.
Hmm ...
> * PostgreSQL version number you are running:
> remote: "PostgreSQL 9.4.13 on x86_64-apple-darwin20.2.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29),
64-bit"
> local: "PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0,
64-bit"
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.
(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.)
Having said that,
> * The EXACT TEXT of the error message you're getting, if there is one:
> ERROR: invalid value for parameter "TimeZone": "UTC"
> CONTEXT: remote SQL command: SET timezone = 'UTC'
That's just bizarre. There is no release of Postgres anywhere,
at any time in the past couple decades, that should not think that
"UTC" is a valid timezone setting. My best guess is that the
remote was built with a --with-system-tzdata setting that's not
actually valid for its platform.
Short answer: your remote database is very incompetently
administrated. If the remote's DBA is not willing to work on fixing
it, I suggest finding a job where you don't have to deal with that.
regards, tom lane
On 4/3/24 22:23, Adnan Dautovic wrote: > Hi everyone, > > > I have some trouble using postgres_fdw in order to display some data from a Postgres database I do not control in a Postgresdatabase that I do control. I filled out the form from the wiki below and would appreciate any tips. > > > * A description of what you are trying to achieve and what results you expect.: > I am trying to import the public schema of a Postgres instance I do not control (I will call it "remote"), but have read-onlyaccess to, into a Postgres instance I fully control (I will call it "local"), using the foreign data wrapper postgres_fdw. Define 'read-only', especially as it applies to the privileges on the public schema. Per Tom Lane's comments on timezone, log into the remote server and do: SHOW timezone; SET timezone = 'etc/UTC'; SET timezone = 'UTC'; > > Does anyone have an idea for me? > > Kind regards, > > > Adnan Dautovic > > > -- Adrian Klaver adrian.klaver@aklaver.com
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
Dear Adrian,
Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>Define 'read-only', especially as it applies to the privileges on the
>public schema.
I am not quite sure which information you are looking for
exactly. According to this [1], I ran the following query:
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS
"create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
FROM "names";
And recieved the following result:
"name" "create" "usage"
"public" true true
>Per Tom Lane's comments on timezone, log into the remote server and do:
>
>SHOW timezone;
Europe/Berlin
>SET timezone = 'etc/UTC';
ERROR: invalid value for parameter "TimeZone": "etc/UTC"
SQL state: 22023
>SET timezone = 'UTC';
ERROR: invalid value for parameter "TimeZone": "UTC"
SQL state: 22023
However, this lead me to [2] and I find the output very
interesting:
SELECT * FROM pg_timezone_names ORDER BY name;
>"name" "abbrev" "utc_offset" "is_dst"
>"Turkey" "+03" "03:00:00" false
>"UCT" "UCT" "00:00:00" false
>"Universal" "UTC" "00:00:00" false
>"W-SU" "MSK" "03:00:00" false
And then attempting
SET timezone = 'Universal';
>SET
>Query returned successfully in 100 msec.
Any ideas on how to proceed?
Kind regards,
Adnan Dautovic
[1]: https://stackoverflow.com/a/36095257
[2]: https://stackoverflow.com/a/32009497
Adnan Dautovic <daut@mailbox.org> writes:
> However, this lead me to [2] and I find the output very
> interesting:
> SELECT * FROM pg_timezone_names ORDER BY name;
>> "name" "abbrev" "utc_offset" "is_dst"
>> "Turkey" "+03" "03:00:00" false
>> "UCT" "UCT" "00:00:00" false
>> "Universal" "UTC" "00:00:00" false
>> "W-SU" "MSK" "03:00:00" false
Wow. To clarify, is that the *whole* result?
But even if you just excerpted it, you're clearly
missing a lot of entries.
Per your other answer, the remote DB doesn't seem to have
been built with --with-system-tzdata, so it must be relying
on a Postgres-private copy of the tzdb data set, and evidently
a fair number of entries in that have gone missing. Postgres
itself would never modify that data after installation, so
we're left to speculate about filesystem corruption or
somebody's odd desire to remove "unnecessary" files.
Out of curiosity, does
SET timezone to 'GMT';
work?
regards, tom lane
On 4/5/24 02:39, Adnan Dautovic wrote:
> Dear Adrian,
>
> Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> Define 'read-only', especially as it applies to the privileges on the
>> public schema.
>
> I am not quite sure which information you are looking for
> exactly. According to this [1], I ran the following query:
>
> WITH "names"("name") AS (
> SELECT n.nspname AS "name"
> FROM pg_catalog.pg_namespace n
> WHERE n.nspname !~ '^pg_'
> AND n.nspname <> 'information_schema'
> ) SELECT "name",
> pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS
> "create",
> pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS
> "usage"
> FROM "names";
>
> And recieved the following result:
>
> "name" "create" "usage"
> "public" true true
Looks alright. The below is the issue.
>
>> Per Tom Lane's comments on timezone, log into the remote server and do:
>>
>> SHOW timezone;
> Europe/Berlin
>
>> SET timezone = 'etc/UTC';
> ERROR: invalid value for parameter "TimeZone": "etc/UTC"
> SQL state: 22023
>
>> SET timezone = 'UTC';
> ERROR: invalid value for parameter "TimeZone": "UTC"
> SQL state: 22023
>
> However, this lead me to [2] and I find the output very
> interesting:
>
> SELECT * FROM pg_timezone_names ORDER BY name;
The below is cut down from the actual output as there should be at least:
Europe/Berlin CEST 02:00:00 t
present also?
>
>> "name" "abbrev" "utc_offset" "is_dst"
>> "Turkey" "+03" "03:00:00" false
>> "UCT" "UCT" "00:00:00" false
Hmm I get:
UCT UTC 00:00:00 f
could be version difference though.
>> "Universal" "UTC" "00:00:00" false
>> "W-SU" "MSK" "03:00:00" false
>
>
> And then attempting
>
> SET timezone = 'Universal';
>
>> SET
>> Query returned successfully in 100 msec.
>
> Any ideas on how to proceed?
1) For the long term contact whomever is in charge of the remote server
and ask them what they have done with the timezones, why and can they
fix it?
2) In short term per the link from your first post and with no guarantees:
https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677
In the source code change
do_sql_command(conn, "SET timezone = 'UTC'");
to
do_sql_command(conn, "SET timezone = 'Universal'");
As from the link: "Set remote timezone; this is basically just cosmetic"
Then recompile the extension.
>
> Kind regards,
>
> Adnan Dautovic
>
>
> [1]: https://stackoverflow.com/a/36095257
> [2]: https://stackoverflow.com/a/32009497
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi, On 05. Apr 2024, at 16:13, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Adnan Dautovic <daut@mailbox.org> writes: >> SELECT * FROM pg_timezone_names ORDER BY name; > >>> "name" "abbrev" "utc_offset" "is_dst" >>> "Turkey" "+03" "03:00:00" false >>> "UCT" "UCT" "00:00:00" false >>> "Universal" "UTC" "00:00:00" false >>> "W-SU" "MSK" "03:00:00" false > >Wow. To clarify, is that the *whole* result? I apologize for the confusion, this is an excerpt where I cut out everything before "Turkey" and after "W-SU". Between those, the output is complete. >Out of curiosity, does > SET timezone to 'GMT'; >work? Yes, it yields: >SET > >Query returned successfully in 84 msec. The corresponding excerpt from pg_timezone_names is: >"name" "abbrev" "utc_offset" "is_dst" > [snip] >"Europe/Zurich" "CEST" "02:00:00" true >"GB-Eire" "BST" "01:00:00" true >"Greenwich" "GMT" "00:00:00" false >"HST" "HST" "-10:00:00" false >"Hongkong" "HKT" "08:00:00" false >"Iceland" "GMT" "00:00:00" false > [snip] By the way, the row count of pg_timezone_names is 385, but I do not know how that compares to a more standard installation. Kind regards, Adnan Dautovic
Dear Adrian, On 05. Apr 2024, at 17:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >The below is cut down from the actual output as there should be at least: > >Europe/Berlin CEST 02:00:00 t > >present also? Correct! That entry also exists. I only included the snippet where I would have expected the "UTC" entry to be. >1) For the long term contact whomever is in charge of the remote >server and ask them what they have done with the timezones, why and >can they fix it? This will probably be long term indeed. But I am curious and want to see if I can get some information from the responsible person(s). >2) In short term per the link from your first post and with no guarantees: > >https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677 > >In the source code change > >do_sql_command(conn, "SET timezone = 'UTC'"); > >to > >do_sql_command(conn, "SET timezone = 'Universal'"); > >As from the link: "Set remote timezone; this is basically just cosmetic" > >Then recompile the extension. Thank you, I got around to trying this route and it worked! Now I just have to tinker around a bit to see how I can best include the modified extension into the Docker image, but that is a task I can grapple with outside of this mailing list. :-) I am happy to have learned a few things and thank you for your help tom and Adrian. Kind regards, Adnan Dautovic
On 4/10/24 12:38, Adnan Dautovic wrote: > Hi, > > On 05. Apr 2024, at 16:13, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Adnan Dautovic <daut@mailbox.org> writes: > By the way, the row count of pg_timezone_names is 385, but I do > not know how that compares to a more standard installation. On my instance of Postgres 16.2, 1196. > > Kind regards, > > Adnan Dautovic > > -- Adrian Klaver adrian.klaver@aklaver.com
Adnan Dautovic <daut@mailbox.org> writes:
> On 05. Apr 2024, at 16:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Out of curiosity, does
>> SET timezone to 'GMT';
>> work?
> Yes, it yields:
>> SET
>>
>> Query returned successfully in 84 msec.
I expected that, because the name "GMT" is hard-wired in our code.
Doesn't help for postgres_fdw though, because it has "UTC" hardwired.
(I have a todo item to rationalize that...)
> By the way, the row count of pg_timezone_names is 385, but I do
> not know how that compares to a more standard installation.
Using current PG HEAD (with tzdata release 2024a):
=# select count(*) from pg_timezone_names;
count
-------
597
(1 row)
I can believe older tzdata releases varying from that a little,
but they haven't exactly been adding zone names at a rapid clip.
Either the one you're dealing with is VERY old or it lost some
files sometime.
regards, tom lane
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/10/24 12:38, Adnan Dautovic wrote:
>> By the way, the row count of pg_timezone_names is 385, but I do
>> not know how that compares to a more standard installation.
> On my instance of Postgres 16.2, 1196.
You're probably using a build with --with-system-tzdata pointing
at a system tzdata tree that includes leap-second-aware zones.
These tend to have duplicative entries like "America/New_York"
and "posix/America/New_York". (There's also a subtree like
"right/America/New_York", but we reject those because we don't
do leap seconds.) The real number of distinct zones in a
standard tzdata file set these days is a shade under 600.
regards, tom lane
On 4/10/24 1:31 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 4/10/24 12:38, Adnan Dautovic wrote: >>> By the way, the row count of pg_timezone_names is 385, but I do >>> not know how that compares to a more standard installation. > >> On my instance of Postgres 16.2, 1196. > > You're probably using a build with --with-system-tzdata pointing > at a system tzdata tree that includes leap-second-aware zones. > These tend to have duplicative entries like "America/New_York" > and "posix/America/New_York". (There's also a subtree like > "right/America/New_York", but we reject those because we don't > do leap seconds.) The real number of distinct zones in a > standard tzdata file set these days is a shade under 600. It's the PGDG package running on Ubuntu 22.04. > > regards, tom lane -- Adrian Klaver adrian.klaver@aklaver.com