Re: Failure of postgres_fdw because of TimeZone setting
On 4/10/24 1:31 PM, Tom Lane wrote: Adrian Klaver 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.kla...@aklaver.com
Re: Failure of postgres_fdw because of TimeZone setting
Adrian Klaver 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
Re: Failure of postgres_fdw because of TimeZone setting
Adnan Dautovic writes: > On 05. Apr 2024, at 16:13, Tom Lane 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
Re: Failure of postgres_fdw because of TimeZone setting
On 4/10/24 12:38, Adnan Dautovic wrote: Hi, On 05. Apr 2024, at 16:13, Tom Lane wrote: Adnan Dautovic 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.kla...@aklaver.com
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
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
On 4/5/24 02:39, Adnan Dautovic wrote:
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" 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.kla...@aklaver.com
Re: Failure of postgres_fdw because of TimeZone setting
Adnan Dautovic 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
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 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
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 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. 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.kla...@aklaver.com
Re: Failure of postgres_fdw because of TimeZone setting
Adnan Dautovic writes: > 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. 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
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 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
