Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails |
| Date | |
| Msg-id | 3404441.1626208861@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails (Ibrar Ahmed <ibrar.ahmad@gmail.com>) |
| Responses |
Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
(Ibrar Ahmed <ibrar.ahmad@gmail.com>)
|
| List | pgsql-hackers |
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>
pgsql-hackers by date: