From 655148c85768afbbfc034e6f5dc5a5a6d72139b8 Mon Sep 17 00:00:00 2001 From: Alexander Pyhalov 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