Re: postgres_fdw could deparse ArrayCoerceExpr

On Wed, Jun 4, 2025 at 6:15 PM Alexander Pyhalov
<a.pyhalov@postgrespro.ru> wrote:
> Alexander Korotkov писал(а) 2025-06-04 14:29:
> > On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <orlovmg@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.

Got it, thank you for the explanation.  I thin it's fair that array
coercion works the same way as a regular cast.

------
Regards,
Alexander Korotkov
Supabase