public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: Alexander Pyhalov <[email protected]>
Cc: Maxim Orlov <[email protected]>
Cc: [email protected]
Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
Date: Wed, 16 Jul 2025 00:55:44 +0300
Message-ID: <CAPpHfdvYO92VgOjOKeejqoGavg5q3pcYB55C_n+2OkTs4m-71w@mail.gmail.com> (raw)
In-Reply-To: <CAPpHfdutHWetLMuhYnpfUZoqVnzRr_YyfwE2AzvJPQoK_B+vPQ@mail.gmail.com>
References: <[email protected]>
<CACG=ezYRyy0quCUPAOze+zRA6qdndFbJ_pKDGVVHa+rZVv390w@mail.gmail.com>
<[email protected]>
<CACG=ezamc2JY7SkhfBMWsybf-O+nSPzK36cMcDg8yCjFWM3R0Q@mail.gmail.com>
<CACG=ezYDjmTtVqKHxcMzq8KrnNV1sMTBuf1ZxLPx7jPKTKCoaw@mail.gmail.com>
<CACG=ezZ5g=8nKHqdc-rJQNd31jJ1t_aLgWSDcFPNBMxfG4-KsA@mail.gmail.com>
<CAPpHfdtoZNDzqm=n9tDFRtCrswJJ533zWmyZ_EZdqew+7EYxeQ@mail.gmail.com>
<[email protected]>
<CAPpHfdutHWetLMuhYnpfUZoqVnzRr_YyfwE2AzvJPQoK_B+vPQ@mail.gmail.com>
On Wed, Jun 4, 2025 at 11:52 PM Alexander Korotkov <[email protected]> wrote:
> On Wed, Jun 4, 2025 at 6:15 PM Alexander Pyhalov
> <[email protected]> wrote:
> > Alexander Korotkov писал(а) 2025-06-04 14:29:
> > > On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <[email protected]> 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
Attachments:
[application/octet-stream] v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch (5.3K, ../CAPpHfdvYO92VgOjOKeejqoGavg5q3pcYB55C_n+2OkTs4m-71w@mail.gmail.com/2-v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch)
download | inline diff:
From efc5eb338db6621243e25e0b0281ae69c465974d Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
Date: Wed, 27 Nov 2024 14:07:39 +0300
Subject: [PATCH] postgres_fdw could deparse ArrayCoerceExpr
---
contrib/postgres_fdw/deparse.c | 50 +++++++++++++++++++
.../postgres_fdw/expected/postgres_fdw.out | 21 ++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 9 ++++
3 files changed, 80 insertions(+)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 4680d517331..008237cb8f8 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -160,6 +160,7 @@ static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
static void deparseScalarArrayOpExpr(ScalarArrayOpExpr *node,
deparse_expr_cxt *context);
static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
+static void deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context);
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
static void deparseCaseExpr(CaseExpr *node, deparse_expr_cxt *context);
@@ -696,6 +697,34 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_ArrayCoerceExpr:
+ {
+ ArrayCoerceExpr *e = (ArrayCoerceExpr *) node;
+
+ /*
+ * Recurse to input subexpression.
+ */
+ if (!foreign_expr_walker((Node *) e->arg,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+
+ /*
+ * T_ArrayCoerceExpr must not introduce a collation not
+ * derived from an input foreign Var (same logic as for a
+ * function).
+ */
+ collation = e->resultcollid;
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
case T_BoolExpr:
{
BoolExpr *b = (BoolExpr *) node;
@@ -2913,6 +2942,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_RelabelType:
deparseRelabelType((RelabelType *) node, context);
break;
+ case T_ArrayCoerceExpr:
+ deparseArrayCoerceExpr((ArrayCoerceExpr *) node, context);
+ break;
case T_BoolExpr:
deparseBoolExpr((BoolExpr *) node, context);
break;
@@ -3501,6 +3533,24 @@ deparseRelabelType(RelabelType *node, deparse_expr_cxt *context)
node->resulttypmod));
}
+/*
+ * Deparse a ArrayCoerceExpr (array-type conversion) node.
+ */
+static void
+deparseArrayCoerceExpr(ArrayCoerceExpr *node, deparse_expr_cxt *context)
+{
+ deparseExpr(node->arg, context);
+
+ /*
+ * No difference how to deparse explicit cast, but if we omit implicit
+ * cast in the query, it'll be more user-friendly
+ */
+ if (node->coerceformat != COERCE_IMPLICIT_CAST)
+ appendStringInfo(context->buf, "::%s",
+ deparse_type_name(node->resulttype,
+ node->resulttypmod));
+}
+
/*
* Deparse a BoolExpr node.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f2bcd6aa98c..55a8ac9020e 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1169,6 +1169,27 @@ SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' EN
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)
+-- Test array type conversion pushdown
+SET plan_cache_mode = force_generic_plan;
+PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE s(ARRAY['1','2']);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(*))
+ Relations: Aggregate on (public.ft2)
+ Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c6 = ANY ($1::character varying[])))
+(4 rows)
+
+EXECUTE s(ARRAY['1','2']);
+ count
+-------
+ 200
+(1 row)
+
+DEALLOCATE s;
+RESET plan_cache_mode;
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 372fe6dad15..164cd8b895a 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -450,6 +450,15 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END;
+-- Test array type conversion pushdown
+SET plan_cache_mode = force_generic_plan;
+PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
+EXPLAIN (VERBOSE, COSTS OFF)
+EXECUTE s(ARRAY['1','2']);
+EXECUTE s(ARRAY['1','2']);
+DEALLOCATE s;
+RESET plan_cache_mode;
+
-- a regconfig constant referring to this text search configuration
-- is initially unshippable
CREATE TEXT SEARCH CONFIGURATION public.custom_search
--
2.43.0
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
In-Reply-To: <CAPpHfdvYO92VgOjOKeejqoGavg5q3pcYB55C_n+2OkTs4m-71w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox