From 0820c6b002f5a1df0158c4b507d44462f4993c34 Mon Sep 17 00:00:00 2001 From: Alexander Pyhalov Date: Mon, 16 Aug 2021 18:09:09 +0300 Subject: [PATCH 2/2] now() pushdown Treat now() the same way as current_timestamp and send it to remote server as parameter value. --- contrib/postgres_fdw/deparse.c | 34 +++++++++++++++---- .../postgres_fdw/expected/postgres_fdw.out | 15 ++++++++ contrib/postgres_fdw/shippable.c | 12 ++++--- contrib/postgres_fdw/sql/postgres_fdw.sql | 5 +++ 4 files changed, 55 insertions(+), 11 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 01748835fc8..9f0be6cb884 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -52,6 +52,7 @@ #include "parser/parsetree.h" #include "postgres_fdw.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -269,10 +270,9 @@ is_foreign_expr(PlannerInfo *root, /* * An expression which includes any mutable functions can't be sent over - * because its result is not stable. For example, sending now() remote - * side could cause confusion from clock offsets. Future versions might - * be able to make this choice with more granularity. (We check this last - * because it requires a lot of expensive catalog lookups.) + * because its result is not stable. Future versions might be able to + * make this choice with more granularity. (We check this last because it + * requires a lot of expensive catalog lookups.) */ if (contain_unsafe_functions((Node *) expr)) return false; @@ -625,8 +625,8 @@ foreign_expr_walker(Node *node, /* * For now only time-related SQLValue functions are supported. - * We can push down localtimestamp and localtime as we - * compute them locally. + * We can push down localtimestamp and localtime as we compute + * them locally. */ if ((s->op != SVFOP_CURRENT_TIMESTAMP) && (s->op != SVFOP_CURRENT_TIMESTAMP_N) && @@ -1056,6 +1056,14 @@ is_foreign_param(PlannerInfo *root, case T_Param: /* Params always have to be sent to the foreign server */ return true; + case T_FuncExpr: + { + FuncExpr *fe = (FuncExpr *) expr; + + if (fe->funcid == F_NOW) + return true; + break; + } case T_SQLValueFunction: { SQLValueFunction *s = (SQLValueFunction *) expr; @@ -2963,6 +2971,20 @@ deparseFuncExpr(FuncExpr *node, deparse_expr_cxt *context) return; } + if (node->funcid == F_NOW) + { + SQLValueFunction *svf = makeNode(SQLValueFunction); + + svf->op = SVFOP_CURRENT_TIMESTAMP; + svf->type = TIMESTAMPTZOID; + svf->typmod = -1; + svf->location = -1; + + deparseSQLValueFunction(svf, context); + + return; + } + /* Check if need to print VARIADIC (cf. ruleutils.c) */ use_variadic = node->funcvariadic; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 105324e2a00..f12455a7c74 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1082,6 +1082,21 @@ SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp; ----+----+---- (0 rows) +-- the same with now() +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1,c2,c4 FROM ft2 WHERE c4 = now(); + QUERY PLAN +------------------------------------------------------------------------------------------------- + Foreign Scan on public.ft2 + Output: c1, c2, c4 + Remote SQL: SELECT "C 1", c2, c4 FROM "S 1"."T 1" WHERE ((c4 = $1::timestamp with time zone)) +(3 rows) + +SELECT c1,c2,c4 FROM ft2 WHERE c4 = now(); + c1 | c2 | c4 +----+----+---- +(0 rows) + EXPLAIN (VERBOSE, COSTS OFF) SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1; QUERY PLAN diff --git a/contrib/postgres_fdw/shippable.c b/contrib/postgres_fdw/shippable.c index 56d47d0b2f3..d8163d67d46 100644 --- a/contrib/postgres_fdw/shippable.c +++ b/contrib/postgres_fdw/shippable.c @@ -28,6 +28,7 @@ #include "catalog/pg_proc.h" #include "nodes/nodeFuncs.h" #include "postgres_fdw.h" +#include "utils/fmgroids.h" #include "utils/hsearch.h" #include "utils/inval.h" #include "utils/lsyscache.h" @@ -214,9 +215,10 @@ is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo) } static bool -contain_mutable_functions_checker(Oid func_id, void *context) +contain_unsafe_functions_checker(Oid func_id, void *context) { - return (func_volatile(func_id) != PROVOLATILE_IMMUTABLE); + /* 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); } static bool @@ -224,8 +226,8 @@ contain_unsafe_functions_walker(Node *node, void *context) { if (node == NULL) return false; - /* Check for mutable functions in node itself */ - if (check_functions_in_node(node, contain_mutable_functions_checker, + /* Check for unsafe functions in node itself */ + if (check_functions_in_node(node, contain_unsafe_functions_checker, context)) return true; @@ -266,7 +268,7 @@ contain_unsafe_functions_walker(Node *node, void *context) * contain_unsafe_functions * Recursively search for unsafe mutable functions within a clause. * - * Returns true if any mutable function (or operator implemented by a + * Returns true if any unsafe mutable function (or operator implemented by a * mutable function), which is not known to be safe, is found. * * We will recursively look into Query nodes (i.e., SubLink sub-selects) diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 167f966553d..55046f25c50 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -413,6 +413,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp; SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp; +-- the same with now() +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c1,c2,c4 FROM ft2 WHERE c4 = now(); +SELECT c1,c2,c4 FROM ft2 WHERE c4 = now(); + EXPLAIN (VERBOSE, COSTS OFF) SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1; SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1; -- 2.25.1