Thread: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
I wrote:
>> So I think what is happening here is that postgres_fdw's version of
>> IMPORT FOREIGN SCHEMA translates "COLLATE default" on the remote
>> server to "COLLATE default" on the local one, which of course is
>> a big fail if the defaults don't match. That allows the local
>> planner to believe that remote ORDER BYs on the two foreign tables
>> will give compatible results, causing the merge join to not work
>> very well at all.
Here's a full patch addressing this issue. I decided that the best
way to address the test-instability problem is to explicitly give
collations to all the foreign-table columns for which it matters
in the postgres_fdw test. (For portability's sake, that has to be
"C" or "POSIX"; I mostly used "C".) Aside from ensuring that the
test still passes with some other prevailing locale, this seems like
a good idea since we'll then be testing the case we are encouraging
users to use.
And indeed, it immediately turned up a new problem: if we explicitly
assign a collation to a foreign-table column c, the system won't
ship WHERE clauses as simple as "c = 'foo'" to the remote. This
surprised me, but the reason turned out to be that what postgres_fdw
is actually seeing is something like
{OPEXPR
:opno 98
:opfuncid 67
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 950
:args (
{VAR
:varno 6
:varattno 4
:vartype 25
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 6
:varattnosyn 4
:location 171
}
{RELABELTYPE
:arg
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 341
:constvalue 9 [ 36 0 0 0 48 48 48 48 49 ]
}
:resulttype 25
:resulttypmod -1
:resultcollid 950
:relabelformat 2
:location -1
}
)
:location -1
}
that is, the constant is being explicitly relabeled with the correct
collation, and thus is_foreign_expr() thinks the collation shown by
the RelabelType node is an unsafely-introduced collation.
What I did about this was to change the recursion rule in
foreign_expr_walker() so that merging a safely-derived collation with
the same collation unsafely derived is considered safe. I think this
is all right, and it allows us to accept some cases that previously
were rejected as unsafe. But I might be missing something.
(BTW, there's an independent bug here, which is that we're getting
a tree of the above shape rather than a simple Const with the
appropriate collation; that is, this tree isn't fully const-folded.
This is a bug in canonicalize_ec_expression, which I'll go fix
separately. But it won't affect the problem at hand.)
This seems like a sufficiently large change in postgres_fdw's
behavior to require review, so I'll go add this to the next CF.
regards, tom lane
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ad37a74221..f0693a061c 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -320,7 +320,9 @@ foreign_expr_walker(Node *node,
/*
* If the Var is from the foreign table, we consider its
- * collation (if any) safe to use. If it is from another
+ * collation (if any) safe to use, *unless* it's
+ * DEFAULT_COLLATION_OID. We treat that as meaning "we don't
+ * know which collation this is". If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
@@ -342,7 +344,12 @@ foreign_expr_walker(Node *node,
/* Else check the collation */
collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_UNSAFE;
+ else
+ state = FDW_COLLATE_SAFE;
}
else
{
@@ -808,8 +815,24 @@ foreign_expr_walker(Node *node,
/*
* Now, merge my collation information into my parent's state.
+ *
+ * If one branch of an expression derives a non-default collation safely
+ * (that is, from a foreign Var) and another one derives the same
+ * collation unsafely, we can consider the expression safe overall. This
+ * allows cases such as "foreign_var = ('foo' COLLATE x)" where x is the
+ * same collation the foreign_var has anyway. Note that we will not ship
+ * any explicit COLLATE clause to the remote, but rely on it to re-derive
+ * the correct collation based on the foreign_var.
*/
- if (state > outer_cxt->state)
+ if (collation == outer_cxt->collation &&
+ ((state == FDW_COLLATE_UNSAFE &&
+ outer_cxt->state == FDW_COLLATE_SAFE) ||
+ (state == FDW_COLLATE_SAFE &&
+ outer_cxt->state == FDW_COLLATE_UNSAFE)))
+ {
+ outer_cxt->state = FDW_COLLATE_SAFE;
+ }
+ else if (state > outer_cxt->state)
{
/* Override previous parent state */
outer_cxt->collation = collation;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 90db550b92..4707e75d27 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -27,29 +27,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
-- Disable autovacuum for these tables to avoid unexpected effects of that
@@ -89,16 +89,18 @@ ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -106,28 +108,28 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
-- ===================================================================
-- tests for validator
@@ -277,7 +279,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tabl
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
-> Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
- Sort Key: t1.c3, t1.c1, t1.tableoid
+ Sort Key: t1.c3 COLLATE "C", t1.c1, t1.tableoid
-> Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -881,12 +883,12 @@ EXPLAIN (VERBOSE, COSTS OFF)
(6 rows)
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
QUERY PLAN
-------------------------------------------------------------------------------
Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
- Sort Key: ft2.c1, ft2.c3 COLLATE "C"
+ Sort Key: ft2.c1, ft2.c3 COLLATE "POSIX"
-> Foreign Scan on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -1808,7 +1810,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t
Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1"
=r2."C 1"))))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
- Sort Key: t.c1_3, t.c1_1
+ Sort Key: t.c1_3 COLLATE "C", t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
@@ -2008,7 +2010,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Hash Right Join
Output: t1.c1, t2.c1, t1.c3
Hash Cond: (t2.c1 = t1.c1)
@@ -2050,7 +2052,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
@@ -3495,7 +3497,7 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) orde
----------------------------------------------------------------------------------
Sort
Output: c2, c6, (sum(c1))
- Sort Key: ft1.c2, ft1.c6
+ Sort Key: ft1.c2, ft1.c6 COLLATE "C"
-> HashAggregate
Output: c2, c6, sum(c1)
Hash Key: ft1.c2
@@ -3879,12 +3881,12 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(4 rows)
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
@@ -3912,12 +3914,12 @@ EXECUTE st6;
(9 rows)
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 0"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(4 rows)
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
@@ -4125,36 +4127,14 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
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
-------------------------------------------------------------------------------
- Foreign Scan on public.ft3
- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
QUERY PLAN
------------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = '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
@@ -4165,12 +4145,12 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
-explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
+explain (verbose, costs off) select * from ft3 where f2 = 'foo';
QUERY PLAN
---------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Filter: (ft3.f1 = 'foo'::text COLLATE "C")
+ Filter: (ft3.f2 = 'foo'::text)
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
@@ -4192,6 +4172,33 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+ QUERY PLAN
+---------------------------------------------------
+ Foreign Scan on public.ft3
+ Output: f1, f2, f3
+ Filter: ((ft3.f3)::text = 'foo'::text)
+ 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 and l.f1 = 'foo';
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ Inner Unique: true
+ 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)
+(12 rows)
+
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
QUERY PLAN
@@ -4215,12 +4222,12 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-- ===================================================================
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
-
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
-> Subquery Scan on "*SELECT*"
- Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10),
NULL::user_enum
+ Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying COLLATE "C", 'ft2
'::character(10),NULL::user_enum
-> Foreign Scan on public.ft2 ft2_1
Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3)
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" LIMIT 20::bigint
@@ -5330,13 +5337,13 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Output: (ft2.tableoid)::regclass
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
-> Result
- Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft2 '::character(10), NULL::user_enum
+ Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft2 '::character(10), NULL::user_enum
(5 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
@@ -7551,12 +7558,12 @@ NOTICE: drop cascades to foreign table bar2
drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
insert into remt1 values (1, 'foo');
@@ -7755,10 +7762,10 @@ drop table itrtest;
drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
insert into utrtest values (1, 'foo');
@@ -7942,7 +7949,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -8203,11 +8210,11 @@ drop table loc3;
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
@@ -8232,7 +8239,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8240,7 +8247,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8264,8 +8271,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -8296,7 +8303,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8304,7 +8311,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | 42 | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8328,8 +8335,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 9fc53cad68..6e1b391808 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,7 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
@@ -4802,44 +4803,54 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, "
+ " pg_get_expr(adbin, adrelid), ");
+ if (import_collate)
+ appendStringInfo(&buf,
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.collname ELSE coll.collname END, "
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.nspname ELSE collnsp.nspname END ",
+ DEFAULT_COLLATION_OID,
+ DEFAULT_COLLATION_OID);
+ else
+ appendStringInfoString(&buf, " NULL, NULL ");
+ appendStringInfoString(&buf,
+ "FROM pg_class c "
+ " JOIN pg_namespace n ON "
+ " relnamespace = n.oid "
+ " LEFT JOIN pg_attribute a ON "
+ " attrelid = c.oid AND attnum > 0 "
+ " AND NOT attisdropped "
+ " LEFT JOIN pg_attrdef ad ON "
+ " adrelid = c.oid AND adnum = attnum ");
if (import_collate)
+ {
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " collname, "
- " collnsp.nspname "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum "
" LEFT JOIN pg_collation coll ON "
" coll.oid = attcollation "
" LEFT JOIN pg_namespace collnsp ON "
- " collnsp.oid = collnamespace ");
- else
+ " collnsp.oid = collnamespace "
+ " LEFT JOIN ("
+ " SELECT cd.collname, nd.nspname FROM"
+ " pg_collation cd, pg_namespace nd, pg_database d"
+ " WHERE nd.oid = cd.collnamespace AND"
+ " d.datname = current_database() AND");
+ /* collprovider is new as of v10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf,
+ " cd.collprovider = 'c' AND");
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " NULL, NULL "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum ");
-
+ " cd.collcollate = d.datcollate AND"
+ " cd.collctype = d.datctype AND"
+ " cd.collencoding IN (d.encoding, -1)"
+ " ORDER BY length(cd.collname) LIMIT 1 )"
+ " defcoll ON TRUE ");
+ }
appendStringInfoString(&buf,
"WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ","
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 83971665e3..8454f7ae27 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -31,29 +31,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
@@ -97,16 +97,18 @@ ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -115,11 +117,11 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
@@ -127,19 +129,19 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
-- ===================================================================
@@ -332,7 +334,7 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, random();
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
@@ -1119,15 +1121,15 @@ create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
-- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
-- 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 = 'foo';
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 where f3 = 'foo';
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
@@ -1931,12 +1933,12 @@ drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
@@ -2029,10 +2031,10 @@ drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
@@ -2109,7 +2111,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -2312,11 +2314,11 @@ drop table loc3;
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index eab2cc9378..50601fb208 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -89,6 +89,12 @@
need, surprising semantic anomalies may arise when types or collations do
not match, due to the remote server interpreting <literal>WHERE</literal> clauses
slightly differently from the local server.
+ Also, while you need not apply any explicit <literal>COLLATE</literal>
+ clause to a collatable column of a foreign table, not doing so will cause
+ the planner to assume that the remote column's collation does not match any
+ locally available collation. This will generally result in not sending
+ <literal>WHERE</literal> clauses involving such a column to the remote
+ server, which may greatly degrade query performance.
</para>
<para>
@@ -423,6 +429,10 @@ OPTIONS (ADD password_required 'false');
need to turn this off if the remote server has a different set of
collation names than the local server does, which is likely to be the
case if it's running on a different operating system.
+ (Note, however, that simply turning this off may result in
+ non-optimal plans for queries on the imported tables. It's best to
+ find a way to label collatable columns with collations that match the
+ remote server's behavior.)
</para>
</listitem>
</varlistentry>
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Peter Eisentraut
Date:
On 2020-08-18 22:09, Tom Lane wrote: > Here's a full patch addressing this issue. I decided that the best > way to address the test-instability problem is to explicitly give > collations to all the foreign-table columns for which it matters > in the postgres_fdw test. (For portability's sake, that has to be > "C" or "POSIX"; I mostly used "C".) Aside from ensuring that the > test still passes with some other prevailing locale, this seems like > a good idea since we'll then be testing the case we are encouraging > users to use. I have studied this patch and this functionality. I don't think collation differences between remote and local instances are handled sufficiently. This bug report and patch addresses one particular case, where the database-wide collation of the remote and local instance are different. But it doesn't handle cases like the same collation name doing different things, having different versions, or different attributes. This probably works currently because the libc collations don't have much functionality like that, but there is a variety of work conceived (or, in the case of version tracking, already done since the bug was first discussed) that would break that. Taking a step back, I think there are only two ways this could really work: Either, the admin makes a promise that all the collations match on all the instances; then the planner can take advantage of that. Or, there is no such promise, and then the planner can't. I don't understand what the currently implemented approach is. It appears to be something in the middle, where certain representations are made that certain things might match, and then there is some nontrivial code that analyzes expressions whether they conform to those rules. As you said, the description of the import_collate option is kind of hand-wavy about all this. -- Peter Eisentraut 2ndQuadrant, an EDB company https://www.2ndquadrant.com/
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I have studied this patch and this functionality. I don't think
> collation differences between remote and local instances are handled
> sufficiently. This bug report and patch addresses one particular case,
> where the database-wide collation of the remote and local instance are
> different. But it doesn't handle cases like the same collation name
> doing different things, having different versions, or different
> attributes.
Yeah, agreed. I don't think it's practical to have a 100% solution.
I'd make a couple of points:
* The design philosophy of postgres_fdw, to the extent it has one,
is that it's the user's responsibility to make sure that the local
declaration of a foreign table is a faithful model of the actual
remote object. There are certain variances you can get away with,
but in general, if it breaks it's your fault. (Admittedly, if the
local declaration was created via IMPORT FOREIGN SCHEMA, we would
like to be sure that it's right without help. But there's only
so much we can do there. There are already plenty of ways to
fool IMPORT FOREIGN SCHEMA anyway, for example if the same type
name refers to something different on the two systems.)
* Not being able to ship any qual conditions involving collatable
datatypes seems like an absolutely unacceptable outcome. Thus,
I don't buy your alternative of not letting the planner make
any assumptions at all about compatibility of remote collations.
I think that what this patch is basically doing is increasing the
visibility of collation compatibility as something that postgres_fdw
users need to take into account. Sure, it's not a 100% solution,
but it improves the situation, and it seems like we'd have to do
this anyway along the road to any better solution.
If you've got ideas about how to improve things further, by all
means let's discuss that ... but let's not make the perfect be
the enemy of the good.
regards, tom lane
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Rebased over b663a4136 --- no substantive changes, just keeping
the cfbot happy.
regards, tom lane
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6faf499f9a..c38e2419d5 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -320,7 +320,9 @@ foreign_expr_walker(Node *node,
/*
* If the Var is from the foreign table, we consider its
- * collation (if any) safe to use. If it is from another
+ * collation (if any) safe to use, *unless* it's
+ * DEFAULT_COLLATION_OID. We treat that as meaning "we don't
+ * know which collation this is". If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
@@ -342,7 +344,12 @@ foreign_expr_walker(Node *node,
/* Else check the collation */
collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_UNSAFE;
+ else
+ state = FDW_COLLATE_SAFE;
}
else
{
@@ -813,8 +820,24 @@ foreign_expr_walker(Node *node,
/*
* Now, merge my collation information into my parent's state.
+ *
+ * If one branch of an expression derives a non-default collation safely
+ * (that is, from a foreign Var) and another one derives the same
+ * collation unsafely, we can consider the expression safe overall. This
+ * allows cases such as "foreign_var = ('foo' COLLATE x)" where x is the
+ * same collation the foreign_var has anyway. Note that we will not ship
+ * any explicit COLLATE clause to the remote, but rely on it to re-derive
+ * the correct collation based on the foreign_var.
*/
- if (state > outer_cxt->state)
+ if (collation == outer_cxt->collation &&
+ ((state == FDW_COLLATE_UNSAFE &&
+ outer_cxt->state == FDW_COLLATE_SAFE) ||
+ (state == FDW_COLLATE_SAFE &&
+ outer_cxt->state == FDW_COLLATE_UNSAFE)))
+ {
+ outer_cxt->state = FDW_COLLATE_SAFE;
+ }
+ else if (state > outer_cxt->state)
{
/* Override previous parent state */
outer_cxt->collation = collation;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 60c7e115d6..05628d8aa7 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -32,29 +32,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
-- Disable autovacuum for these tables to avoid unexpected effects of that
@@ -94,16 +94,18 @@ ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -111,28 +113,28 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
c1 int NOT NULL,
@@ -288,7 +290,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tabl
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
-> Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
- Sort Key: t1.c3, t1.c1, t1.tableoid
+ Sort Key: t1.c3 COLLATE "C", t1.c1, t1.tableoid
-> Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -902,12 +904,12 @@ EXPLAIN (VERBOSE, COSTS OFF)
(6 rows)
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
QUERY PLAN
-------------------------------------------------------------------------------
Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
- Sort Key: ft2.c1, ft2.c3 COLLATE "C"
+ Sort Key: ft2.c1, ft2.c3 COLLATE "POSIX"
-> Foreign Scan on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -1829,7 +1831,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t
Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1"
=r2."C 1"))))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
- Sort Key: t.c1_3, t.c1_1
+ Sort Key: t.c1_3 COLLATE "C", t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
@@ -2029,7 +2031,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Hash Right Join
Output: t1.c1, t2.c1, t1.c3
Hash Cond: (t2.c1 = t1.c1)
@@ -2071,7 +2073,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
@@ -3516,7 +3518,7 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) orde
----------------------------------------------------------------------------------
Sort
Output: c2, c6, (sum(c1))
- Sort Key: ft1.c2, ft1.c6
+ Sort Key: ft1.c2, ft1.c6 COLLATE "C"
-> HashAggregate
Output: c2, c6, sum(c1)
Hash Key: ft1.c2
@@ -3900,13 +3902,13 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
@@ -3934,13 +3936,13 @@ EXECUTE st6;
(9 rows)
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 0"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
@@ -4169,36 +4171,14 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
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
-------------------------------------------------------------------------------
- Foreign Scan on public.ft3
- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
QUERY PLAN
------------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = '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
@@ -4209,12 +4189,12 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
-explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
+explain (verbose, costs off) select * from ft3 where f2 = 'foo';
QUERY PLAN
---------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Filter: (ft3.f1 = 'foo'::text COLLATE "C")
+ Filter: (ft3.f2 = 'foo'::text)
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
@@ -4236,6 +4216,33 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+ QUERY PLAN
+---------------------------------------------------
+ Foreign Scan on public.ft3
+ Output: f1, f2, f3
+ Filter: ((ft3.f3)::text = 'foo'::text)
+ 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 and l.f1 = 'foo';
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ Inner Unique: true
+ 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)
+(12 rows)
+
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
QUERY PLAN
@@ -4259,13 +4266,13 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-- ===================================================================
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
-
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Subquery Scan on "*SELECT*"
- Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10),
NULL::user_enum
+ Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying COLLATE "C", 'ft2
'::character(10),NULL::user_enum
-> Foreign Scan on public.ft2 ft2_1
Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3)
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" LIMIT 20::bigint
@@ -5375,14 +5382,14 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Output: (ft2.tableoid)::regclass
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft2 '::character(10), NULL::user_enum
+ Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft2 '::character(10), NULL::user_enum
(6 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
@@ -7599,12 +7606,12 @@ NOTICE: drop cascades to foreign table bar2
drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
insert into remt1 values (1, 'foo');
@@ -7803,10 +7810,10 @@ drop table itrtest;
drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
insert into utrtest values (1, 'foo');
@@ -7990,7 +7997,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -8251,11 +8258,11 @@ drop table loc3;
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
@@ -8280,7 +8287,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8288,7 +8295,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8312,8 +8319,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -8344,7 +8351,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8352,7 +8359,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | 42 | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8376,8 +8383,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0e977066a8..832c1e8d62 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,7 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
@@ -4969,44 +4970,54 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, "
+ " pg_get_expr(adbin, adrelid), ");
+ if (import_collate)
+ appendStringInfo(&buf,
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.collname ELSE coll.collname END, "
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.nspname ELSE collnsp.nspname END ",
+ DEFAULT_COLLATION_OID,
+ DEFAULT_COLLATION_OID);
+ else
+ appendStringInfoString(&buf, " NULL, NULL ");
+ appendStringInfoString(&buf,
+ "FROM pg_class c "
+ " JOIN pg_namespace n ON "
+ " relnamespace = n.oid "
+ " LEFT JOIN pg_attribute a ON "
+ " attrelid = c.oid AND attnum > 0 "
+ " AND NOT attisdropped "
+ " LEFT JOIN pg_attrdef ad ON "
+ " adrelid = c.oid AND adnum = attnum ");
if (import_collate)
+ {
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " collname, "
- " collnsp.nspname "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum "
" LEFT JOIN pg_collation coll ON "
" coll.oid = attcollation "
" LEFT JOIN pg_namespace collnsp ON "
- " collnsp.oid = collnamespace ");
- else
+ " collnsp.oid = collnamespace "
+ " LEFT JOIN ("
+ " SELECT cd.collname, nd.nspname FROM"
+ " pg_collation cd, pg_namespace nd, pg_database d"
+ " WHERE nd.oid = cd.collnamespace AND"
+ " d.datname = current_database() AND");
+ /* collprovider is new as of v10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf,
+ " cd.collprovider = 'c' AND");
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " NULL, NULL "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum ");
-
+ " cd.collcollate = d.datcollate AND"
+ " cd.collctype = d.datctype AND"
+ " cd.collencoding IN (d.encoding, -1)"
+ " ORDER BY length(cd.collname) LIMIT 1 )"
+ " defcoll ON TRUE ");
+ }
appendStringInfoString(&buf,
"WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ","
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 151f4f1834..a99bbf3b47 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -36,29 +36,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
@@ -102,16 +102,18 @@ ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -120,11 +122,11 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
@@ -132,19 +134,19 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
@@ -354,7 +356,7 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, random();
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
@@ -1161,15 +1163,15 @@ create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
-- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
-- 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 = 'foo';
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 where f3 = 'foo';
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
@@ -1973,12 +1975,12 @@ drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
@@ -2071,10 +2073,10 @@ drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
@@ -2151,7 +2153,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -2354,11 +2356,11 @@ drop table loc3;
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 8d6abd4c54..0b7008e2de 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -89,6 +89,12 @@
need, surprising semantic anomalies may arise when types or collations do
not match, due to the remote server interpreting <literal>WHERE</literal> clauses
slightly differently from the local server.
+ Also, while you need not apply any explicit <literal>COLLATE</literal>
+ clause to a collatable column of a foreign table, not doing so will cause
+ the planner to assume that the remote column's collation does not match any
+ locally available collation. This will generally result in not sending
+ <literal>WHERE</literal> clauses involving such a column to the remote
+ server, which may greatly degrade query performance.
</para>
<para>
@@ -436,6 +442,10 @@ OPTIONS (ADD password_required 'false');
need to turn this off if the remote server has a different set of
collation names than the local server does, which is likely to be the
case if it's running on a different operating system.
+ (Note, however, that simply turning this off may result in
+ non-optimal plans for queries on the imported tables. It's best to
+ find a way to label collatable columns with collations that match the
+ remote server's behavior.)
</para>
</listitem>
</varlistentry>
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Ibrar Ahmed
Date:
On Wed, Mar 3, 2021 at 1:42 PM Neil Chen <carpenter.nail.cz@gmail.com> wrote:
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: tested, passed
Greetings,
I learned about the patch and read your discussions. I'm not sure why this patch has not been discussed now. In short, I think it's beneficial to submit it as a temporary solution.
Another thing I want to know is whether these codes can be simplified:
- if (state > outer_cxt->state)
+ if (collation == outer_cxt->collation &&
+ ((state == FDW_COLLATE_UNSAFE &&
+ outer_cxt->state == FDW_COLLATE_SAFE) ||
+ (state == FDW_COLLATE_SAFE &&
+ outer_cxt->state == FDW_COLLATE_UNSAFE)))
+ {
+ outer_cxt->state = FDW_COLLATE_SAFE;
+ }
+ else if (state > outer_cxt->state)
If the state is determined by the collation, when the collations are equal, do we just need to judge the state not equal to FDW_COLLATE_NONE?
============== running regression test queries ==============
test postgres_fdw ... FAILED 2782 ms
============== shutting down postmaster ==============
======================
1 of 1 tests failed.
======================
The differences that caused some tests to fail can be viewed in the
file "/tmp/cirrus-ci-build/contrib/postgres_fdw/regression.diffs". A copy of the test summary that you see
above is saved in the file "/tmp/cirrus-ci-build/contrib/postgres_fdw/regression.out".
--
Ibrar Ahmed
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Ibrar Ahmed <ibrar.ahmad@gmail.com> writes:
> The patch is failing the regression, @Tom Lane <tgl@sss.pgh.pa.us> can you
> please take a look at that.
Seems to just need an update of the expected-file to account for test
cases added recently. (I take no position on whether the new results
are desirable; some of these might be breaking the intent of the case.
But this should quiet the cfbot anyway.)
regards, tom lane
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 31919fda8c..d68720423e 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -321,7 +321,9 @@ foreign_expr_walker(Node *node,
/*
* If the Var is from the foreign table, we consider its
- * collation (if any) safe to use. If it is from another
+ * collation (if any) safe to use, *unless* it's
+ * DEFAULT_COLLATION_OID. We treat that as meaning "we don't
+ * know which collation this is". If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
@@ -343,7 +345,12 @@ foreign_expr_walker(Node *node,
/* Else check the collation */
collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_UNSAFE;
+ else
+ state = FDW_COLLATE_SAFE;
}
else
{
@@ -814,8 +821,24 @@ foreign_expr_walker(Node *node,
/*
* Now, merge my collation information into my parent's state.
+ *
+ * If one branch of an expression derives a non-default collation safely
+ * (that is, from a foreign Var) and another one derives the same
+ * collation unsafely, we can consider the expression safe overall. This
+ * allows cases such as "foreign_var = ('foo' COLLATE x)" where x is the
+ * same collation the foreign_var has anyway. Note that we will not ship
+ * any explicit COLLATE clause to the remote, but rely on it to re-derive
+ * the correct collation based on the foreign_var.
*/
- if (state > outer_cxt->state)
+ if (collation == outer_cxt->collation &&
+ ((state == FDW_COLLATE_UNSAFE &&
+ outer_cxt->state == FDW_COLLATE_SAFE) ||
+ (state == FDW_COLLATE_SAFE &&
+ outer_cxt->state == FDW_COLLATE_UNSAFE)))
+ {
+ outer_cxt->state = FDW_COLLATE_SAFE;
+ }
+ else if (state > outer_cxt->state)
{
/* Override previous parent state */
outer_cxt->collation = collation;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e5bbf3b0af..4408543f55 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -32,29 +32,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
-- Disable autovacuum for these tables to avoid unexpected effects of that
@@ -94,16 +94,18 @@ ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -111,28 +113,28 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
c1 int NOT NULL,
@@ -288,7 +290,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tabl
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
-> Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
- Sort Key: t1.c3, t1.c1, t1.tableoid
+ Sort Key: t1.c3 COLLATE "C", t1.c1, t1.tableoid
-> Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -902,12 +904,12 @@ EXPLAIN (VERBOSE, COSTS OFF)
(6 rows)
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
QUERY PLAN
-------------------------------------------------------------------------------
Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
- Sort Key: ft2.c1, ft2.c3 COLLATE "C"
+ Sort Key: ft2.c1, ft2.c3 COLLATE "POSIX"
-> Foreign Scan on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -1831,7 +1833,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t
Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1"
=r2."C 1"))))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
- Sort Key: t.c1_3, t.c1_1
+ Sort Key: t.c1_3 COLLATE "C", t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
@@ -2031,7 +2033,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Hash Right Join
Output: t1.c1, t2.c1, t1.c3
Hash Cond: (t2.c1 = t1.c1)
@@ -2073,7 +2075,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
@@ -3521,7 +3523,7 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) orde
----------------------------------------------------------------------------------
Sort
Output: c2, c6, (sum(c1))
- Sort Key: ft1.c2, ft1.c6
+ Sort Key: ft1.c2, ft1.c6 COLLATE "C"
-> HashAggregate
Output: c2, c6, sum(c1)
Hash Key: ft1.c2
@@ -3905,13 +3907,13 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
@@ -3939,13 +3941,13 @@ EXECUTE st6;
(9 rows)
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 0"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
@@ -4176,36 +4178,14 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
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
-------------------------------------------------------------------------------
- Foreign Scan on public.ft3
- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
QUERY PLAN
------------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = '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
@@ -4216,12 +4196,12 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
-explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
+explain (verbose, costs off) select * from ft3 where f2 = 'foo';
QUERY PLAN
---------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Filter: (ft3.f1 = 'foo'::text COLLATE "C")
+ Filter: (ft3.f2 = 'foo'::text)
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
@@ -4243,6 +4223,33 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+ QUERY PLAN
+---------------------------------------------------
+ Foreign Scan on public.ft3
+ Output: f1, f2, f3
+ Filter: ((ft3.f3)::text = 'foo'::text)
+ 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 and l.f1 = 'foo';
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ Inner Unique: true
+ 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)
+(12 rows)
+
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
QUERY PLAN
@@ -4266,13 +4273,13 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-- ===================================================================
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
-
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Subquery Scan on "*SELECT*"
- Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10),
NULL::user_enum
+ Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying COLLATE "C", 'ft2
'::character(10),NULL::user_enum
-> Foreign Scan on public.ft2 ft2_1
Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3)
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" LIMIT 20::bigint
@@ -5382,14 +5389,14 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Output: (ft2.tableoid)::regclass
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft2 '::character(10), NULL::user_enum
+ Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft2 '::character(10), NULL::user_enum
(6 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
@@ -7719,12 +7726,12 @@ NOTICE: drop cascades to foreign table bar2
drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
insert into remt1 values (1, 'foo');
@@ -7933,10 +7940,10 @@ drop table itrtest;
drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
insert into utrtest values (1, 'foo');
@@ -8098,7 +8105,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -8559,11 +8566,11 @@ tru_rtable_parent,tru_rtable_child, tru_rtable0_child;
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
@@ -8590,7 +8597,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8598,7 +8605,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8622,8 +8629,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -8654,7 +8661,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8662,7 +8669,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | 42 | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8686,8 +8693,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -10378,14 +10385,16 @@ SELECT * FROM insert_tbl ORDER BY a;
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b
===505;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Insert on public.join_tbl
CTE t
-> Update on public.remote_tbl
Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
- -> Foreign Update on public.remote_tbl
- Remote SQL: UPDATE public.base_tbl3 SET c = (c || c) RETURNING a, b, c
+ Remote SQL: UPDATE public.base_tbl3 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
+ -> Foreign Scan on public.remote_tbl
+ Output: (remote_tbl.c || remote_tbl.c), remote_tbl.ctid, remote_tbl.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl3 FOR UPDATE
-> Nested Loop Left Join
Output: async_pt.a, async_pt.b, async_pt.c, t.a, t.b, t.c
Join Filter: ((async_pt.a = t.a) AND (async_pt.b = t.b))
@@ -10403,7 +10412,7 @@ INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND
Filter: (async_pt_3.b === 505)
-> CTE Scan on t
Output: t.a, t.b, t.c
-(23 rows)
+(25 rows)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b
===505;
@@ -10431,17 +10440,21 @@ UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
Update on public.async_pt
Output: async_pt_1.a, async_pt_1.b, async_pt_1.c
Foreign Update on public.async_p1 async_pt_1
+ Remote SQL: UPDATE public.base_tbl1 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
Foreign Update on public.async_p2 async_pt_2
+ Remote SQL: UPDATE public.base_tbl2 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
Update on public.async_p3 async_pt_3
-> Append
- -> Foreign Update on public.async_p1 async_pt_1
- Remote SQL: UPDATE public.base_tbl1 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
- -> Foreign Update on public.async_p2 async_pt_2
- Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
+ -> Foreign Scan on public.async_p1 async_pt_1
+ Output: (async_pt_1.c || async_pt_1.c), async_pt_1.tableoid, async_pt_1.ctid, async_pt_1.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl1 WHERE ((b = 0)) FOR UPDATE
+ -> Foreign Scan on public.async_p2 async_pt_2
+ Output: (async_pt_2.c || async_pt_2.c), async_pt_2.tableoid, async_pt_2.ctid, async_pt_2.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl2 WHERE ((b = 0)) FOR UPDATE
-> Seq Scan on public.async_p3 async_pt_3
Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record
Filter: (async_pt_3.b = 0)
-(13 rows)
+(17 rows)
UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
a | b | c
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index f15c97ad7a..be360f6f0c 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,7 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
@@ -5269,44 +5270,54 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, "
+ " pg_get_expr(adbin, adrelid), ");
+ if (import_collate)
+ appendStringInfo(&buf,
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.collname ELSE coll.collname END, "
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.nspname ELSE collnsp.nspname END ",
+ DEFAULT_COLLATION_OID,
+ DEFAULT_COLLATION_OID);
+ else
+ appendStringInfoString(&buf, " NULL, NULL ");
+ appendStringInfoString(&buf,
+ "FROM pg_class c "
+ " JOIN pg_namespace n ON "
+ " relnamespace = n.oid "
+ " LEFT JOIN pg_attribute a ON "
+ " attrelid = c.oid AND attnum > 0 "
+ " AND NOT attisdropped "
+ " LEFT JOIN pg_attrdef ad ON "
+ " adrelid = c.oid AND adnum = attnum ");
if (import_collate)
+ {
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " collname, "
- " collnsp.nspname "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum "
" LEFT JOIN pg_collation coll ON "
" coll.oid = attcollation "
" LEFT JOIN pg_namespace collnsp ON "
- " collnsp.oid = collnamespace ");
- else
+ " collnsp.oid = collnamespace "
+ " LEFT JOIN ("
+ " SELECT cd.collname, nd.nspname FROM"
+ " pg_collation cd, pg_namespace nd, pg_database d"
+ " WHERE nd.oid = cd.collnamespace AND"
+ " d.datname = current_database() AND");
+ /* collprovider is new as of v10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf,
+ " cd.collprovider = 'c' AND");
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
- " NULL, NULL "
- "FROM pg_class c "
- " JOIN pg_namespace n ON "
- " relnamespace = n.oid "
- " LEFT JOIN pg_attribute a ON "
- " attrelid = c.oid AND attnum > 0 "
- " AND NOT attisdropped "
- " LEFT JOIN pg_attrdef ad ON "
- " adrelid = c.oid AND adnum = attnum ");
-
+ " cd.collcollate = d.datcollate AND"
+ " cd.collctype = d.datctype AND"
+ " cd.collencoding IN (d.encoding, -1)"
+ " ORDER BY length(cd.collname) LIMIT 1 )"
+ " defcoll ON TRUE ");
+ }
appendStringInfoString(&buf,
"WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ","
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index fe503ed6c3..ac5c5d08ad 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -36,29 +36,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
@@ -102,16 +102,18 @@ ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -120,11 +122,11 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
@@ -132,19 +134,19 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
@@ -354,7 +356,7 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, random();
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
@@ -1165,15 +1167,15 @@ create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
-- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
-- 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 = 'foo';
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 where f3 = 'foo';
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
@@ -2010,12 +2012,12 @@ drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
@@ -2108,10 +2110,10 @@ drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
@@ -2188,7 +2190,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -2498,11 +2500,11 @@ tru_rtable_parent,tru_rtable_child, tru_rtable0_child;
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index c9fce77599..1aa71bc998 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -97,6 +97,12 @@
need, surprising semantic anomalies may arise when types or collations do
not match, due to the remote server interpreting <literal>WHERE</literal> clauses
slightly differently from the local server.
+ Also, while you need not apply any explicit <literal>COLLATE</literal>
+ clause to a collatable column of a foreign table, not doing so will cause
+ the planner to assume that the remote column's collation does not match any
+ locally available collation. This will generally result in not sending
+ <literal>WHERE</literal> clauses involving such a column to the remote
+ server, which may greatly degrade query performance.
</para>
<para>
@@ -537,6 +543,10 @@ OPTIONS (ADD password_required 'false');
need to turn this off if the remote server has a different set of
collation names than the local server does, which is likely to be the
case if it's running on a different operating system.
+ (Note, however, that simply turning this off may result in
+ non-optimal plans for queries on the imported tables. It's best to
+ find a way to label collatable columns with collations that match the
+ remote server's behavior.)
</para>
</listitem>
</varlistentry>
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Ibrar Ahmed
Date:
On Wed, Jul 14, 2021 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ibrar Ahmed <ibrar.ahmad@gmail.com> writes:
> The patch is failing the regression, @Tom Lane <tgl@sss.pgh.pa.us> can you
> please take a look at that.
Seems to just need an update of the expected-file to account for test
cases added recently. (I take no position on whether the new results
are desirable; some of these might be breaking the intent of the case.
But this should quiet the cfbot anyway.)
regards, tom lane
Thanks for the update.
The test case was added by commit "Add support for asynchronous execution"
"27e1f14563cf982f1f4d71e21ef247866662a052" by Etsuro Fujita. He can comment
whether the new results are desirable or not.
--
Ibrar Ahmed
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Thu, Jul 15, 2021 at 4:17 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote: > On Wed, Jul 14, 2021 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Seems to just need an update of the expected-file to account for test >> cases added recently. (I take no position on whether the new results >> are desirable; some of these might be breaking the intent of the case. >> But this should quiet the cfbot anyway.) > The test case was added by commit "Add support for asynchronous execution" > "27e1f14563cf982f1f4d71e21ef247866662a052" by Etsuro Fujita. He can comment > whether the new results are desirable or not. The new results aren't what I intended. I'll update the patch to avoid that by modifying the original test cases properly, if there are no objections. Best regards, Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Ibrar Ahmed
Date:
On Thu, Jul 15, 2021 at 2:35 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Thu, Jul 15, 2021 at 4:17 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
> On Wed, Jul 14, 2021 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Seems to just need an update of the expected-file to account for test
>> cases added recently. (I take no position on whether the new results
>> are desirable; some of these might be breaking the intent of the case.
>> But this should quiet the cfbot anyway.)
> The test case was added by commit "Add support for asynchronous execution"
> "27e1f14563cf982f1f4d71e21ef247866662a052" by Etsuro Fujita. He can comment
> whether the new results are desirable or not.
The new results aren't what I intended. I'll update the patch to
avoid that by modifying the original test cases properly, if there are
no objections.
Best regards,
Etsuro Fujita
I have changed the status to "Waiting On Author", because patch need changes.
Etsuro, can you make yourself a reviewer/co-author to keep track of that?
Ibrar Ahmed
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Thu, Jul 15, 2021 at 4:17 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
>> On Wed, Jul 14, 2021 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Seems to just need an update of the expected-file to account for test
>>> cases added recently. (I take no position on whether the new results
>>> are desirable; some of these might be breaking the intent of the case.
>>> But this should quiet the cfbot anyway.)
>> The test case was added by commit "Add support for asynchronous execution"
>> "27e1f14563cf982f1f4d71e21ef247866662a052" by Etsuro Fujita. He can comment
>> whether the new results are desirable or not.
> The new results aren't what I intended. I'll update the patch to
> avoid that by modifying the original test cases properly, if there are
> no objections.
Please follow up on that sometime? In the meantime, here is a rebase
over aa769f80e and 2dc53fe2a, to placate the cfbot.
The real reason that this hasn't gotten committed is that I remain
pretty uncomfortable about whether it's an acceptable solution to
the problem. Suddenly asking people to plaster COLLATE clauses
on all their textual remote columns seems like a big compatibility
gotcha. However, I lack any ideas about a less unpleasant solution.
regards, tom lane
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd66681..654b09273e 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -328,7 +328,9 @@ foreign_expr_walker(Node *node,
/*
* If the Var is from the foreign table, we consider its
- * collation (if any) safe to use. If it is from another
+ * collation (if any) safe to use, *unless* it's
+ * DEFAULT_COLLATION_OID. We treat that as meaning "we don't
+ * know which collation this is". If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
@@ -350,7 +352,12 @@ foreign_expr_walker(Node *node,
/* Else check the collation */
collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_UNSAFE;
+ else
+ state = FDW_COLLATE_SAFE;
}
else
{
@@ -941,8 +948,24 @@ foreign_expr_walker(Node *node,
/*
* Now, merge my collation information into my parent's state.
+ *
+ * If one branch of an expression derives a non-default collation safely
+ * (that is, from a foreign Var) and another one derives the same
+ * collation unsafely, we can consider the expression safe overall. This
+ * allows cases such as "foreign_var = ('foo' COLLATE x)" where x is the
+ * same collation the foreign_var has anyway. Note that we will not ship
+ * any explicit COLLATE clause to the remote, but rely on it to re-derive
+ * the correct collation based on the foreign_var.
*/
- if (state > outer_cxt->state)
+ if (collation == outer_cxt->collation &&
+ ((state == FDW_COLLATE_UNSAFE &&
+ outer_cxt->state == FDW_COLLATE_SAFE) ||
+ (state == FDW_COLLATE_SAFE &&
+ outer_cxt->state == FDW_COLLATE_UNSAFE)))
+ {
+ outer_cxt->state = FDW_COLLATE_SAFE;
+ }
+ else if (state > outer_cxt->state)
{
/* Override previous parent state */
outer_cxt->collation = collation;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e3ee30f1aa..9e52e09a8b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -32,29 +32,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
-- Disable autovacuum for these tables to avoid unexpected effects of that
@@ -94,16 +94,18 @@ ANALYZE "S 1"."T 3";
ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -111,28 +113,28 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
c1 int NOT NULL,
@@ -288,7 +290,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tabl
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
-> Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
- Sort Key: t1.c3, t1.c1, t1.tableoid
+ Sort Key: t1.c3 COLLATE "C", t1.c1, t1.tableoid
-> Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -902,12 +904,12 @@ EXPLAIN (VERBOSE, COSTS OFF)
(6 rows)
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
QUERY PLAN
-------------------------------------------------------------------------------
Sort
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
- Sort Key: ft2.c1, ft2.c3 COLLATE "C"
+ Sort Key: ft2.c1, ft2.c3 COLLATE "POSIX"
-> Foreign Scan on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
@@ -1149,13 +1151,12 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
-- but this is not because of collation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END;
- QUERY PLAN
--------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Filter: CASE (ft1.c3)::text WHEN ft1.c6 THEN true ELSE (ft1.c3 < 'bar'::text) END
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
-(4 rows)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((CASE c3 WHEN c6 THEN true ELSE (c3 <
'bar'::text)END))
+(3 rows)
-- ===================================================================
-- JOIN queries
@@ -1921,7 +1922,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t
Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1"
=r2."C 1"))))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
- Sort Key: t.c1_3, t.c1_1
+ Sort Key: t.c1_3 COLLATE "C", t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
@@ -2121,7 +2122,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Hash Right Join
Output: t1.c1, t2.c1, t1.c3
Hash Cond: (t2.c1 = t1.c1)
@@ -2163,7 +2164,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
- Sort Key: t1.c3, t1.c1
+ Sort Key: t1.c3 COLLATE "C", t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
@@ -3611,7 +3612,7 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group by grouping sets(c2, c6) orde
----------------------------------------------------------------------------------
Sort
Output: c2, c6, (sum(c1))
- Sort Key: ft1.c2, ft1.c6
+ Sort Key: ft1.c2, ft1.c6 COLLATE "C"
-> HashAggregate
Output: c2, c6, sum(c1)
Hash Key: ft1.c2
@@ -3995,13 +3996,13 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6;
PREPARE st7 AS INSERT INTO ft1 (c1,c2,c3) VALUES (1001,101,'foo');
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 1" RENAME TO "T 0";
@@ -4029,13 +4030,13 @@ EXECUTE st6;
(9 rows)
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft1
Remote SQL: INSERT INTO "S 1"."T 0"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft1 '::character(10), NULL::user_enum
+ Output: NULL::integer, 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft1 '::character(10), NULL::user_enum
(5 rows)
ALTER TABLE "S 1"."T 0" RENAME TO "T 1";
@@ -4266,36 +4267,14 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
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
-------------------------------------------------------------------------------
- Foreign Scan on public.ft3
- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
QUERY PLAN
------------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = '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
@@ -4306,12 +4285,12 @@ explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
-explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
+explain (verbose, costs off) select * from ft3 where f2 = 'foo';
QUERY PLAN
---------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Filter: (ft3.f1 = 'foo'::text COLLATE "C")
+ Filter: (ft3.f2 = 'foo'::text)
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
@@ -4333,6 +4312,33 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(4 rows)
+explain (verbose, costs off) select * from ft3 where f3 = 'foo';
+ QUERY PLAN
+---------------------------------------------------
+ Foreign Scan on public.ft3
+ Output: f1, f2, f3
+ Filter: ((ft3.f3)::text = 'foo'::text)
+ 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 and l.f1 = 'foo';
+ QUERY PLAN
+-------------------------------------------------------------
+ Hash Join
+ Output: f.f1, f.f2, f.f3, l.f1, l.f2, l.f3
+ Inner Unique: true
+ 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)
+(12 rows)
+
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
QUERY PLAN
@@ -4356,13 +4362,13 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-- ===================================================================
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
-
QUERYPLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Subquery Scan on "*SELECT*"
- Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10),
NULL::user_enum
+ Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying COLLATE "C", 'ft2
'::character(10),NULL::user_enum
-> Foreign Scan on public.ft2 ft2_1
Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3)
Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" LIMIT 20::bigint
@@ -5472,14 +5478,14 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on public.ft2
Output: (ft2.tableoid)::regclass
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
Batch Size: 1
-> Result
- Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying, 'ft2 '::character(10), NULL::user_enum
+ Output: 1200, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time
zone,NULL::character varying COLLATE "C", 'ft2 '::character(10), NULL::user_enum
(6 rows)
INSERT INTO ft2 (c1,c2,c3) VALUES (1200,999,'foo') RETURNING tableoid::regclass;
@@ -7881,12 +7887,12 @@ NOTICE: drop cascades to foreign table bar2
drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
insert into remt1 values (1, 'foo');
@@ -8095,10 +8101,10 @@ drop table itrtest;
drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
insert into utrtest values (1, 'foo');
@@ -8260,7 +8266,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -8721,11 +8727,11 @@ tru_rtable_parent,tru_rtable_child, tru_rtable0_child;
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
@@ -8754,7 +8760,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8762,7 +8768,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8786,8 +8792,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -8827,7 +8833,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | | (column_name 'c1')
- c2 | character varying | | not null | | (column_name 'c2')
+ c2 | character varying | C | not null | | (column_name 'c2')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't1')
@@ -8835,7 +8841,7 @@ FDW options: (schema_name 'import_source', table_name 't1')
Column | Type | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+--------------------
c1 | integer | | | 42 | (column_name 'c1')
- c2 | character varying | | | | (column_name 'c2')
+ c2 | character varying | C | | | (column_name 'c2')
c3 | text | POSIX | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 't2')
@@ -8859,8 +8865,8 @@ FDW options: (schema_name 'import_source', table_name 't4')
Column | Type | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+---------------------
c1 | double precision | | | | (column_name 'c1')
- C 2 | text | | | | (column_name 'C 2')
- c3 | character varying(42) | | | | (column_name 'c3')
+ C 2 | text | C | | | (column_name 'C 2')
+ c3 | character varying(42) | C | | | (column_name 'c3')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 4')
@@ -10620,14 +10626,16 @@ SELECT * FROM insert_tbl ORDER BY a;
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b
===505;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Insert on public.join_tbl
CTE t
-> Update on public.remote_tbl
Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
- -> Foreign Update on public.remote_tbl
- Remote SQL: UPDATE public.base_tbl3 SET c = (c || c) RETURNING a, b, c
+ Remote SQL: UPDATE public.base_tbl3 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
+ -> Foreign Scan on public.remote_tbl
+ Output: (remote_tbl.c || remote_tbl.c), remote_tbl.ctid, remote_tbl.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl3 FOR UPDATE
-> Nested Loop Left Join
Output: async_pt.a, async_pt.b, async_pt.c, t.a, t.b, t.c
Join Filter: ((async_pt.a = t.a) AND (async_pt.b = t.b))
@@ -10645,7 +10653,7 @@ INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND
Filter: (async_pt_3.b === 505)
-> CTE Scan on t
Output: t.a, t.b, t.c
-(23 rows)
+(25 rows)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a = t.a AND async_pt.b = t.b) WHERE async_pt.b
===505;
@@ -10673,17 +10681,21 @@ UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
Update on public.async_pt
Output: async_pt_1.a, async_pt_1.b, async_pt_1.c
Foreign Update on public.async_p1 async_pt_1
+ Remote SQL: UPDATE public.base_tbl1 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
Foreign Update on public.async_p2 async_pt_2
+ Remote SQL: UPDATE public.base_tbl2 SET c = $2 WHERE ctid = $1 RETURNING a, b, c
Update on public.async_p3 async_pt_3
-> Append
- -> Foreign Update on public.async_p1 async_pt_1
- Remote SQL: UPDATE public.base_tbl1 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
- -> Foreign Update on public.async_p2 async_pt_2
- Remote SQL: UPDATE public.base_tbl2 SET c = (c || c) WHERE ((b = 0)) RETURNING a, b, c
+ -> Foreign Scan on public.async_p1 async_pt_1
+ Output: (async_pt_1.c || async_pt_1.c), async_pt_1.tableoid, async_pt_1.ctid, async_pt_1.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl1 WHERE ((b = 0)) FOR UPDATE
+ -> Foreign Scan on public.async_p2 async_pt_2
+ Output: (async_pt_2.c || async_pt_2.c), async_pt_2.tableoid, async_pt_2.ctid, async_pt_2.*
+ Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl2 WHERE ((b = 0)) FOR UPDATE
-> Seq Scan on public.async_p3 async_pt_3
Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoid, async_pt_3.ctid, NULL::record
Filter: (async_pt_3.b = 0)
-(13 rows)
+(17 rows)
UPDATE async_pt SET c = c || c WHERE b = 0 RETURNING *;
a | b | c
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4bdab30a73..cacc80585f 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,7 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
@@ -5299,9 +5300,13 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" NULL, ");
if (import_collate)
- appendStringInfoString(&buf,
- " collname, "
- " collnsp.nspname ");
+ appendStringInfo(&buf,
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.collname ELSE coll.collname END, "
+ " CASE WHEN coll.oid = '%u' THEN"
+ " defcoll.nspname ELSE collnsp.nspname END ",
+ DEFAULT_COLLATION_OID,
+ DEFAULT_COLLATION_OID);
else
appendStringInfoString(&buf,
" NULL, NULL ");
@@ -5317,11 +5322,28 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" adrelid = c.oid AND adnum = attnum ");
if (import_collate)
+ {
appendStringInfoString(&buf,
" LEFT JOIN pg_collation coll ON "
" coll.oid = attcollation "
" LEFT JOIN pg_namespace collnsp ON "
- " collnsp.oid = collnamespace ");
+ " collnsp.oid = collnamespace "
+ " LEFT JOIN ("
+ " SELECT cd.collname, nd.nspname FROM"
+ " pg_collation cd, pg_namespace nd, pg_database d"
+ " WHERE nd.oid = cd.collnamespace AND"
+ " d.datname = current_database() AND");
+ /* collprovider is new as of v10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf,
+ " cd.collprovider = 'c' AND");
+ appendStringInfoString(&buf,
+ " cd.collcollate = d.datcollate AND"
+ " cd.collctype = d.datctype AND"
+ " cd.collencoding IN (d.encoding, -1)"
+ " ORDER BY length(cd.collname) LIMIT 1 )"
+ " defcoll ON TRUE ");
+ }
appendStringInfoString(&buf,
"WHERE c.relkind IN ("
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 30b5175da5..a96898ed6b 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -36,29 +36,29 @@ CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
"C 1" int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10),
+ c6 varchar(10) collate "C",
+ c7 char(10) collate "C",
c8 user_enum,
CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
c1 int NOT NULL,
- c2 text,
+ c2 text collate "C",
CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 3" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t3_pkey PRIMARY KEY (c1)
);
CREATE TABLE "S 1"."T 4" (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
@@ -102,16 +102,18 @@ ANALYZE "S 1"."T 4";
-- ===================================================================
-- create foreign tables
+-- Note: to ensure stable regression results, all collatable columns
+-- in these tables must have explicitly-specified collations.
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
c0 int,
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft1',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft1' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
@@ -120,11 +122,11 @@ CREATE FOREIGN TABLE ft2 (
c1 int NOT NULL,
c2 int NOT NULL,
cx int,
- c3 text,
+ c3 text collate "C",
c4 timestamptz,
c5 timestamp,
- c6 varchar(10),
- c7 char(10) default 'ft2',
+ c6 varchar(10) collate "C",
+ c7 char(10) default 'ft2' collate "C",
c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
@@ -132,19 +134,19 @@ ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
CREATE FOREIGN TABLE ft4 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
CREATE FOREIGN TABLE ft5 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft6 (
c1 int NOT NULL,
c2 int NOT NULL,
- c3 text
+ c3 text collate "C"
) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4');
CREATE FOREIGN TABLE ft7 (
@@ -354,7 +356,7 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 ORDER BY ft2.c1, random();
EXPLAIN (VERBOSE, COSTS OFF)
- SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
+ SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "POSIX";
-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
@@ -1194,15 +1196,15 @@ create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
-- 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';
+explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
-- 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 = 'foo';
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 where f3 = 'foo';
+explain (verbose, costs off) select * from ft3 f, loct3 l
+ where f.f3 = l.f3 and l.f1 = 'foo';
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
@@ -2065,12 +2067,12 @@ drop table loct1;
drop table loct2;
-- Test pushing down UPDATE/DELETE joins to the remote server
-create table parent (a int, b text);
-create table loct1 (a int, b text);
-create table loct2 (a int, b text);
-create foreign table remt1 (a int, b text)
+create table parent (a int, b text collate "C");
+create table loct1 (a int, b text collate "C");
+create table loct2 (a int, b text collate "C");
+create foreign table remt1 (a int, b text collate "C")
server loopback options (table_name 'loct1');
-create foreign table remt2 (a int, b text)
+create foreign table remt2 (a int, b text collate "C")
server loopback options (table_name 'loct2');
alter foreign table remt1 inherit parent;
@@ -2163,10 +2165,10 @@ drop table loct1;
drop table loct2;
-- Test update tuple routing
-create table utrtest (a int, b text) partition by list (a);
-create table loct (a int check (a in (1)), b text);
-create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
-create table locp (a int check (a in (2)), b text);
+create table utrtest (a int, b text collate "C") partition by list (a);
+create table loct (a int check (a in (1)), b text collate "C");
+create foreign table remp (a int check (a in (1)), b text collate "C") server loopback options (table_name 'loct');
+create table locp (a int check (a in (2)), b text collate "C");
alter table utrtest attach partition remp for values in (1);
alter table utrtest attach partition locp for values in (2);
@@ -2243,7 +2245,7 @@ alter table utrtest detach partition remp;
drop foreign table remp;
alter table loct drop constraint loct_a_check;
alter table loct add check (a in (3));
-create foreign table remp (a int check (a in (3)), b text) server loopback options (table_name 'loct');
+create foreign table remp (a int check (a in (3)), b text collate "C") server loopback options (table_name 'loct');
alter table utrtest attach partition remp for values in (3);
insert into utrtest values (2, 'qux');
insert into utrtest values (3, 'xyzzy');
@@ -2553,11 +2555,11 @@ tru_rtable_parent,tru_rtable_child, tru_rtable0_child;
-- ===================================================================
CREATE SCHEMA import_source;
-CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
-CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
-CREATE TYPE typ1 AS (m1 int, m2 varchar);
+CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL COLLATE "C");
+CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL COLLATE "C", c3 text collate "POSIX");
+CREATE TYPE typ1 AS (m1 int, m2 varchar COLLATE "C");
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
-CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+CREATE TABLE import_source."x 4" (c1 float8, "C 2" text COLLATE "C", c3 varchar(42) COLLATE "C");
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 0075bc3dbb..fc368db47a 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -97,6 +97,12 @@
need, surprising semantic anomalies may arise when types or collations do
not match, due to the remote server interpreting <literal>WHERE</literal> clauses
slightly differently from the local server.
+ Also, while you need not apply any explicit <literal>COLLATE</literal>
+ clause to a collatable column of a foreign table, not doing so will cause
+ the planner to assume that the remote column's collation does not match any
+ locally available collation. This will generally result in not sending
+ <literal>WHERE</literal> clauses involving such a column to the remote
+ server, which may greatly degrade query performance.
</para>
<para>
@@ -537,6 +543,10 @@ OPTIONS (ADD password_required 'false');
need to turn this off if the remote server has a different set of
collation names than the local server does, which is likely to be the
case if it's running on a different operating system.
+ (Note, however, that simply turning this off may result in
+ non-optimal plans for queries on the imported tables. It's best to
+ find a way to label collatable columns with collations that match the
+ remote server's behavior.)
</para>
</listitem>
</varlistentry>
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Thu, Sep 2, 2021 at 5:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Etsuro Fujita <etsuro.fujita@gmail.com> writes: > > On Thu, Jul 15, 2021 at 4:17 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote: > >> On Wed, Jul 14, 2021 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > >>> Seems to just need an update of the expected-file to account for test > >>> cases added recently. (I take no position on whether the new results > >>> are desirable; some of these might be breaking the intent of the case. > >>> But this should quiet the cfbot anyway.) > > >> The test case was added by commit "Add support for asynchronous execution" > >> "27e1f14563cf982f1f4d71e21ef247866662a052" by Etsuro Fujita. He can comment > >> whether the new results are desirable or not. > > > The new results aren't what I intended. I'll update the patch to > > avoid that by modifying the original test cases properly, if there are > > no objections. > > Please follow up on that sometime? Will do in this commitfest. > In the meantime, here is a rebase > over aa769f80e and 2dc53fe2a, to placate the cfbot. Thanks for the rebase! Best regards, Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Thu, Sep 2, 2021 at 5:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The real reason that this hasn't gotten committed is that I remain
> pretty uncomfortable about whether it's an acceptable solution to
> the problem. Suddenly asking people to plaster COLLATE clauses
> on all their textual remote columns seems like a big compatibility
> gotcha.
I think so too. I reviewed the patch:
/*
* If the Var is from the foreign table, we consider its
- * collation (if any) safe to use. If it is from another
+ * collation (if any) safe to use, *unless* it's
+ * DEFAULT_COLLATION_OID. We treat that as meaning "we don't
+ * know which collation this is". If it is from another
* table, we treat its collation the same way as we would a
* Param's collation, ie it's not safe for it to have a
* non-default collation.
@@ -350,7 +352,12 @@ foreign_expr_walker(Node *node,
/* Else check the collation */
collation = var->varcollid;
- state = OidIsValid(collation) ? FDW_COLLATE_SAFE :
FDW_COLLATE_NONE;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_UNSAFE;
+ else
+ state = FDW_COLLATE_SAFE;
One thing I noticed about this change is:
explain (verbose, costs off) select * from ft3 order by f2;
QUERY PLAN
---------------------------------------------------------
Sort
Output: f1, f2, f3
Sort Key: ft3.f2
-> Foreign Scan on public.ft3
Output: f1, f2, f3
Remote SQL: SELECT f1, f2, f3 FROM public.loct3
(6 rows)
where ft3 is defined as in the postgres_fdw regression test (see the
section “test handling of collations”). For this query, the sort is
done locally, but I think it should be done remotely, or an error
should be raised, as we don’t know the collation assigned to the
column “f2”. So I think we need to do something about this.
Having said that, I think another option for this would be to left the
code as-is; assume that 1) the foreign var has "COLLATE default”, not
an unknown collation, when labeled with "COLLATE default”, and 2)
"COLLATE default” on the local database matches "COLLATE default” on
the remote database. This would be the same as before, so we could
avoid the concern mentioned above. I agree with the
postgresImportForeignSchema() change, except creating a local column
with "COLLATE default" silently if that function can’t find a remote
collation matching the database's datcollate/datctype when seeing
"COLLATE default”, in which case I think an error should be raised to
prompt the user to check the settings for the remote server and/or
define foreign tables manually with collations that match the remote
side. Maybe I’m missing something, though.
Anyway, here is a patch created on top of your patch to modify
async-related test cases to work as intended. I’m also attaching your
patch to make the cfbot quiet.
Sorry for the delay.
Best regards,
Etsuro Fujita
Attachment
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Thu, Sep 2, 2021 at 5:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The real reason that this hasn't gotten committed is that I remain
>> pretty uncomfortable about whether it's an acceptable solution to
>> the problem. Suddenly asking people to plaster COLLATE clauses
>> on all their textual remote columns seems like a big compatibility
>> gotcha.
> I think so too.
Yeah :-(. It seems like a very unpleasant change.
> Having said that, I think another option for this would be to left the
> code as-is; assume that 1) the foreign var has "COLLATE default”, not
> an unknown collation, when labeled with "COLLATE default”, and 2)
> "COLLATE default” on the local database matches "COLLATE default” on
> the remote database.
The fundamental complaint that started this thread was exactly that
assumption (2) isn't safe. So it sounds to me like you're proposing
that we do nothing, which isn't a great answer either. I suppose
we could try documenting our way out of this, but people will
continue to get bit because they won't read or won't understand
the limitation.
I'd be happier if we had a way to check whether the local and remote
default collations are compatible. But it seems like that's a big ask,
especially in cross-operating-system situations.
regards, tom lane
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Fri, Sep 10, 2021 at 1:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Etsuro Fujita <etsuro.fujita@gmail.com> writes: > > Having said that, I think another option for this would be to left the > > code as-is; assume that 1) the foreign var has "COLLATE default”, not > > an unknown collation, when labeled with "COLLATE default”, and 2) > > "COLLATE default” on the local database matches "COLLATE default” on > > the remote database. > > The fundamental complaint that started this thread was exactly that > assumption (2) isn't safe. So it sounds to me like you're proposing > that we do nothing, which isn't a great answer either. I suppose > we could try documenting our way out of this, but people will > continue to get bit because they won't read or won't understand > the limitation. Yeah, but I think it’s the user’s responsibility to make sure that the local and remote default collations match if labeling collatable columns with “COLLATE default” when defining foreign tables manually IMO. > I'd be happier if we had a way to check whether the local and remote > default collations are compatible. But it seems like that's a big ask, > especially in cross-operating-system situations. Agreed. Best regards, Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Fri, Sep 10, 2021 at 8:42 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Fri, Sep 10, 2021 at 1:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> > > Having said that, I think another option for this would be to left the
> > > code as-is; assume that 1) the foreign var has "COLLATE default”, not
> > > an unknown collation, when labeled with "COLLATE default”, and 2)
> > > "COLLATE default” on the local database matches "COLLATE default” on
> > > the remote database.
> >
> > The fundamental complaint that started this thread was exactly that
> > assumption (2) isn't safe. So it sounds to me like you're proposing
> > that we do nothing, which isn't a great answer either. I suppose
> > we could try documenting our way out of this, but people will
> > continue to get bit because they won't read or won't understand
> > the limitation.
>
> Yeah, but I think it’s the user’s responsibility to make sure that the
> local and remote default collations match if labeling collatable
> columns with “COLLATE default” when defining foreign tables manually
> IMO.
One thing I noticed is that collatable operators/functions sent to the
remote might also cause an unexpected result when the default
collations are not compatible. Consider this example (even with your
patch):
explain verbose select chr(c1) from ft1 order by chr(c1);
QUERY PLAN
------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..212.91 rows=2925 width=32)
Output: chr(c1)
Remote SQL: SELECT c1 FROM public.t1 ORDER BY chr(c1) ASC NULLS LAST
(3 rows)
where ft1 is a foreign table with an integer column c1. As shown
above, the sort using the collatable function chr() is performed
remotely, so the select query might produce the result in an
unexpected sort order when the default collations are not compatible.
ISTM that we rely heavily on assumption (2).
Best regards,
Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> One thing I noticed is that collatable operators/functions sent to the
> remote might also cause an unexpected result when the default
> collations are not compatible. Consider this example (even with your
> patch):
> ...
> where ft1 is a foreign table with an integer column c1. As shown
> above, the sort using the collatable function chr() is performed
> remotely, so the select query might produce the result in an
> unexpected sort order when the default collations are not compatible.
I don't think there's anything really new there --- it's still assuming
that COLLATE "default" means the same locally and remotely.
As a short-term answer, I propose that we apply (and back-patch) the
attached documentation changes.
Longer-term, it seems like we really have to be able to represent
the notion of a remote column that has an "unknown" collation (that
is, one that doesn't match any local collation, or at least is not
known to do so). My previous patch essentially makes "default" act
that way, but conflating "unknown" with "default" has too many
downsides. A rough sketch for making this happen is:
1. Create a built-in "unknown" entry in pg_collation. Insert some
hack or other to prevent this from being applied to any real, local
column; but allow foreign-table columns to have it.
2. Apply mods, probably fairly similar to my patch, that prevent
postgres_fdw from believing that "unknown" matches any local
collation. (Hm, actually maybe no special code change will be
needed here, once "unknown" has its own OID?)
3. Change postgresImportForeignSchema so that it can substitute
the "unknown" collation at need. The exact rules for this could
be debated depending on whether you'd rather prioritize safety or
ease-of-use, but I think at least we should use "unknown" whenever
import_collate is turned off. Perhaps there should be an option
to substitute it for remote "default" as well. (Further down the
road, perhaps that could be generalized to allow a user-controlled
mapping from remote to local collations.)
Anyway, I think I should withdraw the upthread patch; we don't
want to go that way.
regards, tom lane
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index bf95da9721..dbc11694a0 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -95,8 +95,8 @@
referenced columns of the remote table. Although <filename>postgres_fdw</filename>
is currently rather forgiving about performing data type conversions at
need, surprising semantic anomalies may arise when types or collations do
- not match, due to the remote server interpreting <literal>WHERE</literal> clauses
- slightly differently from the local server.
+ not match, due to the remote server interpreting query conditions
+ differently from the local server.
</para>
<para>
@@ -537,6 +537,17 @@ OPTIONS (ADD password_required 'false');
need to turn this off if the remote server has a different set of
collation names than the local server does, which is likely to be the
case if it's running on a different operating system.
+ If you do so, however, there is a very severe risk that the imported
+ table columns' collations will not match the underlying data, resulting
+ in anomalous query behavior.
+ </para>
+
+ <para>
+ Even when this parameter is set to <literal>true</literal>, importing
+ columns whose collation is the remote server's default can be risky.
+ They will be imported with <literal>COLLATE "default"</literal>, which
+ will select the local server's default collation, which could be
+ different.
</para>
</listitem>
</varlistentry>
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Etsuro Fujita <etsuro.fujita@gmail.com> writes: > > One thing I noticed is that collatable operators/functions sent to the > > remote might also cause an unexpected result when the default > > collations are not compatible. Consider this example (even with your > > patch): > > ... > > where ft1 is a foreign table with an integer column c1. As shown > > above, the sort using the collatable function chr() is performed > > remotely, so the select query might produce the result in an > > unexpected sort order when the default collations are not compatible. > > I don't think there's anything really new there --- it's still assuming > that COLLATE "default" means the same locally and remotely. I thought that the example showed that we would need to specify a collation per-operation, not only per-foreign-table-column, like “ORDER BY chr(c1) COLLATE “foo”” where “foo” is the actual name of a local collation matching the local server’s default collation, when the default collation doesn’t match the remote server’s default collation, to avoid pushing down operations incorrectly as in the example. > As a short-term answer, I propose that we apply (and back-patch) the > attached documentation changes. The attached patch looks good to me. > Longer-term, it seems like we really have to be able to represent > the notion of a remote column that has an "unknown" collation (that > is, one that doesn't match any local collation, or at least is not > known to do so). +1 > A rough sketch for making this happen is: > > 1. Create a built-in "unknown" entry in pg_collation. Insert some > hack or other to prevent this from being applied to any real, local > column; but allow foreign-table columns to have it. > > 2. Apply mods, probably fairly similar to my patch, that prevent > postgres_fdw from believing that "unknown" matches any local > collation. (Hm, actually maybe no special code change will be > needed here, once "unknown" has its own OID?) > > 3. Change postgresImportForeignSchema so that it can substitute > the "unknown" collation at need. The exact rules for this could > be debated depending on whether you'd rather prioritize safety or > ease-of-use, but I think at least we should use "unknown" whenever > import_collate is turned off. Perhaps there should be an option > to substitute it for remote "default" as well. (Further down the > road, perhaps that could be generalized to allow a user-controlled > mapping from remote to local collations.) In addition, a) we should detect whether local “default” matches remote “default”, and b) if not, we should prevent pushing down sort/comparison operations using collatable functions/operators like “ORDER BY chr(c1)” in the example (and pushing down those operations on foreign-table columns labeled with “COLLATE default” if such labeling is allowed)? Best regards, Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Longer-term, it seems like we really have to be able to represent
>> the notion of a remote column that has an "unknown" collation (that
>> is, one that doesn't match any local collation, or at least is not
>> known to do so).
> +1
> In addition, a) we should detect whether local “default” matches
> remote “default”,
If we had a way to do that, most of the problem here wouldn't exist.
I don't believe we can do it reliably. (Maybe we could put it on
the user to tell us, say via a foreign-server property?)
regards, tom lane
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Tom Lane
Date:
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> As a short-term answer, I propose that we apply (and back-patch) the
>> attached documentation changes.
> The attached patch looks good to me.
I've pushed that, and marked the current CF entry as returned with
feedback. I'm not sure how soon I might get around to trying the
idea of an explicit "unknown" collation ... if anyone wants to take
a stab at that, feel free.
regards, tom lane
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Etsuro Fujita
Date:
On Sat, Sep 25, 2021 at 10:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Etsuro Fujita <etsuro.fujita@gmail.com> writes: > > On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Longer-term, it seems like we really have to be able to represent > >> the notion of a remote column that has an "unknown" collation (that > >> is, one that doesn't match any local collation, or at least is not > >> known to do so). > > > +1 > > > In addition, a) we should detect whether local “default” matches > > remote “default”, > > If we had a way to do that, most of the problem here wouldn't exist. > I don't believe we can do it reliably. (Maybe we could put it on > the user to tell us, say via a foreign-server property?) Yeah, I was thinking we could get it from a server option. Also, I was thinking this bit might be back-patchable independently of the solution mentioned above. Best regards, Etsuro Fujita
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
From
Jeremy Schneider
Date:
On 9/25/21 06:59, Tom Lane wrote: > Etsuro Fujita <etsuro.fujita@gmail.com> writes: >> On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Longer-term, it seems like we really have to be able to represent >>> the notion of a remote column that has an "unknown" collation (that >>> is, one that doesn't match any local collation, or at least is not >>> known to do so). > >> +1 > >> In addition, a) we should detect whether local “default” matches >> remote “default”, > > If we had a way to do that, most of the problem here wouldn't exist. > I don't believe we can do it reliably. (Maybe we could put it on > the user to tell us, say via a foreign-server property?) A related situation is local and remote servers having different versions of glibc - in particular, pre versus post 2.28. I think there's still a major brewing storm here that hasn't yet fully hit the world of PG users. I know PG throws the warning message for queries using the wrong collation library version, but I can't remember - does the query still execute? If so, then glibc 2.28 seems to significnatly raise the likelihood of wrong query results across the entire global PG install base. Does PostgreSQL handle cases which involve FDWs (ala this thread) or hot standbys? Would be nice if some approach could be found to solve that problem at the same time as the one discussed on this thread. -Jeremy -- Jeremy Schneider Database Engineer Amazon Web Services