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 |
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 |