Subject: Re: postgres_fdw could deparse ArrayCoerceExpr

From: Maxim Orlov <[email-masked]>
Date: Wed, 29 Jan 2025 12:58:55 +0300

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.

-- 
Best regards,
Maxim Orlov.

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 4 Jun 2025 14:29:04 +0300

Hi Maxim,

On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <[email-masked]> 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?
2) Can we fallback to remote type conversion in local type conversion fails?

------
Regards,
Alexander Korotkov
Supabase

---

From: Alexander Pyhalov <[email-masked]>
Date: Wed, 04 Jun 2025 18:15:11 +0300

Hi.

Alexander Korotkov писал(а) 2025-06-04 14:29:
>> 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.

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 

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.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 4 Jun 2025 23:52:50 +0300

> EXPLAIN (VERBOSE, COSTS OFF)

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

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 16 Jul 2025 00:55:44 +0300

> 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

---

From: Tender Wang <[email-masked]>
Date: Fri, 18 Jul 2025 22:33:50 +0800

> > > EXPLAIN (VERBOSE, COSTS OFF)

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.

-- 
Thanks,
Tender Wang
From f348498cf70e68cfdaf8df3a1bed66e6a0bb7f09 Mon Sep 17 00:00:00 2001
From: Tender Wang <[email-masked]>
Date: Fri, 18 Jul 2025 22:26:56 +0800
Subject: [PATCH v1] Fix a typo.

---
 contrib/postgres_fdw/deparse.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d761d076dc8..e5b5e1a5f51 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -3540,7 +3540,7 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
 }
 
 /*
- * Deparse a ArrayCoerceExpr (array-type conversion) node.
+ * Deparse an ArrayCoerceExpr (array-type conversion) node.
  */
 static void
 deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context)
-- 
2.34.1

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 18 Jul 2025 18:41:55 +0300

On Fri, Jul 18, 2025 at 5:34 PM Tender Wang <[email-masked]> wrote:
> 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