Push down time-related SQLValue functions to foreign server
От | Alexander Pyhalov |
---|---|
Тема | Push down time-related SQLValue functions to foreign server |
Дата | |
Msg-id | 39f983a265780b378b835af602276d0a@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Push down time-related SQLValue functions to foreign server
Re: Push down time-related SQLValue functions to foreign server Re: Push down time-related SQLValue functions to foreign server |
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: