Hi.
postgres_fdw currently doesn't handle RowCompareExpr, which doesn't
allow keyset pagination queries to be efficiently executed over sharded
table.
Attached patch adds handling of RowCompareExpr in deparse.c, so that we
could push down conditions like
WHERE (created, id) > ('2023-01-01 00:00:00'::timestamp, 12345) to the
foreign server.
I'm not sure about conditions when it's possible for RowCompareExpr to
have opnos with different names or namespaces, but comment in
ruleutils.c suggests that this is possible, so I've added check for this
in foreign_expr_walker().
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 655148c85768afbbfc034e6f5dc5a5a6d72139b8 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Mon, 7 Aug 2023 11:47:31 +0300
Subject: [PATCH] postgres_fdw: support RowCompareExpr pushdown
---
contrib/postgres_fdw/deparse.c | 139 ++++++++++++++++++
.../postgres_fdw/expected/postgres_fdw.out | 49 ++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 9 ++
3 files changed, 197 insertions(+)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 09d6dd60ddc..5eed3ac981c 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -166,6 +166,7 @@ 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);
static void deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context);
+static void deparseRowCompareExpr(RowCompareExpr *node, deparse_expr_cxt *context);
static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
@@ -287,6 +288,51 @@ is_foreign_expr(PlannerInfo *root,
return true;
}
+/*
+ * Determines if the names and namespaces of operations match
+ */
+static bool
+opnames_match(List *opnos)
+{
+ Oid oprns = InvalidOid;
+ char *oprname = NULL;
+ ListCell *lc;
+ bool match = true;
+
+ foreach(lc, opnos)
+ {
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ Oid opno = lfirst_oid(lc);
+
+ opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(opno));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u", opno);
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ /* First op */
+ if (oprname == NULL)
+ {
+ oprname = pstrdup(NameStr(operform->oprname));
+ oprns = operform->oprnamespace;
+ }
+ else
+ {
+ Assert(OidIsValid(oprns));
+ if (oprns != operform->oprnamespace || (strcmp(oprname, NameStr(operform->oprname)) != 0))
+ match = false;
+ }
+ ReleaseSysCache(opertup);
+
+ if (!match)
+ break;
+ }
+
+ if (oprname)
+ pfree(oprname);
+
+ return match;
+}
+
/*
* Check if expression is safe to execute remotely, and return true if so.
*
@@ -872,6 +918,44 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_RowCompareExpr:
+ {
+ RowCompareExpr *rce = (RowCompareExpr *) node;
+ ListCell *lc;
+
+ if (list_length(rce->opnos) == 0)
+ return false;
+
+ /*
+ * Only shippable operators can be sent to remote.
+ */
+ foreach(lc, rce->opnos)
+ {
+ if (!is_shippable(lfirst_oid(lc), OperatorRelationId, fpinfo))
+ return false;
+ }
+
+ /* If opnos names do not match, can't deparse such expression */
+ if (!opnames_match(rce->opnos))
+ return false;
+
+ /*
+ * Recurse to arguments
+ */
+ if (!foreign_expr_walker((Node *) rce->largs,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+
+ if (!foreign_expr_walker((Node *) rce->rargs,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+
+ /* Output is always boolean and so noncollatable. */
+ collation = InvalidOid;
+ state = FDW_COLLATE_NONE;
+
+ }
+ break;
case T_List:
{
List *l = (List *) node;
@@ -2785,6 +2869,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_ArrayExpr:
deparseArrayExpr((ArrayExpr *) node, context);
break;
+ case T_RowCompareExpr:
+ deparseRowCompareExpr((RowCompareExpr *) node, context);
+ break;
case T_Aggref:
deparseAggref((Aggref *) node, context);
break;
@@ -3508,6 +3595,58 @@ deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context)
deparse_type_name(node->array_typeid, -1));
}
+/*
+ * Deparse RowCompareExpr
+ */
+static void
+deparseRowCompareExpr(RowCompareExpr *node, deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ bool first;
+ ListCell *arg;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ Oid opno = linitial_oid(node->opnos);
+
+ /* Deparse the first argument */
+ appendStringInfoString(buf, "(ROW(");
+
+ first = true;
+ foreach(arg, node->largs)
+ {
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ deparseExpr((Expr *) lfirst(arg), context);
+ first = false;
+ }
+ appendStringInfoString(buf, ") ");
+
+ /*
+ * Append operator name. We've checked that the first operator name matches
+ * other names.
+ */
+
+ opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(opno));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u", opno);
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ deparseOperatorName(buf, operform);
+ ReleaseSysCache(opertup);
+
+ /* Deparse the second argument */
+ appendStringInfoString(buf, " ROW(");
+
+ first = true;
+ foreach(arg, node->rargs)
+ {
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ deparseExpr((Expr *) lfirst(arg), context);
+ first = false;
+ }
+ appendStringInfoString(buf, "))");
+}
+
/*
* Deparse an Aggref node.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f6d3b8ec08e..cdd8d7ab1e7 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1163,6 +1163,55 @@ SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' EN
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)
+-- row-wise comparison can be shipped
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) > (990, 100) ORDER BY c1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" WHERE ((ROW("C 1", c2) > ROW(990, 100))) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) > (990, 100) ORDER BY c1;
+ c1 | c2 | c3
+------+----+-------
+ 991 | 1 | 00991
+ 992 | 2 | 00992
+ 993 | 3 | 00993
+ 994 | 4 | 00994
+ 995 | 5 | 00995
+ 996 | 6 | 00996
+ 997 | 7 | 00997
+ 998 | 8 | 00998
+ 999 | 9 | 00999
+ 1000 | 0 | 01000
+(10 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) <= (10, 500) ORDER BY c1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft2
+ Output: c1, c2, c3
+ Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" WHERE ((ROW("C 1", c2) <= ROW(10, 500))) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) <= (10, 500) ORDER BY c1;
+ c1 | c2 | c3
+----+----+-------
+ 1 | 1 | 00001
+ 2 | 2 | 00002
+ 3 | 3 | 00003
+ 4 | 4 | 00004
+ 5 | 5 | 00005
+ 6 | 6 | 00006
+ 7 | 7 | 00007
+ 8 | 8 | 00008
+ 9 | 9 | 00009
+ 10 | 0 | 00010
+(10 rows)
+
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 436feee396b..e407be22045 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -443,6 +443,15 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END;
+-- row-wise comparison can be shipped
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) > (990, 100) ORDER BY c1;
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) > (990, 100) ORDER BY c1;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) <= (10, 500) ORDER BY c1;
+SELECT c1,c2,c3 FROM ft2 WHERE (c1, c2) <= (10, 500) ORDER BY c1;
+
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
--
2.34.1
