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

Em qui., 19 de ago. de 2021 às 07:50, Zhihong Yu <zyu(at)yugabyte(dot)com>
escreveu:

>
>
> 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 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.
>
Perhaps in a MACRO?

regards,
Ranier Vilela