On Thu, Aug 19, 2021 at 2:52 AM Alexander Pyhalov <a.pyha...@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 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' ?
