Skip to content

Commit 62c3b4c

Browse files
akorotkovakorotkov
committed
Support for deparsing of ArrayCoerceExpr node in contrib/postgres_fdw
When using a prepared statement to select data from a PostgreSQL foreign table (postgres_fdw) with the "field = ANY($1)" expression, the operation is not pushed down when an implicit type case is applied, and a generic plan is used. This commit resolves the issue by supporting the push-down of ArrayCoerceExpr, which is used in this case. The support is quite straightforward and similar to other nods, such as RelabelType. Discussion: https://postgr.es/m/4f0cea802476d23c6e799512ffd17aff%40postgrespro.ru Author: Alexander Pyhalov <a.pyhalov@postgrespro.ru> Reviewed-by: Maxim Orlov <orlovmg@gmail.com> Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
1 parent b597ae6 commit 62c3b4c

File tree

3 files changed

+80
-0
lines changed

3 files changed

+80
-0
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,7 @@ static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
161161
static void deparseScalarArrayOpExpr(ScalarArrayOpExpr *node,
162162
deparse_expr_cxt *context);
163163
static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
164+
static void deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context);
164165
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
165166
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
166167
static void deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
@@ -702,6 +703,34 @@ foreign_expr_walker(Node *node,
702703
state = FDW_COLLATE_UNSAFE;
703704
}
704705
break;
706+
case T_ArrayCoerceExpr:
707+
{
708+
ArrayCoerceExpr *e = (ArrayCoerceExpr *) node;
709+
710+
/*
711+
* Recurse to input subexpression.
712+
*/
713+
if (!foreign_expr_walker((Node *) e->arg,
714+
glob_cxt, &inner_cxt, case_arg_cxt))
715+
return false;
716+
717+
/*
718+
* T_ArrayCoerceExpr must not introduce a collation not
719+
* derived from an input foreign Var (same logic as for a
720+
* function).
721+
*/
722+
collation = e->resultcollid;
723+
if (collation == InvalidOid)
724+
state = FDW_COLLATE_NONE;
725+
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
726+
collation == inner_cxt.collation)
727+
state = FDW_COLLATE_SAFE;
728+
else if (collation == DEFAULT_COLLATION_OID)
729+
state = FDW_COLLATE_NONE;
730+
else
731+
state = FDW_COLLATE_UNSAFE;
732+
}
733+
break;
705734
case T_BoolExpr:
706735
{
707736
BoolExpr *b = (BoolExpr *) node;
@@ -2919,6 +2948,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
29192948
case T_RelabelType:
29202949
deparseRelabelType((RelabelType *) node, context);
29212950
break;
2951+
case T_ArrayCoerceExpr:
2952+
deparseArrayCoerceExpr((ArrayCoerceExpr *) node, context);
2953+
break;
29222954
case T_BoolExpr:
29232955
deparseBoolExpr((BoolExpr *) node, context);
29242956
break;
@@ -3507,6 +3539,24 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
35073539
node->resulttypmod));
35083540
}
35093541

3542+
/*
3543+
* Deparse a ArrayCoerceExpr (array-type conversion) node.
3544+
*/
3545+
static void
3546+
deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context)
3547+
{
3548+
deparseExpr(node->arg, context);
3549+
3550+
/*
3551+
* No difference how to deparse explicit cast, but if we omit implicit
3552+
* cast in the query, it'll be more user-friendly
3553+
*/
3554+
if (node->coerceformat != COERCE_IMPLICIT_CAST)
3555+
appendStringInfo(context->buf, "::%s",
3556+
deparse_type_name(node->resulttype,
3557+
node->resulttypmod));
3558+
}
3559+
35103560
/*
35113561
* Deparse a BoolExpr node.
35123562
*/

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1180,6 +1180,27 @@ SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' EN
11801180
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
11811181
(4 rows)
11821182

1183+
-- Test array type conversion pushdown
1184+
SET plan_cache_mode = force_generic_plan;
1185+
PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
1186+
EXPLAIN (VERBOSE, COSTS OFF)
1187+
EXECUTE s(ARRAY['1','2']);
1188+
QUERY PLAN
1189+
---------------------------------------------------------------------------------------------
1190+
Foreign Scan
1191+
Output: (count(*))
1192+
Relations: Aggregate on (public.ft2)
1193+
Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c6 = ANY ($1::character varying[])))
1194+
(4 rows)
1195+
1196+
EXECUTE s(ARRAY['1','2']);
1197+
count
1198+
-------
1199+
200
1200+
(1 row)
1201+
1202+
DEALLOCATE s;
1203+
RESET plan_cache_mode;
11831204
-- a regconfig constant referring to this text search configuration
11841205
-- is initially unshippable
11851206
CREATE TEXT SEARCH CONFIGURATION public.custom_search

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -458,6 +458,15 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
458458
EXPLAIN (VERBOSE, COSTS OFF)
459459
SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END;
460460

461+
-- Test array type conversion pushdown
462+
SET plan_cache_mode = force_generic_plan;
463+
PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
464+
EXPLAIN (VERBOSE, COSTS OFF)
465+
EXECUTE s(ARRAY['1','2']);
466+
EXECUTE s(ARRAY['1','2']);
467+
DEALLOCATE s;
468+
RESET plan_cache_mode;
469+
461470
-- a regconfig constant referring to this text search configuration
462471
-- is initially unshippable
463472
CREATE TEXT SEARCH CONFIGURATION public.custom_search

0 commit comments

Comments
 (0)