1

I have a PostgreSQL 9.6 server connecting to remote 9.3 server.

I'm trying to create the following materialized view :

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

It's slow because the filtering is done on local server.

Here is the EXPLAIN ANALYSE result :

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

How to make condition filtering on the remote server ?

Note : remote filtering is working with such a query :

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

But with this, each time I refreh the view, the date has already been calculated and stay for example above at 2017-05-31 11:44:10.89017

Any ideas ?

Thanks and regards

Luma
  • 11
  • 2

1 Answers1

1

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

Function now() is not immutable - it does not give the same result if parameters don't change.

If you really need to send query with now() executed on remote server, then you can use dblink for that.

Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32