public inbox for [email protected]help / color / mirror / Atom feed
Re: Failure of postgres_fdw because of TimeZone setting 6+ messages / 3 participants [nested] [flat]
* Re: Failure of postgres_fdw because of TimeZone setting @ 2024-04-04 15:15 Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrian Klaver @ 2024-04-04 15:15 UTC (permalink / raw) To: Adnan Dautovic <[email protected]>; pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> @ 2024-04-05 09:39 ` Adnan Dautovic <[email protected]> 2024-04-05 14:13 ` Re: Failure of postgres_fdw because of TimeZone setting Tom Lane <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adnan Dautovic @ 2024-04-05 09:39 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general Dear Adrian, Adrian Klaver <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> @ 2024-04-05 14:13 ` Tom Lane <[email protected]> 2024-04-10 19:38 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2024-04-05 14:13 UTC (permalink / raw) To: Adnan Dautovic <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general Adnan Dautovic <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> 2024-04-05 14:13 ` Re: Failure of postgres_fdw because of TimeZone setting Tom Lane <[email protected]> @ 2024-04-10 19:38 ` Adnan Dautovic <[email protected]> 2024-04-10 20:04 ` Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-10 20:21 ` Re: Failure of postgres_fdw because of TimeZone setting Tom Lane <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Adnan Dautovic @ 2024-04-10 19:38 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general Hi, On 05. Apr 2024, at 16:13, Tom Lane <[email protected]> wrote: >Adnan Dautovic <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> 2024-04-05 14:13 ` Re: Failure of postgres_fdw because of TimeZone setting Tom Lane <[email protected]> 2024-04-10 19:38 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> @ 2024-04-10 20:04 ` Adrian Klaver <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Adrian Klaver @ 2024-04-10 20:04 UTC (permalink / raw) To: Adnan Dautovic <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-general On 4/10/24 12:38, Adnan Dautovic wrote: > Hi, > > On 05. Apr 2024, at 16:13, Tom Lane <[email protected]> wrote: >> Adnan Dautovic <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> 2024-04-05 14:13 ` Re: Failure of postgres_fdw because of TimeZone setting Tom Lane <[email protected]> 2024-04-10 19:38 ` Re: Failure of postgres_fdw because of TimeZone setting Adnan Dautovic <[email protected]> @ 2024-04-10 20:21 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-04-10 20:21 UTC (permalink / raw) To: Adnan Dautovic <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general Adnan Dautovic <[email protected]> writes: > On 05. Apr 2024, at 16:13, Tom Lane <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-10 20:21 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-04 15:15 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]> 2024-04-05 09:39 ` Adnan Dautovic <[email protected]> 2024-04-05 14:13 ` Tom Lane <[email protected]> 2024-04-10 19:38 ` Adnan Dautovic <[email protected]> 2024-04-10 20:04 ` Adrian Klaver <[email protected]> 2024-04-10 20:21 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox