Subject: Re: Failure of postgres_fdw because of TimeZone setting

From: Adrian Klaver <[email-masked]>
Date: Thu, 4 Apr 2024 08:15:42 -0700

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-masked]

---

From: Adnan Dautovic <[email-masked]>
Date: Fri, 5 Apr 2024 11:39:04 +0200

Dear Adrian,

>Define 'read-only', especially as it applies to the privileges on the 

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:
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

---

From: Tom Lane <[email-masked]>
Date: Fri, 05 Apr 2024 10:13:38 -0400

>> "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

---

From: Adnan Dautovic <[email-masked]>
Date: Wed, 10 Apr 2024 21:38:52 +0200

Hi,

>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.

Yes, it yields:
>SET
>
>Query returned successfully in 84 msec.

The corresponding excerpt from pg_timezone_names is:
> [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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 10 Apr 2024 13:04:38 -0700

> not know how that compares to a more standard installation.

On my instance of Postgres 16.2, 1196.

---

From: Tom Lane <[email-masked]>
Date: Wed, 10 Apr 2024 16:21:47 -0400

>> 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...)

> 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