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

Commit76f965f

Browse files
committed
Improve handling of collations in contrib/postgres_fdw.
If we have a local Var of say varchar type with default collation, andwe apply a RelabelType to convert that to text with default collation, wedon't want to consider that as creating an FDW_COLLATE_UNSAFE situation.It should be okay to compare that to a remote Var, so long as the remoteVar determines the comparison collation. (When we actually ship such anexpression to the remote side, the local Var would become a Param withdefault collation, meaning the remote Var would in fact control thecomparison collation, because non-default implicit collation overridesdefault implicit collation in parse_collate.c.) To fix, be more preciseabout what FDW_COLLATE_NONE means: it applies either to a noncollatabledata type or to a collatable type with default collation, if that collationcan't be traced to a remote Var. (When it can, FDW_COLLATE_SAFE isappropriate.) We were essentially using that interpretation already atthe Var/Const/Param level, but we weren't bubbling it up properly.An alternative fix would be to introduce a separate FDW_COLLATE_DEFAULTvalue to describe the second situation, but that would add more codewithout changing the actual behavior, so it didn't seem worthwhile.Also, since we're clarifying the rule to be that we care about whetheroperator/function input collations match, there seems no need to failimmediately upon seeing a Const/Param/non-foreign-Var with nondefaultcollation. We only have to reject if it appears in a collation-sensitivecontext (for example, "var IS NOT NULL" is perfectly safe from a collationstandpoint, whatever collation the var has). So just set the state toUNSAFE rather than failing immediately.Per report from Jeevan Chalke. This essentially corrects some sloppythinking in commited3ddf9, so back-patchto 9.3 where that logic appeared.
1 parent9f1255a commit76f965f

File tree

3 files changed

+135
-66
lines changed

3 files changed

+135
-66
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 57 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -17,11 +17,12 @@
1717
* We do not consider that it is ever safe to send COLLATE expressions to
1818
* the remote server: it might not have the same collation names we do.
1919
* (Later we might consider it safe to send COLLATE "C", but even that would
20-
* fail on old remote servers.) An expression is considered safe to send only
21-
* if all collations used in it are traceable to Var(s) of the foreign table.
22-
* That implies that if the remote server gets a different answer than we do,
23-
* the foreign table's columns are not marked with collations that match the
24-
* remote table's columns, which we can consider to be user error.
20+
* fail on old remote servers.) An expression is considered safe to send
21+
* only if all operator/function input collations used in it are traceable to
22+
* Var(s) of the foreign table. That implies that if the remote server gets
23+
* a different answer than we do, the foreign table's columns are not marked
24+
* with collations that match the remote table's columns, which we can
25+
* consider to be user error.
2526
*
2627
* Portions Copyright (c) 2012-2015, PostgreSQL Global Development Group
2728
*
@@ -69,9 +70,12 @@ typedef struct foreign_glob_cxt
6970
*/
7071
typedefenum
7172
{
72-
FDW_COLLATE_NONE,/* expression is of a noncollatable type */
73+
FDW_COLLATE_NONE,/* expression is of a noncollatable type, or
74+
* it has default collation that is not
75+
* traceable to a foreign Var */
7376
FDW_COLLATE_SAFE,/* collation derives from a foreign Var */
74-
FDW_COLLATE_UNSAFE/* collation derives from something else */
77+
FDW_COLLATE_UNSAFE/* collation is non-default and derives from
78+
* something other than a foreign Var */
7579
}FDWCollateState;
7680

