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

I spent some time looking at this patch.

Generally it looks like a good idea. These stable functions will be
evaluated at the execution time and replaced with constants. I am not
sure whether the nodes saved in the param_list may not get the same
treatment. Have you verified that?

Also the new node types being added to the param list is something
other than Param. So it conflicts with the comment below in
prepare_query_params()?
/*
* Prepare remote-parameter expressions for evaluation. (Note: in
* practice, we expect that all these expressions will be just Params, so
* we could possibly do something more efficient than using the full
* expression-eval machinery for this. But probably there would be little
* benefit, and it'd require postgres_fdw to know more than is desirable
* about Param evaluation.)
*/
If we are already adding non-params to this list, then the comment is outdated?

On Thu, Aug 19, 2021 at 3:22 PM 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

--
Best Wishes,
Ashutosh Bapat