Hi.
Recently, we were surprised by the following behavior - prepared
statement, selecting data from foreign table with varchar(N) field
couldn't push down "field = ANY($1)" expression, when switched to
generic plan. This looked like shown in the attached patch. Reproducer
is simple:
create extension postgres_fdw;
create server local foreign data wrapper postgres_fdw;
create user MAPPING FOR CURRENT_USER SERVER local;
create table test (c varchar(255));
create foreign table ftest (c varchar(255)) server local options
(table_name 'test');
set plan_cache_mode to force_generic_plan ; -- just for demonstration,
can happen with defautl plan_cache_mode, if planner decides that generic
plan is preferable
prepare s(varchar[]) as select * from ftest where c = any ($1);
explain verbose execute s('{test}');
QUERY PLAN
----------------------------------------------------------------------
Foreign Scan on public.ftest (cost=100.00..143.43 rows=7 width=516)
Output: c
Filter: ((ftest.c)::text = ANY (($1)::text[]))
Remote SQL: SELECT c FROM public.test
The issue is that we need to translate input array type from varchar[]
to text[].
Attaching patch to allow postgres_fdw to deparse such conversion.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From efc5eb338db6621243e25e0b0281ae69c465974d Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Wed, 27 Nov 2024 14:07:39 +0300
Subject: [PATCH] postgres_fdw could deparse ArrayCoerceExpr
---
contrib/postgres_fdw/deparse.c | 50 +++++++++++++++++++
.../postgres_fdw/expected/postgres_fdw.out | 21 ++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 9 ++++
3 files changed, 80 insertions(+)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 4680d517331..008237cb8f8 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -160,6 +160,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 deparseArrayCoerceExpr(ArrayCoerceExpr *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);
@@ -696,6 +697,34 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_ArrayCoerceExpr:
+ {
+ ArrayCoerceExpr *e = (ArrayCoerceExpr *) node;
+
+ /*
+ * Recurse to input subexpression.
+ */
+ if (!foreign_expr_walker((Node *) e->arg,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+
+ /*
+ * T_ArrayCoerceExpr must not introduce a collation not
+ * derived from an input foreign Var (same logic as for a
+ * function).
+ */
+ collation = e->resultcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
case T_BoolExpr:
{
BoolExpr *b = (BoolExpr *) node;
@@ -2913,6 +2942,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_RelabelType:
deparseRelabelType((RelabelType *) node, context);
break;
+ case T_ArrayCoerceExpr:
+ deparseArrayCoerceExpr((ArrayCoerceExpr *) node, context);
+ break;
case T_BoolExpr:
deparseBoolExpr((BoolExpr *) node, context);
break;
@@ -3501,6 +3533,24 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
node->resulttypmod));
}
+/*
+ * Deparse a ArrayCoerceExpr (array-type conversion) node.
+ */
+static void
+deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context)
+{
+ deparseExpr(node->arg, context);
+
+ /*
+ * No difference how to deparse explicit cast, but if we omit implicit
+ * cast in the query, it'll be more user-friendly
+ */
+ if (node->coerceformat != COERCE_IMPLICIT_CAST)
+ appendStringInfo(context->buf, "::%s",
+ deparse_type_name(node->resulttype,
+ node->resulttypmod));
+}
+
/*
* Deparse a BoolExpr node.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98c..55a8ac9020e 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1169,6 +1169,27 @@ 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)
+-- Test array type conversion pushdown
+SET plan_cache_mode = force_generic_plan;
+PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE s(ARRAY['1','2']);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(*))
+ Relations: Aggregate on (public.ft2)
+ Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c6 = ANY ($1::character varying[])))
+(4 rows)
+
+EXECUTE s(ARRAY['1','2']);
+ count
+-------
+ 200
+(1 row)
+
+DEALLOCATE s;
+RESET plan_cache_mode;
-- 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 372fe6dad15..164cd8b895a 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -450,6 +450,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;
+-- Test array type conversion pushdown
+SET plan_cache_mode = force_generic_plan;
+PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE s(ARRAY['1','2']);
+EXECUTE s(ARRAY['1','2']);
+DEALLOCATE s;
+RESET plan_cache_mode;
+
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
--
2.43.0