7781
typedefstructforeign_loc_cxt
@@ -272,13 +276,24 @@ foreign_expr_walker(Node *node,
272276
else
273277
{
274278
/* Var belongs to some other table */
275-
if (var->varcollid!=InvalidOid&&
276-
var->varcollid!=DEFAULT_COLLATION_OID)
277-
return false;
278-
279-
/* We can consider that it doesn't set collation */
280-
collation=InvalidOid;
281-
state=FDW_COLLATE_NONE;
279+
collation=var->varcollid;
280+
if (collation==InvalidOid||
281+
collation==DEFAULT_COLLATION_OID)
282+
{
283+
/*
284+
* It's noncollatable, or it's safe to combine with a
285+
* collatable foreign Var, so set state to NONE.
286+
*/
287+
state=FDW_COLLATE_NONE;
288+
}
289+
else
290+
{
291+
/*
292+
* Do not fail right away, since the Var might appear
293+
* in a collation-insensitive context.
294+
*/
295+
state=FDW_COLLATE_UNSAFE;
296+
}
282297
}
283298
}
284299
break;
@@ -288,31 +303,31 @@ foreign_expr_walker(Node *node,
288303

289304
/*
290305
* If the constant has nondefault collation, either it's of a
291-
* non-builtin type, or it reflects folding of a CollateExpr;
292-
* either way, it's unsafe to send to the remote.
306+
* non-builtin type, or it reflects folding of a CollateExpr.
307+
* It's unsafe to send to the remote unless it's used in a
308+
* non-collation-sensitive context.
293309
*/
294-
if (c->constcollid!=InvalidOid&&
295-
c->constcollid!=DEFAULT_COLLATION_OID)
296-
return false;
297-
298-
/* Otherwise, we can consider that it doesn't set collation */
299-
collation=InvalidOid;
300-
state=FDW_COLLATE_NONE;
310+
collation=c->constcollid;
311+
if (collation==InvalidOid||
312+
collation==DEFAULT_COLLATION_OID)
313+
state=FDW_COLLATE_NONE;
314+
else
315+
state=FDW_COLLATE_UNSAFE;
301316
}
302317
break;
303318
caseT_Param:
304319
{
305320
Param*p= (Param*)node;
306321

307322
/*
308-
* Collationhandling is same as for Consts.
323+
* Collationrule is same as for Consts and non-foreign Vars.
309324
*/
310-
if (p->paramcollid!=InvalidOid&&
311-
p->paramcollid!=DEFAULT_COLLATION_OID)
312-
return false;
313-
314-
collation=InvalidOid;
315-
state=FDW_COLLATE_NONE;
325+
collation=p->paramcollid;
326+
if (collation==InvalidOid||
327+
collation==DEFAULT_COLLATION_OID)
328+
state=FDW_COLLATE_NONE;
329+
else
330+
state=FDW_COLLATE_UNSAFE;
316331
}
317332
break;
318333
caseT_ArrayRef:
@@ -348,6 +363,8 @@ foreign_expr_walker(Node *node,
348363
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
349364
collation==inner_cxt.collation)
350365
state=FDW_COLLATE_SAFE;
366+
elseif (collation==DEFAULT_COLLATION_OID)
367+
state=FDW_COLLATE_NONE;
351368
else
352369
state=FDW_COLLATE_UNSAFE;
353370
}
@@ -393,6 +410,8 @@ foreign_expr_walker(Node *node,
393410
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
394411
collation==inner_cxt.collation)
395412
state=FDW_COLLATE_SAFE;
413+
elseif (collation==DEFAULT_COLLATION_OID)
414+
state=FDW_COLLATE_NONE;
396415
else
397416
state=FDW_COLLATE_UNSAFE;
398417
}
@@ -434,6 +453,8 @@ foreign_expr_walker(Node *node,
434453
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
435454
collation==inner_cxt.collation)
436455
state=FDW_COLLATE_SAFE;
456+
elseif (collation==DEFAULT_COLLATION_OID)
457+
state=FDW_COLLATE_NONE;
437458
else
438459
state=FDW_COLLATE_UNSAFE;
439460
}
@@ -483,14 +504,16 @@ foreign_expr_walker(Node *node,
483504

484505
/*
485506
* RelabelType must not introduce a collation not derived from
486-
* an input foreign Var.
507+
* an input foreign Var (same logic as for a real function).
487508
*/
488509
collation=r->resultcollid;
489510
if (collation==InvalidOid)
490511
state=FDW_COLLATE_NONE;
491512
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
492513
collation==inner_cxt.collation)
493514
state=FDW_COLLATE_SAFE;
515+
elseif (collation==DEFAULT_COLLATION_OID)
516+
state=FDW_COLLATE_NONE;
494517
else
495518
state=FDW_COLLATE_UNSAFE;
496519
}
@@ -540,14 +563,16 @@ foreign_expr_walker(Node *node,
540563

541564
/*
542565
* ArrayExpr must not introduce a collation not derived from
543-
* an input foreign Var.
566+
* an input foreign Var (same logic as for a function).
544567
*/
545568
collation=a->array_collid;
546569
if (collation==InvalidOid)
547570
state=FDW_COLLATE_NONE;
548571
elseif (inner_cxt.state==FDW_COLLATE_SAFE&&
549572
collation==inner_cxt.collation)
550573
state=FDW_COLLATE_SAFE;
574+
elseif (collation==DEFAULT_COLLATION_OID)
575+
state=FDW_COLLATE_NONE;
551576
else
552577
state=FDW_COLLATE_UNSAFE;
553578
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 70 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1005,71 +1005,110 @@ COMMIT;
10051005
-- ===================================================================
10061006
-- test handling of collations
10071007
-- ===================================================================
1008-
create table loct3 (f1 text collate "C", f2 text);
1009-
create foreign table ft3 (f1 text collate "C", f2 text)
1010-
server loopback options (table_name 'loct3');
1008+
create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique);
1009+
create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
1010+
server loopback options (table_name 'loct3', use_remote_estimate 'true');
10111011
-- can be sent to remote
10121012
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
1013-
QUERY PLAN
1014-
--------------------------------------------------------------------------
1013+
QUERY PLAN
1014+
------------------------------------------------------------------------------
10151015
Foreign Scan on public.ft3
1016-
Output: f1, f2
1017-
Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
1016+
Output: f1, f2, f3
1017+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
10181018
(3 rows)
10191019

