postgres_fdw could deparse ArrayCoerceExpr
Alexander Pyhalov <a.pyhalov@postgrespro.ru>
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Date: 2024-11-28T14:57:32Z
Lists: pgsql-hackers
Attachments
- v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch (text/x-diff) patch v1-0001
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
Commits
-
Fix a typo in the deparseArrayCoerceExpr() header comment
- 4c5159a2d8c0 19 (unreleased) landed
-
Support for deparsing of ArrayCoerceExpr node in contrib/postgres_fdw
- 62c3b4cd9ddc 19 (unreleased) landed