aboutsummaryrefslogtreecommitdiff
path: root/contrib/postgres_fdw
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/postgres_fdw')
-rw-r--r--contrib/postgres_fdw/deparse.c89
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out101
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql11
3 files changed, 135 insertions, 66 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 81cb2b447d8..697de60dfe5 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -17,11 +17,12 @@
* We do not consider that it is ever safe to send COLLATE expressions to
* the remote server: it might not have the same collation names we do.
* (Later we might consider it safe to send COLLATE "C", but even that would
- * fail on old remote servers.) An expression is considered safe to send only
- * if all collations used in it are traceable to Var(s) of the foreign table.
- * That implies that if the remote server gets a different answer than we do,
- * the foreign table's columns are not marked with collations that match the
- * remote table's columns, which we can consider to be user error.
+ * fail on old remote servers.) An expression is considered safe to send
+ * only if all operator/function input collations used in it are traceable to
+ * Var(s) of the foreign table. That implies that if the remote server gets
+ * a different answer than we do, the foreign table's columns are not marked
+ * with collations that match the remote table's columns, which we can
+ * consider to be user error.
*
* Portions Copyright (c) 2012-2015, PostgreSQL Global Development Group
*
@@ -69,9 +70,12 @@ typedef struct foreign_glob_cxt
*/
typedef enum
{
- FDW_COLLATE_NONE, /* expression is of a noncollatable type */
+ FDW_COLLATE_NONE, /* expression is of a noncollatable type, or
+ * it has default collation that is not
+ * traceable to a foreign Var */
FDW_COLLATE_SAFE, /* collation derives from a foreign Var */
- FDW_COLLATE_UNSAFE /* collation derives from something else */
+ FDW_COLLATE_UNSAFE /* collation is non-default and derives from
+ * something other than a foreign Var */
} FDWCollateState;
typedef struct foreign_loc_cxt
@@ -272,13 +276,24 @@ foreign_expr_walker(Node *node,
else
{
/* Var belongs to some other table */
- if (var->varcollid != InvalidOid &&
- var->varcollid != DEFAULT_COLLATION_OID)
- return false;
-
- /* We can consider that it doesn't set collation */
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
+ collation = var->varcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ {
+ /*
+ * It's noncollatable, or it's safe to combine with a
+ * collatable foreign Var, so set state to NONE.
+ */
+ state = FDW_COLLATE_NONE;
+ }
+ else
+ {
+ /*
+ * Do not fail right away, since the Var might appear
+ * in a collation-insensitive context.
+ */
+ state = FDW_COLLATE_UNSAFE;
+ }
}
}
break;
@@ -288,16 +303,16 @@ foreign_expr_walker(Node *node,
/*
* If the constant has nondefault collation, either it's of a
- * non-builtin type, or it reflects folding of a CollateExpr;
- * either way, it's unsafe to send to the remote.
+ * non-builtin type, or it reflects folding of a CollateExpr.
+ * It's unsafe to send to the remote unless it's used in a
+ * non-collation-sensitive context.
*/
- if (c->constcollid != InvalidOid &&
- c->constcollid != DEFAULT_COLLATION_OID)
- return false;
-
- /* Otherwise, we can consider that it doesn't set collation */
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
+ collation = c->constcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
}
break;
case T_Param:
@@ -305,14 +320,14 @@ foreign_expr_walker(Node *node,
Param *p = (Param *) node;
/*
- * Collation handling is same as for Consts.
+ * Collation rule is same as for Consts and non-foreign Vars.
*/
- if (p->paramcollid != InvalidOid &&
- p->paramcollid != DEFAULT_COLLATION_OID)
- return false;
-
- collation = InvalidOid;
- state = FDW_COLLATE_NONE;
+ collation = p->paramcollid;
+ if (collation == InvalidOid ||
+ collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
}
break;
case T_ArrayRef:
@@ -348,6 +363,8 @@ foreign_expr_walker(Node *node,
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
else
state = FDW_COLLATE_UNSAFE;
}
@@ -393,6 +410,8 @@ foreign_expr_walker(Node *node,
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
else
state = FDW_COLLATE_UNSAFE;
}
@@ -434,6 +453,8 @@ foreign_expr_walker(Node *node,
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
else
state = FDW_COLLATE_UNSAFE;
}
@@ -483,7 +504,7 @@ foreign_expr_walker(Node *node,
/*
* RelabelType must not introduce a collation not derived from
- * an input foreign Var.
+ * an input foreign Var (same logic as for a real function).
*/
collation = r->resultcollid;
if (collation == InvalidOid)
@@ -491,6 +512,8 @@ foreign_expr_walker(Node *node,
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
else
state = FDW_COLLATE_UNSAFE;
}
@@ -540,7 +563,7 @@ foreign_expr_walker(Node *node,
/*
* ArrayExpr must not introduce a collation not derived from
- * an input foreign Var.
+ * an input foreign Var (same logic as for a function).
*/
collation = a->array_collid;
if (collation == InvalidOid)
@@ -548,6 +571,8 @@ foreign_expr_walker(Node *node,
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
else
state = FDW_COLLATE_UNSAFE;
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 1f417b30be8..65ea6e8d7d5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1005,71 +1005,110 @@ COMMIT;
-- ===================================================================
-- test handling of collations
-- ===================================================================
-create table loct3 (f1 text collate "C", f2 text);
-create foreign table ft3 (f1 text collate "C", f2 text)
- server loopback options (table_name 'loct3');
+create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique);
+create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
+ server loopback options (table_name 'loct3', use_remote_estimate 'true');
-- can be sent to remote
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
- Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
+ Output: f1, f2, f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
(3 rows)
explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
- Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
+ Output: f1, f2, f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
(3 rows)
explain (verbose, costs off) select * from ft3 where f2 = 'foo';
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
- Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f2 = 'foo'::text))
+ Output: f1, f2, f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f2 = 'foo'::text))
(3 rows)
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Foreign Scan on public.ft3
+ Output: f1, f2, f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
+(3 rows)
+
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ -> Index Scan using loct3_f1_key on public.loct3 l
+ Output: l.f1, l.f2, l.f3
+ Index Cond: (l.f1 = 'foo'::text)
+ -> Foreign Scan on public.ft3 f
+ Output: f.f1, f.f2, f.f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE (($1::character varying(10) = f3))
+(8 rows)
+
-- can't be sent to remote
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
+ Output: f1, f2, f3
Filter: ((ft3.f1)::text = 'foo'::text)
- Remote SQL: SELECT f1, f2 FROM public.loct3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
+ Output: f1, f2, f3
Filter: (ft3.f1 = 'foo'::text COLLATE "C")
- Remote SQL: SELECT f1, f2 FROM public.loct3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
+ Output: f1, f2, f3
Filter: ((ft3.f2)::text = 'foo'::text)
- Remote SQL: SELECT f1, f2 FROM public.loct3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Foreign Scan on public.ft3
- Output: f1, f2
+ Output: f1, f2, f3
Filter: (ft3.f2 = 'foo'::text COLLATE "C")
- Remote SQL: SELECT f1, f2 FROM public.loct3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ Hash Cond: ((f.f3)::text = (l.f3)::text)
+ -> Foreign Scan on public.ft3 f
+ Output: f.f1, f.f2, f.f3
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3
+ -> Hash
+ Output: l.f1, l.f2, l.f3
+ -> Index Scan using loct3_f1_key on public.loct3 l
+ Output: l.f1, l.f2, l.f3
+ Index Cond: (l.f1 = 'foo'::text)
+(11 rows)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index fcdd92e280b..11160f82455 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -316,19 +316,24 @@ COMMIT;
-- ===================================================================
-- test handling of collations
-- ===================================================================
-create table loct3 (f1 text collate "C", f2 text);
-create foreign table ft3 (f1 text collate "C", f2 text)
- server loopback options (table_name 'loct3');
+create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10) unique);
+create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
+ server loopback options (table_name 'loct3', use_remote_estimate 'true');
-- can be sent to remote
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
explain (verbose, costs off) select * from ft3 where f2 = 'foo';
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
-- can't be sent to remote
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
-- ===================================================================
-- test writable foreign table stuff
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-01 05:30:21 +0000