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

Commit5b68d81

Browse files
committed
Fix contrib/postgres_fdw's remote-estimate representation of array Params.
We were emitting "(SELECT null::typename)", which is usually interpretedas a scalar subselect, but not so much in the context "x = ANY(...)".This led to remote-side parsing failures when remote_estimate is enabled.A quick and ugly fix is to stick in an extra cast step,"((SELECT null::typename)::typename)". The cast will be thrown away asredundant by parse analysis, but not before it's done its job of makingsure the grammar sees the ANY argument as an a_expr rather than aselect_with_parens. Per an example from Hannu Krosing.
1 parent9c53346 commit5b68d81

File tree

3 files changed

+76
-30
lines changed

3 files changed

+76
-30
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 54 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,10 @@ static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
132132
staticvoiddeparseBoolExpr(BoolExpr*node,deparse_expr_cxt*context);
133133
staticvoiddeparseNullTest(NullTest*node,deparse_expr_cxt*context);
134134
staticvoiddeparseArrayExpr(ArrayExpr*node,deparse_expr_cxt*context);
135+
staticvoidprintRemoteParam(intparamindex,Oidparamtype,int32paramtypmod,
136+
deparse_expr_cxt*context);
137+
staticvoidprintRemotePlaceholder(Oidparamtype,int32paramtypmod,
138+
deparse_expr_cxt*context);
135139

136140

137141
/*
@@ -1283,16 +1287,11 @@ deparseVar(Var *node, deparse_expr_cxt *context)
12831287
*context->params_list=lappend(*context->params_list,node);
12841288
}
12851289

1286-
appendStringInfo(buf,"$%d",pindex);
1287-
appendStringInfo(buf,"::%s",
1288-
format_type_with_typemod(node->vartype,
1289-
node->vartypmod));
1290+
printRemoteParam(pindex,node->vartype,node->vartypmod,context);
12901291
}
12911292
else
12921293
{
1293-
appendStringInfo(buf,"(SELECT null::%s)",
1294-
format_type_with_typemod(node->vartype,
1295-
node->vartypmod));
1294+
printRemotePlaceholder(node->vartype,node->vartypmod,context);
12961295
}
12971296
}
12981297
}
@@ -1399,26 +1398,12 @@ deparseConst(Const *node, deparse_expr_cxt *context)
13991398
*
14001399
* If we're generating the query "for real", add the Param to
14011400
* context->params_list if it's not already present, and then use its index
1402-
* in that list as the remote parameter number.
1403-
*
1404-
* If we're just generating the query for EXPLAIN, replace the Param with
1405-
* a dummy expression "(SELECT null::<type>)".In all extant versions of
1406-
* Postgres, the planner will see that as an unknown constant value, which is
1407-
* what we want. (If we sent a Param, recent versions might try to use the
1408-
* value supplied for the Param as an estimated or even constant value, which
1409-
* we don't want.) This might need adjustment if we ever make the planner
1410-
* flatten scalar subqueries.
1411-
*
1412-
* Note: we label the Param's type explicitly rather than relying on
1413-
* transmitting a numeric type OID in PQexecParams(). This allows us to
1414-
* avoid assuming that types have the same OIDs on the remote side as they
1415-
* do locally --- they need only have the same names.
1401+
* in that list as the remote parameter number. During EXPLAIN, there's
1402+
* no need to identify a parameter number.
14161403
*/
14171404
staticvoid
14181405
deparseParam(Param*node,deparse_expr_cxt*context)
14191406
{
1420-
StringInfobuf=context->buf;
1421-
14221407
if (context->params_list)
14231408
{
14241409
intpindex=0;
@@ -1438,16 +1423,11 @@ deparseParam(Param *node, deparse_expr_cxt *context)
14381423
*context->params_list=lappend(*context->params_list,node);
14391424
}
14401425

1441-
appendStringInfo(buf,"$%d",pindex);
1442-
appendStringInfo(buf,"::%s",
1443-
format_type_with_typemod(node->paramtype,
1444-
node->paramtypmod));
1426+
printRemoteParam(pindex,node->paramtype,node->paramtypmod,context);
14451427
}
14461428
else
14471429
{
1448-
appendStringInfo(buf,"(SELECT null::%s)",
1449-
format_type_with_typemod(node->paramtype,
1450-
node->paramtypmod));
1430+
printRemotePlaceholder(node->paramtype,node->paramtypmod,context);
14511431
}
14521432
}
14531433

