Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit62c3b4c

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

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
staticvoiddeparseScalarArrayOpExpr(ScalarArrayOpExpr*node,
162162
deparse_expr_cxt*context);
163163
staticvoiddeparseRelabelType(RelabelType*node,deparse_expr_cxt*context);
164+
staticvoiddeparseArrayCoerceExpr(ArrayCoerceExpr*node,deparse_expr_cxt*context);
164165
staticvoiddeparseBoolExpr(BoolExpr*node,deparse_expr_cxt*context);
165166
staticvoiddeparseNullTest(NullTest*node,deparse_expr_cxt*context);
166167
staticvoiddeparseCaseExpr(CaseExpr*node,deparse_expr_cxt*context);
@@ -702,6 +703,34 @@ foreign_expr_walker(Node *node,
702703
state=FDW_COLLATE_UNSAFE;
703704
}
704705
break;
706+
caseT_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+
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
726+
collation==inner_cxt.collation)
727+
state=FDW_COLLATE_SAFE;
728+
elseif (collation==DEFAULT_COLLATION_OID)
729+
state=FDW_COLLATE_NONE;
730+
else
731+
state=FDW_COLLATE_UNSAFE;
732+
}
733+
break;
705734
caseT_BoolExpr:
706735
{
707736
BoolExpr*b= (BoolExpr*)node;
@@ -2919,6 +2948,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
29192948
caseT_RelabelType:
29202949
deparseRelabelType((RelabelType*)node,context);
29212950
break;
2951+
caseT_ArrayCoerceExpr:
2952+
deparseArrayCoerceExpr((ArrayCoerceExpr*)node,context);
2953+
break;
29222954
caseT_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+
staticvoid
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 ft1WHERE 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[])ASSELECTcount(*)FROM ft2WHERE 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
CREATETEXT SEARCH CONFIGURATIONpublic.custom_search

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp