postgres_fdw could deparse ArrayCoerceExpr
Hi.
Recently, we were surprised by the following behavior - prepared
statement, selecting data from foreign table with varchar(N) field
couldn't push down "field = ANY($1)" expression, when switched to
generic plan. This looked like shown in the attached patch. Reproducer
is simple:
create extension postgres_fdw;
create server local foreign data wrapper postgres_fdw;
create user MAPPING FOR CURRENT_USER SERVER local;
create table test (c varchar(255));
create foreign table ftest (c varchar(255)) server local options
(table_name 'test');
set plan_cache_mode to force_generic_plan ; -- just for demonstration,
can happen with defautl plan_cache_mode, if planner decides that generic
plan is preferable
prepare s(varchar[]) as select * from ftest where c = any ($1);
explain verbose execute s('{test}');
QUERY PLAN
----------------------------------------------------------------------
Foreign Scan on public.ftest (cost=100.00..143.43 rows=7 width=516)
Output: c
Filter: ((ftest.c)::text = ANY (($1)::text[]))
Remote SQL: SELECT c FROM public.test
The issue is that we need to translate input array type from varchar[]
to text[].
Attaching patch to allow postgres_fdw to deparse such conversion.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patchtext/x-diff; name=v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patchDownload
From efc5eb338db6621243e25e0b0281ae69c465974d Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
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
Look like an overlook for me. Apparently no one has encountered this use
case before.
Patch seems good to me with no visible defects. Deparse support was also
added. As well as a
test case. But do we really need copy/paste code for a T_ArrayCoerceExpr
case? To be more specific,
can we "reuse" T_RelabelType case, as it made for T_OpExpr and
T_DistinctExpr?
--
Best regards,
Maxim Orlov.
Maxim Orlov писал(а) 2025-01-24 18:09:
Look like an overlook for me. Apparently no one has encountered this
use case before.Patch seems good to me with no visible defects. Deparse support was
also added. As well as a
test case. But do we really need copy/paste code for a
T_ArrayCoerceExpr case? To be more specific,
can we "reuse" T_RelabelType case, as it made for T_OpExpr and
T_DistinctExpr?--
Unfortunately, it's not so simple. We can't just ship type casts to
remote server if we are not sure that local and remote types match. For
example,
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');
INSERT INTO ft_conversions VALUES (1, '1'), (2, '2'), (3, '3'), (4,
'4');
Patched version gives error:
-- Test array type conversion pushdown
SET plan_cache_mode = force_generic_plan;
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.
CONTEXT: remote SQL command: SELECT count(*) FROM public.conversions
WHERE ((d = ANY ($1::character varying[])))
Original one does successful local filtering:
PREPARE s(varchar[]) AS SELECT count(*) FROM ft_conversions WHERE d =
ANY ($1);
EXPLAIN (VERBOSE, COSTS OFF)
EXECUTE s(ARRAY['1','2']);
QUERY PLAN
-----------------------------------------------------------
Aggregate
Output: count(*)
-> Foreign Scan on public.ft_conversions
Output: id, d
Filter: (ft_conversions.d = ANY (($1)::bpchar[]))
Remote SQL: SELECT d FROM public.conversions
(6 rows)
EXECUTE s(ARRAY['1','2']);
count
-------
2
--
Best regards,
Alexander Pyhalov,
Postgres Professional
On Mon, 27 Jan 2025 at 09:46, Alexander Pyhalov <a.pyhalov@postgrespro.ru>
wrote:
Unfortunately, it's not so simple. We can't just ship type casts to
remote server if we are not sure that local and remote types match. For
example,
Yeah, my fault. I've overlooked an "elemexpr" member in "ArrayCoerceExpr"
and erroneously
consider them to have the same structure. Maybe some refactoring may be
done here, but,
obviously, this is not a goal of this patch
--
Best regards,
Maxim Orlov.
So, patch looks good to me. Implements described functionality. Test case
also provided.
I think it's ready to be viewed by a committer.
--
Best regards,
Maxim Orlov.
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.
Hi Maxim,
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?
2) Can we fallback to remote type conversion in local type conversion fails?
------
Regards,
Alexander Korotkov
Supabase
Hi.
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.
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.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
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.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 getPREPARE 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.
------
Regards,
Alexander Korotkov
Supabase
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.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 getPREPARE 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:
v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patchapplication/octet-stream; name=v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patchDownload
From efc5eb338db6621243e25e0b0281ae69c465974d Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
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
Alexander Korotkov <aekorotkov@gmail.com> 于2025年7月16日周三 05:56写道：
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 notmean
that it is also true for a foreign wrapped data.
In any case, it's up to the committer to decide whether this changeis
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 getPREPARE 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.
--
Thanks,
Tender Wang
Attachments:
v1-0001-Fix-a-typo.patchtext/plain; charset=US-ASCII; name=v1-0001-Fix-a-typo.patchDownload
From f348498cf70e68cfdaf8df3a1bed66e6a0bb7f09 Mon Sep 17 00:00:00 2001
From: Tender Wang <tndrwang@gmail.com>
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
On Fri, Jul 18, 2025 at 5:34 PM Tender Wang <tndrwang@gmail.com> wrote:
Alexander Korotkov <aekorotkov@gmail.com> 于2025年7月16日周三 05:56写道：
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.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 getPREPARE 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