public inbox for [email protected]
help / color / mirror / Atom feedRe: Failure of postgres_fdw because of TimeZone setting
2+ messages / 2 participants
[nested] [flat]
* Re: Failure of postgres_fdw because of TimeZone setting
@ 2024-04-05 15:05 Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Klaver @ 2024-04-05 15:05 UTC (permalink / raw)
To: Adnan Dautovic <[email protected]>; +Cc: pgsql-general
On 4/5/24 02:39, Adnan Dautovic wrote:
> 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
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_...
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
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Failure of postgres_fdw because of TimeZone setting
@ 2024-04-10 19:48 Adnan Dautovic <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adnan Dautovic @ 2024-04-10 19:48 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
Dear Adrian,
On 05. Apr 2024, at 17:05, Adrian Klaver <[email protected]> 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_...
>
>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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-10 19:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-05 15:05 Re: Failure of postgres_fdw because of TimeZone setting Adrian Klaver <[email protected]>
2024-04-10 19:48 ` Adnan Dautovic <[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