postgres_fdw could deparse ArrayCoerceExpr

From: Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: postgres_fdw could deparse ArrayCoerceExpr
Date: 2024-11-28 14:57:32
Message-ID: 4f0cea802476d23c6e799512ffd17aff@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch text/x-diff 5.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Kirill Reshke 2024-11-28 15:18:21 Re: Useless field ispartitioned in CreateStmtContext
Previous Message Daniel Gustafsson 2024-11-28 14:23:05 Re: Fix comment in reorderbuffer.h