Maxim Orlov писал(а) 2025-01-24 18:09:
Look like an overlook for me. Apparently no one has encountered this
use case before.
Patch seems good to me with no visible defects. Deparse support was
also added. As well as a
test case. But do we really need copy/paste code for a
T_ArrayCoerceExpr case? To be more specific,
can we "reuse" T_RelabelType case, as it made for T_OpExpr and
T_DistinctExpr?
--
Unfortunately, it's not so simple. We can't just ship type casts to
remote server if we are not sure that local and remote types match. For
example,
CREATE TYPE enum_of_int_like AS enum('1', '2', '3', '4');
CREATE TABLE conversions(id int, d enum_of_int_like);
CREATE FOREIGN TABLE ft_conversions (id int, d char(1))
SERVER loopback options (table_name 'conversions');
INSERT INTO ft_conversions VALUES (1, '1'), (2, '2'), (3, '3'), (4,
'4');
Patched version gives error:
-- Test array type conversion pushdown
SET plan_cache_mode = force_generic_plan;
PREPARE s(varchar[]) AS SELECT count(*) FROM ft_conversions WHERE d =
ANY ($1);
EXPLAIN (VERBOSE, COSTS OFF)
EXECUTE s(ARRAY['1','2']);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Foreign Scan
Output: (count(*))
Relations: Aggregate on (public.ft_conversions)
Remote SQL: SELECT count(*) FROM public.conversions WHERE ((d = ANY
($1::character varying[])))
(4 rows)
EXECUTE s(ARRAY['1','2']);
ERROR: operator does not exist: public.enum_of_int_like = character
varying
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
CONTEXT: remote SQL command: SELECT count(*) FROM public.conversions
WHERE ((d = ANY ($1::character varying[])))
Original one does successful local filtering:
PREPARE s(varchar[]) AS SELECT count(*) FROM ft_conversions WHERE d =
ANY ($1);
EXPLAIN (VERBOSE, COSTS OFF)
EXECUTE s(ARRAY['1','2']);
QUERY PLAN
-----------------------------------------------------------
Aggregate
Output: count(*)
-> Foreign Scan on public.ft_conversions
Output: id, d
Filter: (ft_conversions.d = ANY (($1)::bpchar[]))
Remote SQL: SELECT d FROM public.conversions
(6 rows)
EXECUTE s(ARRAY['1','2']);
count
-------
2
--
Best regards,
Alexander Pyhalov,
Postgres Professional