@@ -1816,3 +1796,47 @@ deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context)
18161796
appendStringInfo(buf,"::%s",
18171797
format_type_with_typemod(node->array_typeid,-1));
18181798
}
1799+
1800+
/*
1801+
* Print the representation of a parameter to be sent to the remote side.
1802+
*
1803+
* Note: we always label the Param's type explicitly rather than relying on
1804+
* transmitting a numeric type OID in PQexecParams(). This allows us to
1805+
* avoid assuming that types have the same OIDs on the remote side as they
1806+
* do locally --- they need only have the same names.
1807+
*/
1808+
staticvoid
1809+
printRemoteParam(intparamindex,Oidparamtype,int32paramtypmod,
1810+
deparse_expr_cxt*context)
1811+
{
1812+
StringInfobuf=context->buf;
1813+
char*ptypename=format_type_with_typemod(paramtype,paramtypmod);
1814+
1815+
appendStringInfo(buf,"$%d::%s",paramindex,ptypename);
1816+
}
1817+
1818+
/*
1819+
* Print the representation of a placeholder for a parameter that will be
1820+
* sent to the remote side at execution time.
1821+
*
1822+
* This is used when we're just trying to EXPLAIN the remote query.
1823+
* We don't have the actual value of the runtime parameter yet, and we don't
1824+
* want the remote planner to generate a plan that depends on such a value
1825+
* anyway.Thus, we can't do something simple like "$1::paramtype".
1826+
* Instead, we emit "((SELECT null::paramtype)::paramtype)".
1827+
* In all extant versions of Postgres, the planner will see that as an unknown
1828+
* constant value, which is what we want. This might need adjustment if we
1829+
* ever make the planner flatten scalar subqueries. Note: the reason for the
1830+
* apparently useless outer cast is to ensure that the representation as a
1831+
* whole will be parsed as an a_expr and not a select_with_parens; the latter
1832+
* would do the wrong thing in the context "x = ANY(...)".
1833+
*/
1834+
staticvoid
1835+
printRemotePlaceholder(Oidparamtype,int32paramtypmod,
1836+
deparse_expr_cxt*context)
1837+
{
1838+
StringInfobuf=context->buf;
1839+
char*ptypename=format_type_with_typemod(paramtype,paramtypmod);
1840+
1841+
appendStringInfo(buf,"((SELECT null::%s)::%s)",ptypename,ptypename);
1842+
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -592,6 +592,25 @@ WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
592592
996 | 6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 996 | 6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo
593593
(100 rows)
594594

595+
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
596+
SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
597+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
598+
----+----+-------+------------------------------+--------------------------+----+------------+-----
599+
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
600+
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
601+
3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
602+
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
603+
(4 rows)
604+
605+
SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
606+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
607+
----+----+-------+------------------------------+--------------------------+----+------------+-----
608+
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
609+
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
610+
3 | 3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
611+
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
612+
(4 rows)
613+
595614
-- ===================================================================
596615
-- parameterized queries
597616
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,9 @@ EXPLAIN (VERBOSE, COSTS false)
200200
WHEREa.c2=6ANDb.c1=a.c1ANDa.c8='foo'ANDb.c7=upper(a.c7);
201201
SELECT*FROM ft2 a, ft2 b
202202
WHEREa.c2=6ANDb.c1=a.c1ANDa.c8='foo'ANDb.c7=upper(a.c7);
203+
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
204+
SELECT*FROM ft1WHERE c1= ANY (ARRAY(SELECT c1FROM ft2WHERE c1<5));
205+
SELECT*FROM ft2WHERE c1= ANY (ARRAY(SELECT c1FROM ft1WHERE c1<5));
203206

204207
-- ===================================================================
205208
-- parameterized queries

0 commit comments

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