[MASSMAIL]Failure of postgres_fdw because of TimeZone setting - Mailing list 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
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