Re: Push down time-related SQLValue functions to foreign server - Mailing list pgsql-hackers

On Thu, Aug 19, 2021 at 2:52 AM Alexander Pyhalov <a.pyhalov@postgrespro.ru> wrote:

Hi.

The attached patches allow pushing down
current_timestamp/localtimestamp/current_time/localtime and now() to
remote PostgreSQL server as locally computed parameters.
The idea is based on oracle_fdw behavior.

Examples.

\d test
                               Foreign table "public.test"
  Column |           Type           | Collation | Nullable | Default |   
FDW options
--------+--------------------------+-----------+----------+---------+-------------------
  i      | integer                  |           |          |         |
(column_name 'i')
  t      | timestamp with time zone |           |          |         |
(column_name 't')
Server: loopback
FDW options: (schema_name 'data', table_name 'test')

Prior the patch:

explain verbose select * from test where t=current_timestamp;
                              QUERY PLAN
---------------------------------------------------------------------
  Foreign Scan on public.test  (cost=100.00..188.12 rows=11 width=12)
    Output: i, t
    Filter: (test.t = CURRENT_TIMESTAMP)
    Remote SQL: SELECT i, t FROM data.test

explain verbose update test set t=current_timestamp where t<now();
                                  QUERY PLAN
----------------------------------------------------------------------------
  Update on public.test  (cost=100.00..154.47 rows=0 width=0)
    Remote SQL: UPDATE data.test SET t = $2 WHERE ctid = $1
    ->  Foreign Scan on public.test  (cost=100.00..154.47 rows=414
width=50)
          Output: CURRENT_TIMESTAMP, ctid, test.*
          Filter: (test.t < now())
          Remote SQL: SELECT i, t, ctid FROM data.test FOR UPDATE


After patch:
explain verbose select * from test where t=current_timestamp;
                                      QUERY PLAN
-------------------------------------------------------------------------------------
  Foreign Scan on public.test  (cost=100.00..144.35 rows=11 width=12)
    Output: i, t
    Remote SQL: SELECT i, t FROM data.test WHERE ((t = $1::timestamp with
time zone))

explain verbose update test set t=current_timestamp where t<now();
                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
  Update on public.test  (cost=100.00..137.93 rows=0 width=0)
    ->  Foreign Update on public.test  (cost=100.00..137.93 rows=414
width=50)
          Remote SQL: UPDATE data.test SET t = $1::timestamp with time
zone WHERE ((t < $1::timestamp with time zone))

--
Best regards,
Alexander Pyhalov,
Postgres Professional

Hi,

For 0001 patch:

+               if ((s->op != SVFOP_CURRENT_TIMESTAMP) &&
+                   (s->op != SVFOP_CURRENT_TIMESTAMP_N) &&

+                   (s->op != SVFOP_CURRENT_TIME) &&

...

The above check appears more than once. If extracted into a helper method, it would help reduce duplicate and make the code more readable.