10201020
explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
1021-
QUERY PLAN
1022-
--------------------------------------------------------------------------
1021+
QUERY PLAN
1022+
------------------------------------------------------------------------------
10231023
Foreign Scan on public.ft3
1024-
Output: f1, f2
1025-
Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
1024+
Output: f1, f2, f3
1025+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
10261026
(3 rows)
10271027

10281028
explain (verbose, costs off) select * from ft3 where f2 = 'foo';
1029-
QUERY PLAN
1030-
--------------------------------------------------------------------------
1029+
QUERY PLAN
1030+
------------------------------------------------------------------------------
10311031
Foreign Scan on public.ft3
1032-
Output: f1, f2
1033-
Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f2 = 'foo'::text))
1032+
Output: f1, f2, f3
1033+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f2 = 'foo'::text))
10341034
(3 rows)
10351035

1036+
explain (verbose, costs off) select * from ft3 where f3 = 'foo';
1037+
QUERY PLAN
1038+
------------------------------------------------------------------------------
1039+
Foreign Scan on public.ft3
1040+
Output: f1, f2, f3
1041+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
1042+
(3 rows)
1043+
1044+
explain (verbose, costs off) select * from ft3 f, loct3 l
1045+
where f.f3 = l.f3 and l.f1 = 'foo';
1046+
QUERY PLAN
1047+
--------------------------------------------------------------------------------------------------
1048+
Nested Loop
1049+
Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
1050+
-> Index Scan using loct3_f1_key on public.loct3 l
1051+
Output: l.f1, l.f2, l.f3
1052+
Index Cond: (l.f1 = 'foo'::text)
1053+
-> Foreign Scan on public.ft3 f
1054+
Output: f.f1, f.f2, f.f3
1055+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE (($1::character varying(10) = f3))
1056+
(8 rows)
1057+
10361058
-- can't be sent to remote
10371059
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
1038-
QUERY PLAN
1039-
-----------------------------------------------
1060+
QUERY PLAN
1061+
---------------------------------------------------
10401062
Foreign Scan on public.ft3
1041-
Output: f1, f2
1063+
Output: f1, f2, f3
10421064
Filter: ((ft3.f1)::text = 'foo'::text)
1043-
Remote SQL: SELECT f1, f2 FROM public.loct3
1065+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
10441066
(4 rows)
10451067

