Failure of postgres_fdw because of TimeZone setting
ession" "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 Debian 5.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 version of 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
Re: Failure of postgres_fdw because of TimeZone setting
Hi Tom, thank you for your reply! Tom Lane 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
Re: Failure of postgres_fdw because of TimeZone setting
Dear Adrian,
Adrian Klaver 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" truetrue
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
Re: Failure of postgres_fdw because of TimeZone setting
Hi, On 05. Apr 2024, at 16:13, Tom Lane wrote: Adnan Dautovic 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
Re: Failure of postgres_fdw because of TimeZone setting
Dear Adrian, On 05. Apr 2024, at 17:05, Adrian Klaver 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
