On Sun Sep 5, 2021 at 6:43 PM EDT, Tom Lane wrote:
> "Dian M Fay" <dian.m....@gmail.com> writes:
> > [ 0001-Suppress-explicit-casts-of-text-constants-in-postgre.patch ]
>
> I took a quick look at this. The restriction to type text seems like
> very obviously a hack rather than something we actually want; wouldn't
> it mean we fail to act in a large fraction of the cases where we'd
> like to suppress the cast?
>
> A second problem is that I don't think the business with a
> const_showtype
> context field is safe at all. As you've implemented it here, it would
> affect the entire RHS tree, including constants far down inside complex
> expressions that have nothing to do with the top-level semantics.
> (I didn't look closely, but I wonder if the regression failure you
> mentioned is associated with that.)
>
> I think that we only want to suppress the cast in cases where
> (1) the constant is directly an operand of the operator we're
> expecting the remote parser to use its same-type heuristic for, and
> (2) the constant will be deparsed as a string literal. (If it's
> deparsed as a number, boolean, etc, then it won't be initially
> UNKNOWN, so that heuristic won't be applied.)
>
> Now point 1 means that we don't really need to mess with keeping
> state in the recursion context. If we've determined at the level
> of the OpExpr that we can do this, including checking that the
> RHS operand IsA(Const), then we can just invoke deparseConst() on
> it directly instead of recursing via deparseExpr().
>
> Meanwhile, I suspect that point 2 might be best checked within
> deparseConst() itself, as that contains both the decision and the
> mechanism about how the Const will be printed. So that suggests
> that we should invent a new showtype code telling deparseConst()
> to act this way, and then supply that code directly when we
> invoke deparseConst directly from deparseOpExpr.
>
> BTW, don't we also want to be able to optimize cases where the Const
> is on the LHS rather than the RHS?
>
> regards, tom lane
Thanks Tom, that makes way more sense! I've attached a new patch which
tests operands and makes sure one side is a Const before feeding it to
deparseConst with a new showtype code, -2. The one regression is gone,
but I've left a couple of test output discrepancies for now which
showcase lost casts on the following predicates:
* date(c5) = '1970-01-17'::date
* ctid = '(0,2)'::tid
These aren't exactly failures -- both implicit string comparisons work
just fine -- but I don't know Postgres well enough to be sure that
that's true more generally. I did try checking that the non-Const member
of the predicate is a Var; that left the date cast alone, since date(c5)
is a FuncExpr, but obviously can't do anything about the tid.
There's also an interesting case where `val::text LIKE 'foo'` works when
val is an enum column in the local table, and breaks, castless, with an
operator mismatch when it's altered to text: Postgres' statement parser
recognizes the cast as redundant and creates a Var node instead of a
RelabelType (as it will for, say, `val::varchar(10)`) before the FDW is
even in the picture. It's a little discomfiting, but I suppose a certain
level of "caveat emptor" entails when disregarding foreign types.
> (val as enum on local and remote)
> explain verbose select * from test where (val::text) like 'foo';
>
> Foreign Scan on public.test (cost=100.00..169.06 rows=8 width=28)
> Output: id, val, on_day, ts, ts2
> Filter: ((test.val)::text ~~ 'foo'::text)
> Remote SQL: SELECT id, val, on_day, ts, ts2 FROM public.test
>
> (val as local text, remote enum)
> explain verbose select * from test where (val::text) like 'foo';
>
> Foreign Scan on public.test (cost=100.00..122.90 rows=5 width=56)
> Output: id, val, on_day, ts, ts2
> Remote SQL: SELECT id, val, on_day, ts, ts2 FROM public.test WHERE ((val
> ~~ 'foo'))
>
> explain verbose select * from test where (val::varchar(10)) like 'foo';
>
> Foreign Scan on public.test (cost=100.00..125.46 rows=5 width=56)
> Output: id, val, on_day, ts, ts2
> Remote SQL: SELECT id, val, on_day, ts, ts2 FROM public.test WHERE
> ((val::character varying(10) ~~ 'foo'))
Outside that, deparseConst also contains a note about keeping the code
in sync with the parser (make_const in particular); from what I could
tell, I don't think there's anything in this that necessitates changes
there.
From 92ca385ff98913333008e48d975a622f8b56b921 Mon Sep 17 00:00:00 2001
From: Dian M Fay <dian.m....@gmail.com>
Date: Sat, 23 Oct 2021 00:54:48 -0400
Subject: [PATCH] Suppress explicit casts of safe const operands
Adds a new showtype code -2 to deparseConst in order to skip casting for
string literals when deparseOpExpr determines that the remote server can
be relied on to match operand types.
---
contrib/postgres_fdw/deparse.c | 36 ++++-
.../postgres_fdw/expected/postgres_fdw.out | 132 +++++++++++++-----
contrib/postgres_fdw/sql/postgres_fdw.sql | 18 +++
3 files changed, 146 insertions(+), 40 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd66681..112baf0d7c 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -2707,6 +2707,7 @@ deparseConst(Const *node, deparse_expr_cxt *context, int
showtype)
bool typIsVarlena;
char *extval;
bool isfloat = false;
+ bool isstring = false;
bool needlabel;
if (node->constisnull)
@@ -2762,12 +2763,13 @@ deparseConst(Const *node, deparse_expr_cxt *context,
int showtype)
break;
default:
deparseStringLiteral(buf, extval);
+ isstring = true;
break;
}
pfree(extval);
- if (showtype < 0)
+ if (showtype == -1)
return;
/*
@@ -2788,7 +2790,7 @@ deparseConst(Const *node, deparse_expr_cxt *context, int
showtype)
needlabel = !isfloat || (node->consttypmod >= 0);
break;
default:
- needlabel = true;
+ needlabel = !(isstring && showtype == -2);
break;
}
if (needlabel || showtype > 0)
@@ -2953,6 +2955,10 @@ deparseOpExpr(OpExpr *node, deparse_expr_cxt *context)
StringInfo buf = context->buf;
HeapTuple tuple;
Form_pg_operator form;
+ Expr *left;
+ Expr *right;
+ Oid leftType;
+ Oid rightType;
char oprkind;
/* Retrieve information about the operator from system catalog. */
@@ -2969,10 +2975,24 @@ deparseOpExpr(OpExpr *node, deparse_expr_cxt *context)
/* Always parenthesize the expression. */
appendStringInfoChar(buf, '(');
+ right = llast(node->args);
+ rightType = exprType((Node *) right);
+
/* Deparse left operand, if any. */
if (oprkind == 'b')
{
- deparseExpr(linitial(node->args), context);
+ left = linitial(node->args);
+ leftType = exprType((Node *) left);
+
+ if (IsA(left, Const) && leftType == form->oprleft)
+ {
+ deparseConst((Const*) left, context, -2);
+ }
+ else
+ {
+ deparseExpr(left, context);
+ }
+
appendStringInfoChar(buf, ' ');
}
@@ -2981,7 +3001,15 @@ deparseOpExpr(OpExpr *node, deparse_expr_cxt *context)
/* Deparse right operand. */
appendStringInfoChar(buf, ' ');
- deparseExpr(llast(node->args), context);
+
+ if (IsA(right, Const) && rightType == form->oprright)
+ {
+ deparseConst((Const*) right, context, -2);
+ }
+ else
+ {
+ deparseExpr(right, context);
+ }
appendStringInfoChar(buf, ')');
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 44c4367b8f..ccdd3b1c2a 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -341,11 +341,11 @@ SELECT * FROM ft1 WHERE false;
-- with WHERE clause
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6
= '1' AND t1.c7 >= '1';
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((c7 >= '1'::bpchar)) AND (("C 1" = 101)) AND ((c6 = '1'::text))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((c7 >= '1')) AND (("C 1" = 101)) AND ((c6 = '1'))
(3 rows)
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
@@ -707,11 +707,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE
c1 = (ARRAY[c1,c2,3])[1]
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';
-- check special chars
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((c6 = E'foo''s\\bar'::text))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((c6 = E'foo''s\\bar'))
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't
be sent to remote
@@ -1130,20 +1130,20 @@ SELECT * FROM ft1 WHERE c1 > (CASE random()::integer
WHEN 0 THEN 1 WHEN 2 THEN 5
-- these are shippable
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c6 WHEN 'foo' THEN true ELSE c3 < 'bar' END;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((CASE c6 WHEN 'foo'::text THEN true ELSE (c3 < 'bar'::text) END))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((CASE c6 WHEN 'foo'::text THEN true ELSE (c3 < 'bar') END))
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c3 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
- 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))
+ 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') END))
(3 rows)
-- but this is not because of collation
@@ -3491,12 +3491,12 @@ ORDER BY ref_0."C 1";
Index Cond: (ref_0."C 1" < 10)
-> Foreign Scan on public.ft1 ref_1
Output: ref_1.c3, ref_0.c2
- Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 =
'00001'::text))
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'))
-> Materialize
Output: ref_3.c3
-> Foreign Scan on public.ft2 ref_3
Output: ref_3.c3
- Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 =
'00001'::text))
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'))
(15 rows)
SELECT ref_0.c2, subq_1.*
@@ -4205,6 +4205,66 @@ ERROR: invalid input syntax for type integer: "foo"
CONTEXT: column "c8" of foreign table "ft1"
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
+-- conversion to operator-compatible local type
+-- ===================================================================
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+SELECT * FROM ft1 WHERE c8::text LIKE 'foo' LIMIT 1;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE text;
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+SELECT * FROM ft1 WHERE 'foo' = c8 LIMIT 1;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
((c8 = 'foo')) LIMIT 1::bigint
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 WHERE 'foo' = c8 LIMIT 1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE
(('foo' = c8)) LIMIT 1::bigint
+(3 rows)
+
+-- we declared c8 to be text locally, but it's still the same type on
+-- the remote which will balk if we try to do anything incompatible
+-- with that remote type
+SELECT * FROM ft1 WHERE trim(c8) = 'foo' LIMIT 1; -- ERROR
+ERROR: function btrim(public.user_enum) does not exist
+HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
+CONTEXT: remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S
1"."T 1" WHERE ((btrim(c8) = 'foo')) LIMIT 1::bigint
+SELECT * FROM ft1 WHERE c8 LIKE 'foo' LIMIT 1; -- ERROR
+ERROR: operator does not exist: public.user_enum ~~ unknown
+HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
+CONTEXT: remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S
1"."T 1" WHERE ((c8 ~~ 'foo')) LIMIT 1::bigint
+SELECT * FROM ft1 WHERE c8::text LIKE 'foo' LIMIT 1; -- ERROR
+ERROR: operator does not exist: public.user_enum ~~ unknown
+HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
+CONTEXT: remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S
1"."T 1" WHERE ((c8 ~~ 'foo')) LIMIT 1::bigint
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
-- ===================================================================
@@ -4254,35 +4314,35 @@ create foreign table ft3 (f1 text collate "C", f2 text,
f3 varchar(10))
server loopback options (table_name 'loct3', use_remote_estimate 'true');
-- can be sent to remote
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'))
(3 rows)
explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f1 = 'foo'))
(3 rows)
explain (verbose, costs off) select * from ft3 where f2 = 'foo';
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Foreign Scan on public.ft3
Output: f1, f2, f3
- Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f2 = 'foo'::text))
+ Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f2 = 'foo'))
(3 rows)
explain (verbose, costs off) select * from ft3 where f3 = 'foo';
- QUERY PLAN
-------------------------------------------------------------------------------
+ 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 ((f3 = 'foo'))
(3 rows)
explain (verbose, costs off) select * from ft3 f, loct3 l
@@ -4384,22 +4444,22 @@ INSERT INTO ft2 (c1,c2,c3)
INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
-- can be pushed down
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
Update on public.ft2
-> Foreign Update on public.ft2
- Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300), c3 = (c3 ||
'_update3'::text) WHERE ((("C 1" % 10) = 3))
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300), c3 = (c3 ||
'_update3') WHERE ((("C 1" % 10) = 3))
(3 rows)
UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7
RETURNING *; -- can be pushed down
-
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY
PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8
-> Foreign Update on public.ft2
- Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 400), c3 = (c3 ||
'_update7'::text) WHERE ((("C 1" % 10) = 7)) RETURNING "C 1", c2, c3, c4, c5,
c6, c7, c8
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 400), c3 = (c3 ||
'_update7') WHERE ((("C 1" % 10) = 7)) RETURNING "C 1", c2, c3, c4, c5, c6, c7,
c8
(4 rows)
UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7
RETURNING *;
@@ -4512,11 +4572,11 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7'
WHERE c1 % 10 = 7 RETURNING
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
-- can be pushed down
-
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
-> Foreign Update
- Remote SQL: UPDATE "S 1"."T 1" r1 SET c2 = (r1.c2 + 500), c3 = (r1.c3
|| '_update9'::text), c7 = 'ft2 '::character(10) FROM "S 1"."T 1" r2
WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9))
+ Remote SQL: UPDATE "S 1"."T 1" r1 SET c2 = (r1.c2 + 500), c3 = (r1.c3
|| '_update9'), c7 = 'ft2 '::character(10) FROM "S 1"."T 1" r2 WHERE
((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9))
(3 rows)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
@@ -8129,7 +8189,7 @@ select tableoid::regclass, * FROM locp;
update utrtest set a = 2 where b = 'foo' returning *;
ERROR: new row for relation "loct" violates check constraint "loct_a_check"
DETAIL: Failing row contains (2, foo).
-CONTEXT: remote SQL command: UPDATE public.loct SET a = 2 WHERE ((b =
'foo'::text)) RETURNING a, b
+CONTEXT: remote SQL command: UPDATE public.loct SET a = 2 WHERE ((b = 'foo'))
RETURNING a, b
-- But the reverse is allowed
update utrtest set a = 1 where b = 'qux' returning *;
ERROR: cannot route tuples into foreign table to be updated "remp"
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e7b869f8ce..b2acbcbd18 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1167,6 +1167,24 @@ SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; --
ERROR
ANALYZE ft1; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+-- ===================================================================
+-- conversion to operator-compatible local type
+-- ===================================================================
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+SELECT * FROM ft1 WHERE c8::text LIKE 'foo' LIMIT 1;
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE text;
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+SELECT * FROM ft1 WHERE 'foo' = c8 LIMIT 1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 WHERE 'foo' = c8 LIMIT 1;
+-- we declared c8 to be text locally, but it's still the same type on
+-- the remote which will balk if we try to do anything incompatible
+-- with that remote type
+SELECT * FROM ft1 WHERE trim(c8) = 'foo' LIMIT 1; -- ERROR
+SELECT * FROM ft1 WHERE c8 LIKE 'foo' LIMIT 1; -- ERROR
+SELECT * FROM ft1 WHERE c8::text LIKE 'foo' LIMIT 1; -- ERROR
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+
-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
--
2.33.1
