Re: postgres_fdw could deparse ArrayCoerceExpr

On Sat, Jul 11, 2026 at 5:42 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Wed, Jul 16, 2025 at 12:55:44AM +0300, Alexander Korotkov wrote:
> > 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.
>
> > > Got it, thank you for the explanation. I thin it's fair that array
> > > coercion works the same way as a regular cast.
>
> I agree with that principle. While the above example shows regular and array
> casts aligned, Fable 5 found the attached test cases where that alignment is
> absent, yielding array-specific wrong query result scenarios. This thread's
> commit 62c3b4c introduced those. I think the test patch's "implicit-format
> ArrayCoerceExpr" is not meaningfully a regression, because scalars have the
> same problem. The other two, "pushed down although the element conversion
> calls a cast" and "CoerceViaIO are pushed down", are array-specific
> regressions. Scalars don't get corresponding trouble for those two. I'm also
> attaching Fable 5's report.
>
> > I've written a commit message for this patch. I'm going to push this
> > if no objections.

Thank you for catching this. The fix is attached. It ships
ArrayCoerceExpr only when its element expression is RelabelType or
CaseTestExpr. I propose to backpatch it to pg 19. And we can
consider shipping ArrayCoerceExpr with shippable element cast
functions for pg 20.

------
Regards,
Alexander Korotkov
Supabase