Postgres timezone issue when using postgres_fdw

Question

I have a view stored on a foreign Postgres server (v 9.3) which pulls all of the data that was posted for the day.

I have foreign tables (using postgres_fdw) stored on my local Postgres servers (version 10.5 and 14.1) that point to the foreign view.

I setup pg_agent to pull this data in each hour and upsert it into a table.

This is where it gets weird. I noticed that the foreign table would return 0 records after 6pm, and then after the recent time change, at 5pm. I did not care that much when the data would not pull after business hours, but since the time change it has become an issue.

Both the foreign server and the two local servers are all on Mountain Time, which was -6 UTC before the time change and -7 UTC after the time change. This somewhat explains why the issue was initially starting at 6pm, and now at 5pm, because UTC time is now the next day.

Unfortunately, the foreign server saves dates in timestamp without timezone format. I know, it is definitely one of the "Don't Do This" scenarios, but it is a legacy database from a 3rd party vendor, so I have no control over that.

After the UTC date change, the view query run directly on the foreign server still returns the appropriate records for the day, but the foreign table on my local servers return 0 records. Is this a bug in the postgres_fdw? Does anyone have a workaround?

• Erwin Brandstetter: Postgres server (v 9.03) Seriously? Either way, please show the exact table definition, the formulation in the VIEW and in your query using it.
• dcbeckman: 9.03 was a typo. The 3rd party vendor uses Postgres 9.3 and does not seem willing or capable of upgrading their server. This sucks majorly because not only is it not supported, but it is so obsolete that anything beyond a simple query gets locked up because the query planning is not pushed to their server. This means a pretty basic join query with several conditions can take hours to return. The only upside to this is that it has made me very creative with getting our data off of this thing.

Answer 1

After some tinkering I was able to solve my own problem. I had assumed that a view being used as a foreign table with the Postgres FDW would return the same results as if I ran the query on the remote server. I tried various WHERE comparisons with timestamps and they all returned normally on the foreign server. I finally discovered that I needed to convert the timestamp to my local timezone, even though both the local server and remote server were both in the same timezone. The beauty of using timestamp without timezone, I guess.

To illustrate this issue and how I fixed it, I used this test query to return various timestamp formats:

SELECT now()::timestamp AS nowts, 
LOCALTIMESTAMP AS localts, 
timezone('US/Mountain'::text, now()) AS timezonef;

If I run this query directly on either the foreign server or the local server, I get back results similar to the following:

"2022年10月25日 19:36:48.627804";
"2022年10月25日 19:36:48.627804";
"2022年10月25日 19:36:48.627804"

But, if I create a view containing the above query on the remote server and then create a foreign table of it on my local server, it returns the following:

"2022年10月26日 01:40:02.702616";
"2022年10月26日 01:40:02.702616";
"2022年10月25日 19:40:02.702616"

So as far as the Postgres FDW is concerned, all timestamps are returned in UTC unless converted explicitly to the target timezone. It is also worth pointing out that this is also the case with timestamps that are not directly returned, but used as a WHERE condition in the foreign query itself.

Please note that I am not sure if this scenario exists outside of using a foreign server running an obsolete version of Postgres (9.3). At the moment, I do not have another server to test this scenario using a more modern foreign server.