Failure of postgres_fdw because of TimeZone setting

From: Adnan Dautovic <daut(at)mailbox(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Failure of postgres_fdw because of TimeZone setting
Date: 2024-04-04 05:23:34
Message-ID: 5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4@mailbox.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 Postgres database 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-only access 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, without success, different versions for the local instance, namely 9.4.14 so I would be as close as possible to the remote version 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 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message yudhi s 2024-04-04 05:24:52 Re: Moving delta data faster
Previous Message Adrian Klaver 2024-04-04 04:46:56 Re: Moving delta data faster