Re: Failure of postgres_fdw because of TimeZone setting

On 4/5/24 02:39, Adnan Dautovic wrote:
> Dear Adrian,
>
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com