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; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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::timestamp with 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::timestamp with 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>