10461068
explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
1047-
QUERY PLAN
1048-
-----------------------------------------------
1069+
QUERY PLAN
1070+
---------------------------------------------------
10491071
Foreign Scan on public.ft3
1050-
Output: f1, f2
1072+
Output: f1, f2, f3
10511073
Filter: (ft3.f1 = 'foo'::text COLLATE "C")
1052-
Remote SQL: SELECT f1, f2 FROM public.loct3
1074+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
10531075
(4 rows)
10541076

10551077
explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
1056-
QUERY PLAN
1057-
-----------------------------------------------
1078+
QUERY PLAN
1079+
---------------------------------------------------
10581080
Foreign Scan on public.ft3
1059-
Output: f1, f2
1081+
Output: f1, f2, f3
10601082
Filter: ((ft3.f2)::text = 'foo'::text)
1061-
Remote SQL: SELECT f1, f2 FROM public.loct3
1083+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
10621084
(4 rows)
10631085

10641086
explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
1065-
QUERY PLAN
1066-
-----------------------------------------------
1087+
QUERY PLAN
1088+
---------------------------------------------------
10671089
Foreign Scan on public.ft3
1068-
Output: f1, f2
1090+
Output: f1, f2, f3
10691091
Filter: (ft3.f2 = 'foo'::text COLLATE "C")
1070-
Remote SQL: SELECT f1, f2 FROM public.loct3
1092+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
10711093
(4 rows)
10721094

1095+
explain (verbose, costs off) select * from ft3 f, loct3 l
1096+
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
1097+
QUERY PLAN
1098+
-------------------------------------------------------------
1099+
Hash Join
1100+
Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
1101+
Hash Cond: ((f.f3)::text = (l.f3)::text)
1102+
-> Foreign Scan on public.ft3 f
1103+
Output: f.f1, f.f2, f.f3
1104+
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
1105+
-> Hash
1106+
Output: l.f1, l.f2, l.f3
1107+
-> Index Scan using loct3_f1_key on public.loct3 l
1108+
Output: l.f1, l.f2, l.f3
1109+
Index Cond: (l.f1 = 'foo'::text)
1110+
(11 rows)
1111+
10731112
-- ===================================================================
10741113
-- test writable foreign table stuff
10751114
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -316,19 +316,24 @@ COMMIT;
316316
-- ===================================================================
317317
-- test handling of collations
318318
-- ===================================================================
319-
createtableloct3 (f1text collate"C", f2text);
320-
create foreign table ft3 (f1text collate"C", f2text)
321-
server loopback options (table_name'loct3');
319+
createtableloct3 (f1text collate"C" unique, f2text, f3varchar(10) unique);
320+
create foreign table ft3 (f1text collate"C", f2text, f3varchar(10))
321+
server loopback options (table_name'loct3', use_remote_estimate'true');
322322

323323
-- can be sent to remote
324324
explain (verbose, costs off)select*from ft3where f1='foo';
325325
explain (verbose, costs off)select*from ft3where f1 COLLATE"C"='foo';
326326
explain (verbose, costs off)select*from ft3where f2='foo';
327+
explain (verbose, costs off)select*from ft3where f3='foo';
328+
explain (verbose, costs off)select*from ft3 f, loct3 l
329+
wheref.f3=l.f3andl.f1='foo';
327330
-- can't be sent to remote
328331
explain (verbose, costs off)select*from ft3where f1 COLLATE"POSIX"='foo';
329332
explain (verbose, costs off)select*from ft3where f1='foo' COLLATE"C";
330333
explain (verbose, costs off)select*from ft3where f2 COLLATE"C"='foo';
331334
explain (verbose, costs off)select*from ft3where f2='foo' COLLATE"C";
335+
explain (verbose, costs off)select*from ft3 f, loct3 l
336+
wheref.f3=l.f3 COLLATE"POSIX"andl.f1='foo';
332337

333338
-- ===================================================================
334339
-- test writable foreign table stuff

0 commit comments

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