Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mLX4T-0006xY-JC for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Sep 2021 20:42:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mLX4S-0002zf-CH for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Sep 2021 20:42:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mLX4R-0002uC-NF for pgsql-hackers@lists.postgresql.org; Wed, 01 Sep 2021 20:42:56 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mLX4O-00019m-4p for pgsql-hackers@lists.postgresql.org; Wed, 01 Sep 2021 20:42:54 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 181KgnAn2201881; Wed, 1 Sep 2021 16:42:49 -0400 From: Tom Lane To: Etsuro Fujita cc: Ibrar Ahmed , PostgreSQL Hackers , Neil Chen Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails In-reply-to: References: <16583-4dc8f6c3b8ed24fa@postgresql.org> <422379.1612684550@sss.pgh.pa.us> <161476090989.29967.7097745522340560681.pgcf@coridan.postgresql.org> <3404441.1626208861@sss.pgh.pa.us> Comments: In-reply-to Etsuro Fujita message dated "Thu, 15 Jul 2021 18:35:33 +0900" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2201828.1630528910.0@sss.pgh.pa.us> Date: Wed, 01 Sep 2021 16:42:49 -0400 Message-ID: <2201880.1630528969@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2201828.1630528910.1@sss.pgh.pa.us> Etsuro Fujita writes: > On Thu, Jul 15, 2021 at 4:17 AM Ibrar Ahmed wrote: >> On Wed, Jul 14, 2021 at 1:41 AM Tom Lane 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="fix-postgres-fdw-collation-handling-4.patch"; charset="us-ascii" Content-ID: <2201828.1630528910.2@sss.pgh.pa.us> Content-Description: fix-postgres-fdw-collation-handling-4.patch Content-Transfer-Encoding: quoted-printable 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 =3D var->varcollid; - state =3D OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NON= E; + if (collation =3D=3D InvalidOid) + state =3D FDW_COLLATE_NONE; + else if (collation =3D=3D DEFAULT_COLLATION_OID) + state =3D FDW_COLLATE_UNSAFE; + else + state =3D 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. Thi= s + * allows cases such as "foreign_var =3D ('foo' COLLATE x)" where x is t= he + * same collation the foreign_var has anyway. Note that we will not shi= p + * any explicit COLLATE clause to the remote, but rely on it to re-deriv= e + * the correct collation based on the foreign_var. */ - if (state > outer_cxt->state) + if (collation =3D=3D outer_cxt->collation && + ((state =3D=3D FDW_COLLATE_UNSAFE && + outer_cxt->state =3D=3D FDW_COLLATE_SAFE) || + (state =3D=3D FDW_COLLATE_SAFE && + outer_cxt->state =3D=3D FDW_COLLATE_UNSAFE))) + { + outer_cxt->state =3D FDW_COLLATE_SAFE; + } + else if (state > outer_cxt->state) { /* Override previous parent state */ outer_cxt->collation =3D collation; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/post= gres_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 tha= t @@ -94,16 +94,18 @@ ANALYZE "S 1"."T 3"; ANALYZE "S 1"."T 4"; -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- create foreign tables +-- Note: to ensure stable regression results, all collatable columns +-- in these tables must have explicitly-specified collations. -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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 ORDE= R 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) = -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- 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" =3D 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 =3D 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 =3D t1.c1) @@ -2163,7 +2164,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c= 1 =3D t2.c1) WHERE t1.c8 =3D 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 =3D t2.c8) @@ -3611,7 +3612,7 @@ select c2, c6, sum(c1) from ft1 where c2 < 3 group b= y 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 w= ith 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 w= ith 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 w= ith 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 w= ith 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 whe= re f1 COLLATE "C" =3D 'foo'; Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 =3D 'foo'::= text)) (3 rows) = -explain (verbose, costs off) select * from ft3 where f2 =3D 'foo'; - QUERY PLAN = = --------------------------------------------------------------------------= ----- - Foreign Scan on public.ft3 - Output: f1, f2, f3 - Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f2 =3D 'foo'::= text)) -(3 rows) - -explain (verbose, costs off) select * from ft3 where f3 =3D 'foo'; +explain (verbose, costs off) select * from ft3 where f1 =3D '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 =3D 'foo'::= text)) + Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 =3D 'foo'::= text)) (3 rows) = -explain (verbose, costs off) select * from ft3 f, loct3 l - where f.f3 =3D l.f3 and l.f1 =3D '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 =3D '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::char= acter varying(10) =3D f3)) -(8 rows) - -- can't be sent to remote explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" =3D= 'foo'; QUERY PLAN = @@ -4306,12 +4285,12 @@ explain (verbose, costs off) select * from ft3 whe= re f1 COLLATE "POSIX" =3D 'foo'; Remote SQL: SELECT f1, f2, f3 FROM public.loct3 (4 rows) = -explain (verbose, costs off) select * from ft3 where f1 =3D 'foo' COLLATE= "C"; +explain (verbose, costs off) select * from ft3 where f2 =3D 'foo'; QUERY PLAN = --------------------------------------------------- Foreign Scan on public.ft3 Output: f1, f2, f3 - Filter: (ft3.f1 =3D 'foo'::text COLLATE "C") + Filter: (ft3.f2 =3D 'foo'::text) Remote SQL: SELECT f1, f2, f3 FROM public.loct3 (4 rows) = @@ -4333,6 +4312,33 @@ explain (verbose, costs off) select * from ft3 wher= e f2 =3D 'foo' COLLATE "C"; Remote SQL: SELECT f1, f2, f3 FROM public.loct3 (4 rows) = +explain (verbose, costs off) select * from ft3 where f3 =3D 'foo'; + QUERY PLAN = +--------------------------------------------------- + Foreign Scan on public.ft3 + Output: f1, f2, f3 + Filter: ((ft3.f3)::text =3D '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 =3D l.f3 and l.f1 =3D '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 =3D (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 =3D 'foo'::text) +(12 rows) + explain (verbose, costs off) select * from ft3 f, loct3 l where f.f3 =3D l.f3 COLLATE "POSIX" and l.f1 =3D 'foo'; QUERY PLAN = @@ -4356,13 +4362,13 @@ explain (verbose, costs off) select * from ft3 f, = loct3 l -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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) Batch Size: 1 -> Subquery Scan on "*SELECT*" - Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::in= teger, "*SELECT*"."?column?_2", NULL::timestamp with time zone, NULL::time= stamp without time zone, NULL::character varying, 'ft2 '::character(= 10), NULL::user_enum + Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::in= teger, "*SELECT*"."?column?_2", NULL::timestamp with time zone, NULL::time= stamp 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::re= gclass; - = 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 w= ith 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 w= ith 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::re= gclass; @@ -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 loopbac= k 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") se= rver 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 loopbac= k options (table_name 'loct'); +create foreign table remp (a int check (a in (3)), b text collate "C") se= rver 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_ch= ild; -- test IMPORT FOREIGN SCHEMA -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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 tex= t 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 v= archar(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 loop= back INTO import_dest1; Column | Type | Collation | Nullable | Default | FDW opt= ions = --------+-------------------+-----------+----------+---------+-----------= --------- c1 | integer | | | | (column_na= me 'c1') - c2 | character varying | | not null | | (column_na= me 'c2') + c2 | character varying | C | not null | | (column_na= me 'c2') Server: loopback FDW options: (schema_name 'import_source', table_name 't1') = @@ -8762,7 +8768,7 @@ FDW options: (schema_name 'import_source', table_nam= e 't1') Column | Type | Collation | Nullable | Default | FDW opt= ions = --------+-------------------+-----------+----------+---------+-----------= --------- c1 | integer | | | | (column_na= me 'c1') - c2 | character varying | | | | (column_na= me 'c2') + c2 | character varying | C | | | (column_na= me 'c2') c3 | text | POSIX | | | (column_na= me 'c3') Server: loopback FDW options: (schema_name 'import_source', table_name 't2') @@ -8786,8 +8792,8 @@ FDW options: (schema_name 'import_source', table_nam= e 't4') Column | Type | Collation | Nullable | Default | FD= W options = --------+-----------------------+-----------+----------+---------+-------= -------------- c1 | double precision | | | | (colum= n_name 'c1') - C 2 | text | | | | (colum= n_name 'C 2') - c3 | character varying(42) | | | | (colum= n_name 'c3') + C 2 | text | C | | | (colum= n_name 'C 2') + c3 | character varying(42) | C | | | (colum= n_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 loop= back INTO import_dest2 Column | Type | Collation | Nullable | Default | FDW opt= ions = --------+-------------------+-----------+----------+---------+-----------= --------- c1 | integer | | | | (column_na= me 'c1') - c2 | character varying | | not null | | (column_na= me 'c2') + c2 | character varying | C | not null | | (column_na= me 'c2') Server: loopback FDW options: (schema_name 'import_source', table_name 't1') = @@ -8835,7 +8841,7 @@ FDW options: (schema_name 'import_source', table_nam= e 't1') Column | Type | Collation | Nullable | Default | FDW opt= ions = --------+-------------------+-----------+----------+---------+-----------= --------- c1 | integer | | | 42 | (column_na= me 'c1') - c2 | character varying | | | | (column_na= me 'c2') + c2 | character varying | C | | | (column_na= me 'c2') c3 | text | POSIX | | | (column_na= me 'c3') Server: loopback FDW options: (schema_name 'import_source', table_name 't2') @@ -8859,8 +8865,8 @@ FDW options: (schema_name 'import_source', table_nam= e 't4') Column | Type | Collation | Nullable | Default | FD= W options = --------+-----------------------+-----------+----------+---------+-------= -------------- c1 | double precision | | | | (colum= n_name 'c1') - C 2 | text | | | | (colum= n_name 'C 2') - c3 | character varying(42) | | | | (colum= n_name 'c3') + C 2 | text | C | | | (colum= n_name 'C 2') + c3 | character varying(42) | C | | | (colum= n_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 =3D c || c RETURNING *) INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a =3D= t.a AND async_pt.b =3D t.b) WHERE async_pt.b =3D=3D=3D 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 =3D (c || c) R= ETURNING a, b, c + Remote SQL: UPDATE public.base_tbl3 SET c =3D $2 WHERE ctid =3D= $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 F= OR 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 =3D t.a) AND (async_pt.b =3D t.b)) @@ -10645,7 +10653,7 @@ INSERT INTO join_tbl SELECT * FROM async_pt LEFT J= OIN t ON (async_pt.a =3D t.a AND Filter: (async_pt_3.b =3D=3D=3D 505) -> CTE Scan on t Output: t.a, t.b, t.c -(23 rows) +(25 rows) = WITH t AS (UPDATE remote_tbl SET c =3D c || c RETURNING *) INSERT INTO join_tbl SELECT * FROM async_pt LEFT JOIN t ON (async_pt.a =3D= t.a AND async_pt.b =3D t.b) WHERE async_pt.b =3D=3D=3D 505; @@ -10673,17 +10681,21 @@ UPDATE async_pt SET c =3D c || c WHERE b =3D 0 R= ETURNING *; 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 =3D $2 WHERE ctid =3D $1 R= ETURNING a, b, c Foreign Update on public.async_p2 async_pt_2 + Remote SQL: UPDATE public.base_tbl2 SET c =3D $2 WHERE ctid =3D $1 R= ETURNING 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 =3D (c || c) WHE= RE ((b =3D 0)) RETURNING a, b, c - -> Foreign Update on public.async_p2 async_pt_2 - Remote SQL: UPDATE public.base_tbl2 SET c =3D (c || c) WHE= RE ((b =3D 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.tableoi= d, async_pt_1.ctid, async_pt_1.* + Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl1 WHE= RE ((b =3D 0)) FOR UPDATE + -> Foreign Scan on public.async_p2 async_pt_2 + Output: (async_pt_2.c || async_pt_2.c), async_pt_2.tableoi= d, async_pt_2.ctid, async_pt_2.* + Remote SQL: SELECT a, b, c, ctid FROM public.base_tbl2 WHE= RE ((b =3D 0)) FOR UPDATE -> Seq Scan on public.async_p3 async_pt_3 Output: (async_pt_3.c || async_pt_3.c), async_pt_3.tableoi= d, async_pt_3.ctid, NULL::record Filter: (async_pt_3.b =3D 0) -(13 rows) +(17 rows) = UPDATE async_pt SET c =3D c || c WHERE b =3D 0 RETURNING *; a | b | c = diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/po= stgres_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 =3D '%u' THEN" + " defcoll.collname ELSE coll.collname END, " + " CASE WHEN coll.oid =3D '%u' THEN" + " defcoll.nspname ELSE collnsp.nspname END ", + DEFAULT_COLLATION_OID, + DEFAULT_COLLATION_OID); else appendStringInfoString(&buf, " NULL, NULL "); @@ -5317,11 +5322,28 @@ postgresImportForeignSchema(ImportForeignSchemaStm= t *stmt, Oid serverOid) " adrelid =3D c.oid AND adnum =3D attnum "); = if (import_collate) + { appendStringInfoString(&buf, " LEFT JOIN pg_collation coll ON " " coll.oid =3D attcollation " " LEFT JOIN pg_namespace collnsp ON " - " collnsp.oid =3D collnamespace "); + " collnsp.oid =3D collnamespace " + " LEFT JOIN (" + " SELECT cd.collname, nd.nspname FROM" + " pg_collation cd, pg_namespace nd, pg_database d" + " WHERE nd.oid =3D cd.collnamespace AND" + " d.datname =3D current_database() AND"); + /* collprovider is new as of v10 */ + if (PQserverVersion(conn) >=3D 100000) + appendStringInfoString(&buf, + " cd.collprovider =3D 'c' AND"); + appendStringInfoString(&buf, + " cd.collcollate =3D d.datcollate AND" + " cd.collctype =3D 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"; = -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- create foreign tables +-- Note: to ensure stable regression results, all collatable columns +-- in these tables must have explicitly-specified collations. -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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 =3D ANY (ARRAY(SELECT c1 FR= OM 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 =3D 'foo'; explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" =3D '= foo'; -explain (verbose, costs off) select * from ft3 where f2 =3D 'foo'; -explain (verbose, costs off) select * from ft3 where f3 =3D 'foo'; -explain (verbose, costs off) select * from ft3 f, loct3 l - where f.f3 =3D l.f3 and l.f1 =3D 'foo'; +explain (verbose, costs off) select * from ft3 where f1 =3D 'foo' COLLATE= "C"; -- can't be sent to remote explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" =3D= 'foo'; -explain (verbose, costs off) select * from ft3 where f1 =3D 'foo' COLLATE= "C"; +explain (verbose, costs off) select * from ft3 where f2 =3D 'foo'; explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" =3D '= foo'; explain (verbose, costs off) select * from ft3 where f2 =3D 'foo' COLLATE= "C"; +explain (verbose, costs off) select * from ft3 where f3 =3D 'foo'; +explain (verbose, costs off) select * from ft3 f, loct3 l + where f.f3 =3D l.f3 and l.f1 =3D 'foo'; explain (verbose, costs off) select * from ft3 f, loct3 l where f.f3 =3D l.f3 COLLATE "POSIX" and l.f1 =3D '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 loopbac= k 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") se= rver 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 loopbac= k options (table_name 'loct'); +create foreign table remp (a int check (a in (3)), b text collate "C") se= rver 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_ch= ild; -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = 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 tex= t 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 v= archar(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.sg= ml 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 WHERE clauses slightly differently from the local server. + Also, while you need not apply any explicit COLLATE + clause to a collatable column of a foreign table, not doing so will cau= se + the planner to assume that the remote column's collation does not match= any + locally available collation. This will generally result in not sending + WHERE clauses involving such a column to the remote + server, which may greatly degrade query performance. = @@ -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 t= o + find a way to label collatable columns with collations that match = the + remote server's behavior.) ------- =_aaaaaaaaaa0--