1

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?

2
  • 3
    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. Commented Jan 15, 2022 at 21:14
  • 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.
    – dcbeckman
    Commented Jun 23, 2022 at 23:01

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.