Re: postgres_fdw could deparse ArrayCoerceExpr

On Fri, Jul 18, 2025 at 5:34 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> Alexander Korotkov <aekorotkov(at)gmail(dot)com> 于2025年7月16日周三 05:56写道：
>>
>> On Wed, Jun 4, 2025 at 11:52 PM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>> > On Wed, Jun 4, 2025 at 6:15 PM Alexander Pyhalov
>> > <a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
>> > > Alexander Korotkov писал(а) 2025-06-04 14:29:
>> > > > On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <orlovmg(at)gmail(dot)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.
>
>
> Hi Alexander,
>
> I found a little typo in this commit. Other places use "an" before ArrayCoerceExpr.
> To be consistent may be better. So, please take a look at the attached patch.

Sure thing, pushed!

------
Regards,
Alexander Korotkov
Supabase