From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ibrar Ahmed <ibrar.ahmad@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Neil Chen <carpenter.nail.cz@gmail.com>
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Date: Tue, 13 Jul 2021 16:41:01 -0400
Message-ID: <3404441.1626208861@sss.pgh.pa.us> (raw)
In-Reply-To: <CALtqXTdeSReq0Y1-mRAMO-SivqDRX_caamymdH9f=zsfLxsjow@mail.gmail.com>
References: <16583-4dc8f6c3b8ed24fa@postgresql.org>
<422379.1612684550@sss.pgh.pa.us>
<161476090989.29967.7097745522340560681.pgcf@coridan.postgresql.org>
<CALtqXTdeSReq0Y1-mRAMO-SivqDRX_caamymdH9f=zsfLxsjow@mail.gmail.com>
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
Attachments:fix-postgres-fdw-collation-handling-3.patch (text/x-diff, 41.1K) downloaddiff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.cindex 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.outindex 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;
- 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::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
@@ -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.cindex 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.sqlindex 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.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, ibrar.ahmad@gmail.com, pgsql-hackers@lists.postgresql.org, carpenter.nail.cz@gmail.com
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
In-Reply-To: <3404441.1626208861@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox