Re: Push down time-related SQLValue functions to foreign server

On Thu, Aug 19, 2021 at 2:52 AM Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)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 0002 patch:

+ /* now() is stable, but we can ship it as it's replaced by parameter */
+ return !(func_volatile(func_id) == PROVOLATILE_IMMUTABLE || func_id ==
F_NOW);

Did you mean to say 'now() is unstable' ?