Re: postgres_fdw could deparse ArrayCoerceExpr - Mailing list pgsql-hackers

Alexander Korotkov писал(а) 2026-07-12 19:48:
> On Sat, Jul 11, 2026 at 5:42 AM Noah Misch <noah@leadboat.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@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.
>> 
>> > > 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.
> 

Thanks for report. The suggested fix looks good to me.
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional