Hi.
Alexander Korotkov писал(а) 2025-06-04 14:29:
On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <orlo...@gmail.com> wrote:
One important note here. This patch will change cast behaviour in case
of local and foreign types are mismatched.
The problem is if we cannot convert types locally, this does not mean
that it is also true for a foreign wrapped data.
In any case, it's up to the committer to decide whether this change is
needed or not.
I have two question regarding this aspect.
1) Is it the same with regular type conversion?
Yes, it's the same.
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');
SET plan_cache_mode = force_generic_plan;
PREPARE s(varchar) AS SELECT count(*) FROM ft_conversions where d=$1;
EXPLAIN (VERBOSE, COSTS OFF)
EXECUTE s('1');
QUERY PLAN
-------------------------------------------------------------------------------------------
Foreign Scan
Output: (count(*))
Relations: Aggregate on (public.ft_conversions)
Remote SQL: SELECT count(*) FROM public.conversions WHERE ((d =
$1::character varying))
(4 rows)
EXECUTE s('1');
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.
2) Can we fallback to remote type conversion in local type conversion
fails?
It's the opposite - we've already planned (and deparsed) statement,
using remote type conversion.
When plan execution fails, there's nothing we can do.
We'll get
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.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
