Re: postgres_fdw could deparse ArrayCoerceExpr

On Wed, Jun 4, 2025 at 11:52 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> 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.

I've written a commit message for this patch.  I'm going to push this
if no objections.

------
Regards,
Alexander Korotkov
Supabase