| From: | Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru> | 
|---|---|
| To: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> | 
| Subject: | Push down time-related SQLValue functions to foreign server | 
| Date: | 2021-08-19 09:51:55 | 
| Message-ID: | 39f983a265780b378b835af602276d0a@postgrespro.ru | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-hackers | 
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
| Attachment | Content-Type | Size | 
|---|---|---|
| 0001-SQLValue-functions-pushdown.patch | text/x-diff | 15.4 KB | 
| 0002-now-pushdown.patch | text/x-diff | 6.6 KB | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Hannu Krosing | 2021-08-19 09:57:58 | Re: Middleware Messages for FE/BE | 
| Previous Message | Hannu Krosing | 2021-08-19 09:36:47 | Re: Middleware Messages for FE/BE |