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

Commit9612b98

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 parentc4dcdd0 commit9612b98

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
@@ -131,6 +131,10 @@ static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
131131
staticvoiddeparseBoolExpr(BoolExpr*node,deparse_expr_cxt*context);
132132
staticvoiddeparseNullTest(NullTest*node,deparse_expr_cxt*context);
133133
staticvoiddeparseArrayExpr(ArrayExpr*node,deparse_expr_cxt*context);
134+
staticvoidprintRemoteParam(intparamindex,Oidparamtype,int32paramtypmod,
135+
deparse_expr_cxt*context);
136+
staticvoidprintRemotePlaceholder(Oidparamtype,int32paramtypmod,
137+
deparse_expr_cxt*context);
134138

135139

136140
/*
@@ -1272,16 +1276,11 @@ deparseVar(Var *node, deparse_expr_cxt *context)
12721276
*context->params_list=lappend(*context->params_list,node);
12731277
}
12741278

1275-
appendStringInfo(buf,"$%d",pindex);
1276-
appendStringInfo(buf,"::%s",
1277-
format_type_with_typemod(node->vartype,
1278-
node->vartypmod));
1279+
printRemoteParam(pindex,node->vartype,node->vartypmod,context);
12791280
}
12801281
else
12811282
{
1282-
appendStringInfo(buf,"(SELECT null::%s)",
1283-
format_type_with_typemod(node->vartype,
1284-
node->vartypmod));
1283+
printRemotePlaceholder(node->vartype,node->vartypmod,context);
12851284
}
12861285
}
12871286
}
@@ -1388,26 +1387,12 @@ deparseConst(Const *node, deparse_expr_cxt *context)
13881387
*
13891388
* If we're generating the query "for real", add the Param to
13901389
* context->params_list if it's not already present, and then use its index
1391-
* in that list as the remote parameter number.
1392-
*
1393-
* If we're just generating the query for EXPLAIN, replace the Param with
1394-
* a dummy expression "(SELECT null::<type>)".In all extant versions of
1395-
* Postgres, the planner will see that as an unknown constant value, which is
1396-
* what we want. (If we sent a Param, recent versions might try to use the
1397-
* value supplied for the Param as an estimated or even constant value, which
1398-
* we don't want.) This might need adjustment if we ever make the planner
1399-
* flatten scalar subqueries.
1400-
*
1401-
* Note: we label the Param's type explicitly rather than relying on
1402-
* transmitting a numeric type OID in PQexecParams(). This allows us to
1403-
* avoid assuming that types have the same OIDs on the remote side as they
1404-
* do locally --- they need only have the same names.
1390+
* in that list as the remote parameter number. During EXPLAIN, there's
1391+
* no need to identify a parameter number.
14051392
*/
14061393
staticvoid
14071394
deparseParam(Param*node,deparse_expr_cxt*context)
14081395
{
1409-
StringInfobuf=context->buf;
1410-
14111396
if (context->params_list)
14121397
{
14131398
intpindex=0;
@@ -1427,16 +1412,11 @@ deparseParam(Param *node, deparse_expr_cxt *context)
14271412
*context->params_list=lappend(*context->params_list,node);
14281413
}
14291414

1430-
appendStringInfo(buf,"$%d",pindex);
1431-
appendStringInfo(buf,"::%s",
1432-
format_type_with_typemod(node->paramtype,
1433-
node->paramtypmod));
1415+
printRemoteParam(pindex,node->paramtype,node->paramtypmod,context);
14341416
}
14351417
else
14361418
{
1437-
appendStringInfo(buf,"(SELECT null::%s)",
1438-
format_type_with_typemod(node->paramtype,
1439-
node->paramtypmod));
1419+
printRemotePlaceholder(node->paramtype,node->paramtypmod,context);
14401420
}
14411421
}
14421422

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

‎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