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
From 0846ba1d3a5f15bbea449b39741f08558fdb2d49 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Thu, 29 Jul 2021 11:45:28 +0300
Subject: [PATCH 1/2] SQLValue functions pushdown
current_timestamp, localtimestamp and similar SQLValue functions
can be computed locally and sent to remote side as parameters values.
---
contrib/postgres_fdw/deparse.c | 100 +++++++++++++++-
.../postgres_fdw/expected/postgres_fdw.out | 108 ++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.h | 1 +
contrib/postgres_fdw/shippable.c | 68 +++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 29 +++++
5 files changed, 305 insertions(+), 1 deletion(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd666818..01748835fc8 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -157,6 +157,7 @@ static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
static void deparseScalarArrayOpExpr(ScalarArrayOpExpr *node,
deparse_expr_cxt *context);
static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
+static void deparseSQLValueFunction(SQLValueFunction *node, deparse_expr_cxt *context);
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
static void deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
@@ -273,7 +274,7 @@ is_foreign_expr(PlannerInfo *root,
* be able to make this choice with more granularity. (We check this last
* because it requires a lot of expensive catalog lookups.)
*/
- if (contain_mutable_functions((Node *) expr))
+ if (contain_unsafe_functions((Node *) expr))
return false;
/* OK to evaluate on the remote server */
@@ -618,6 +619,30 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_SQLValueFunction:
+ {
+ SQLValueFunction *s = (SQLValueFunction *) node;
+
+ /*
+ * For now only time-related SQLValue functions are supported.
+ * We can push down localtimestamp and localtime as we
+ * compute them locally.
+ */
+ if ((s->op != SVFOP_CURRENT_TIMESTAMP) &&
+ (s->op != SVFOP_CURRENT_TIMESTAMP_N) &&
+ (s->op != SVFOP_CURRENT_TIME) &&
+ (s->op != SVFOP_CURRENT_TIME_N) &&
+ (s->op != SVFOP_LOCALTIMESTAMP) &&
+ (s->op != SVFOP_LOCALTIMESTAMP_N) &&
+ (s->op != SVFOP_LOCALTIME) &&
+ (s->op != SVFOP_LOCALTIME_N))
+ return false;
+
+ /* Timestamp or time are not collatable */
+ collation = InvalidOid;
+ state = FDW_COLLATE_NONE;
+ }
+ break;
case T_BoolExpr:
{
BoolExpr *b = (BoolExpr *) node;
@@ -1031,6 +1056,21 @@ is_foreign_param(PlannerInfo *root,
case T_Param:
/* Params always have to be sent to the foreign server */
return true;
+ case T_SQLValueFunction:
+ {
+ SQLValueFunction *s = (SQLValueFunction *) expr;
+
+ if ((s->op != SVFOP_CURRENT_TIMESTAMP) &&
+ (s->op != SVFOP_CURRENT_TIMESTAMP_N) &&
+ (s->op != SVFOP_CURRENT_TIME) &&
+ (s->op != SVFOP_CURRENT_TIME_N) &&
+ (s->op != SVFOP_LOCALTIMESTAMP) &&
+ (s->op != SVFOP_LOCALTIMESTAMP_N) &&
+ (s->op != SVFOP_LOCALTIME) &&
+ (s->op != SVFOP_LOCALTIME_N))
+ return true;
+ break;
+ }
default:
break;
}
@@ -2603,6 +2643,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_RelabelType:
deparseRelabelType((RelabelType *) node, context);
break;
+ case T_SQLValueFunction:
+ deparseSQLValueFunction((SQLValueFunction *) node, context);
+ break;
case T_BoolExpr:
deparseBoolExpr((BoolExpr *) node, context);
break;
@@ -3092,6 +3135,61 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
node->resulttypmod));
}
+/*
+ * Deparse a SQLValueFunction node
+ */
+static void
+deparseSQLValueFunction(SQLValueFunction *node, deparse_expr_cxt *context)
+{
+ int32 typmod = -1;
+
+ switch (node->op)
+ {
+ case SVFOP_LOCALTIME:
+ case SVFOP_CURRENT_TIME:
+ case SVFOP_LOCALTIMESTAMP:
+ case SVFOP_CURRENT_TIMESTAMP:
+ break;
+ case SVFOP_LOCALTIME_N:
+ case SVFOP_CURRENT_TIME_N:
+ case SVFOP_CURRENT_TIMESTAMP_N:
+ case SVFOP_LOCALTIMESTAMP_N:
+ typmod = node->typmod;
+ break;
+ default:
+ elog(ERROR, "unsupported SQLValueFunction op for deparse: %d",
+ node->op);
+ }
+ Assert(node->type != InvalidOid);
+
+ /* Treat like a Param */
+ if (context->params_list)
+ {
+ int pindex = 0;
+ ListCell *lc;
+
+ /* find its index in params_list */
+ foreach(lc, *context->params_list)
+ {
+ pindex++;
+ if (equal(node, (Node *) lfirst(lc)))
+ break;
+ }
+ if (lc == NULL)
+ {
+ /* not in list, so add it */
+ pindex++;
+ *context->params_list = lappend(*context->params_list, node);
+ }
+
+ printRemoteParam(pindex, node->type, typmod, context);
+ }
+ else
+ {
+ printRemotePlaceholder(node->type, typmod, context);
+ }
+}
+
/*
* Deparse a BoolExpr node.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e3ee30f1aaf..105324e2a00 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1067,6 +1067,114 @@ SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
(1 row)
+-- Test SQLValue functions pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+ 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 = current_timestamp;
+ 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
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c5
+ Remote SQL: SELECT "C 1", c2, c5 FROM "S 1"."T 1" WHERE (("C 1" > 990)) AND ((c5 > ($1::timestamp without time zone - '1000 years'::interval))) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c5 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ c1 | c2 | c5
+------+----+--------------------------
+ 991 | 1 | Thu Apr 02 00:00:00 1970
+ 992 | 2 | Fri Apr 03 00:00:00 1970
+ 993 | 3 | Sat Apr 04 00:00:00 1970
+ 994 | 4 | Sun Apr 05 00:00:00 1970
+ 995 | 5 | Mon Apr 06 00:00:00 1970
+ 996 | 6 | Tue Apr 07 00:00:00 1970
+ 997 | 7 | Wed Apr 08 00:00:00 1970
+ 998 | 8 | Thu Apr 09 00:00:00 1970
+ 999 | 9 | Fri Apr 10 00:00:00 1970
+ 1000 | 0 | Thu Jan 01 00:00:00 1970
+(10 rows)
+
+-- not shippable due to timestamptz arithmetic
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c4
+ Filter: (ft2.c4 > (CURRENT_TIMESTAMP - '@ 1000 years'::interval))
+ Remote SQL: SELECT "C 1", c2, c4 FROM "S 1"."T 1" WHERE (("C 1" > 990)) ORDER BY "C 1" ASC NULLS LAST
+(4 rows)
+
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+ c1 | c2 | c4
+------+----+------------------------------
+ 991 | 1 | Thu Apr 02 00:00:00 1970 PST
+ 992 | 2 | Fri Apr 03 00:00:00 1970 PST
+ 993 | 3 | Sat Apr 04 00:00:00 1970 PST
+ 994 | 4 | Sun Apr 05 00:00:00 1970 PST
+ 995 | 5 | Mon Apr 06 00:00:00 1970 PST
+ 996 | 6 | Tue Apr 07 00:00:00 1970 PST
+ 997 | 7 | Wed Apr 08 00:00:00 1970 PST
+ 998 | 8 | Thu Apr 09 00:00:00 1970 PST
+ 999 | 9 | Fri Apr 10 00:00:00 1970 PST
+ 1000 | 0 | Thu Jan 01 00:00:00 1970 PST
+(10 rows)
+
+-- direct modify
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 SET c4 = current_timestamp WHERE c4 < current_timestamp;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Update on public.ft2
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c4 = $1::timestamp with time zone WHERE ((c4 < $1::timestamp with time zone))
+(3 rows)
+
+-- parametrized queries
+PREPARE st1(int) AS SELECT c3 FROM ft2 WHERE c1 = $1 AND c5 > localtimestamp - interval '1000 years';
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c3
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 1)) AND ((c5 > ($1::timestamp without time zone - '1000 years'::interval)))
+(3 rows)
+
+EXECUTE st1(1);
+ c3
+-------
+ 00001
+(1 row)
+
+DEALLOCATE st1;
+PREPARE st1(int) AS SELECT c3 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 = $1;
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c3
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = 1)) AND ((c5 > ($1::timestamp without time zone - '1000 years'::interval)))
+(3 rows)
+
+EXECUTE st1(1);
+ c3
+-------
+ 00001
+(1 row)
+
+DEALLOCATE st1;
-- Test CASE pushdown
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1;
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index ca83306af99..4ab199e4ef4 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -229,5 +229,6 @@ extern const char *get_jointype_name(JoinType jointype);
/* in shippable.c */
extern bool is_builtin(Oid objectId);
extern bool is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo);
+extern bool contain_unsafe_functions(Node *clause);
#endif /* POSTGRES_FDW_H */
diff --git a/contrib/postgres_fdw/shippable.c b/contrib/postgres_fdw/shippable.c
index b27f82e0155..56d47d0b2f3 100644
--- a/contrib/postgres_fdw/shippable.c
+++ b/contrib/postgres_fdw/shippable.c
@@ -25,9 +25,12 @@
#include "access/transam.h"
#include "catalog/dependency.h"
+#include "catalog/pg_proc.h"
+#include "nodes/nodeFuncs.h"
#include "postgres_fdw.h"
#include "utils/hsearch.h"
#include "utils/inval.h"
+#include "utils/lsyscache.h"
#include "utils/syscache.h"
/* Hash table for caching the results of shippability lookups */
@@ -209,3 +212,68 @@ is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo)
return entry->shippable;
}
+
+static bool
+contain_mutable_functions_checker(Oid func_id, void *context)
+{
+ return (func_volatile(func_id) != PROVOLATILE_IMMUTABLE);
+}
+
+static bool
+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,
+ context))
+ return true;
+
+ /*
+ * Unlike contain_mutable_functions_walker, don't treat SQLValueFunction
+ * as unsafe - foreign_expr_walker() classifies them
+ */
+
+ if (IsA(node, NextValueExpr))
+ {
+ /* NextValueExpr is volatile */
+ return true;
+ }
+
+ /*
+ * It should be safe to treat MinMaxExpr as immutable, because it will
+ * depend on a non-cross-type btree comparison function, and those should
+ * always be immutable. Treating XmlExpr as immutable is more dubious,
+ * and treating CoerceToDomain as immutable is outright dangerous. But we
+ * have done so historically, and changing this would probably cause more
+ * problems than it would fix. In practice, if you have a non-immutable
+ * domain constraint you are in for pain anyhow.
+ */
+
+ /* Recurse to check arguments */
+ if (IsA(node, Query))
+ {
+ /* Recurse into subselects */
+ return query_tree_walker((Query *) node,
+ contain_unsafe_functions_walker,
+ context, 0);
+ }
+ return expression_tree_walker(node, contain_unsafe_functions_walker,
+ context);
+}
+
+/*
+ * contain_unsafe_functions
+ * Recursively search for unsafe mutable functions within a clause.
+ *
+ * Returns true if any 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)
+ * but not into SubPlans. See comments for contain_volatile_functions().
+ */
+bool
+contain_unsafe_functions(Node *clause)
+{
+ return contain_unsafe_functions_walker(clause, NULL);
+}
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 30b5175da5b..167f966553d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -408,6 +408,35 @@ EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
+-- Test SQLValue functions pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+SELECT c1,c2,c4 FROM ft2 WHERE c4 = current_timestamp;
+
+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;
+
+-- not shippable due to timestamptz arithmetic
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+SELECT c1,c2,c4 FROM ft2 WHERE c4 > current_timestamp - interval '1000 years' AND c1 > 990 ORDER BY c1;
+
+-- direct modify
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE ft2 SET c4 = current_timestamp WHERE c4 < current_timestamp;
+
+-- parametrized queries
+PREPARE st1(int) AS SELECT c3 FROM ft2 WHERE c1 = $1 AND c5 > localtimestamp - interval '1000 years';
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1);
+EXECUTE st1(1);
+DEALLOCATE st1;
+
+PREPARE st1(int) AS SELECT c3 FROM ft2 WHERE c5 > localtimestamp - interval '1000 years' AND c1 = $1;
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1);
+EXECUTE st1(1);
+DEALLOCATE st1;
+
-- Test CASE pushdown
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1;
--
2.25.1
From 0820c6b002f5a1df0158c4b507d44462f4993c34 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
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
