PostgreSQL fdw 和 View ，使用时间戳

Question

我有一个连接到远程 9.3 服务器的 PostgreSQL 9.6 服务器。

我正在尝试创建以下物化 View :

CREATE MATERIALIZED VIEW test AS
  SELECT id
  FROM remote.logs
  WHERE remote.logs.created_at > (now() - interval '1 day')

它很慢，因为过滤是在本地服务器上完成的。

这是 EXPLAIN ANALYZE 结果:

Foreign Scan on integration.logs  (cost=100.00..219.69 rows=975 width=4)
  Output: id
  Filter: (logs.created_at > (now() - '1 day'::interval))
  Remote SQL: SELECT id, created_at FROM public.logs

如何在远程服务器上进行条件过滤？

注意:远程过滤适用于这样的查询:

CREATE MATERIALIZED VIEW test AS
  SELECT id
  FROM integration.logs
  WHERE integration.logs.created_at > (timestamp 'now()' - interval'1 day')

Foreign Scan on integration.logs  (cost=100.00..166.06 rows=975 width=4)
  Output: id
  Remote SQL: SELECT id FROM public.logs WHERE ((created_at > '2017-05-31 11:44:10.89017'::timestamp without time zone))

但是有了这个，每次我刷新 View 时，日期都已经计算出来并保持在上面的例子中 2017-05-31 11:44:10.89017

有什么想法吗？

感谢和问候

Answer 1

来自 https://www.postgresql.org/docs/current/static/postgres-fdw.html

To reduce the risk of misexecution of queries, WHERE clauses are not sent to the remote server unless they use only data types, operators, and functions that are built-in or belong to an extension that's listed in the foreign server's extensions option. Operators and functions in such clauses must be IMMUTABLE as well.

函数 now() 不是不可变的 - 如果参数不改变，它不会给出相同的结果。

如果你真的需要在远程服务器上执行 now() 发送查询，那么你可以使用 dblink为此。