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

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

  1. Fix a typo in the deparseArrayCoerceExpr() header comment

  2. Support for deparsing of ArrayCoerceExpr node in contrib/postgres_fdw