Re: Failure of postgres_fdw because of TimeZone setting
От | Adrian Klaver |
---|---|
Тема | Re: Failure of postgres_fdw because of TimeZone setting |
Дата | |
Msg-id | 5e01327f-340a-49a8-b383-8c363a5d752a@aklaver.com обсуждение исходный текст |
Ответ на | Re: Failure of postgres_fdw because of TimeZone setting (Adnan Dautovic <daut@mailbox.org>) |
Ответы |
Re: Failure of postgres_fdw because of TimeZone setting
(Adnan Dautovic <daut@mailbox.org>)
|
Список | pgsql-general |
On 4/5/24 02:39, Adnan Dautovic wrote: > Dear Adrian, > > Adrian Klaver <adrian.klaver@aklaver.com> 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.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Следующее
От: "David G. Johnston"Дата:
Сообщение: [MASSMAIL]Role Graph for PostgreSQL (v16+) v1-Beta