public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alexander Pyhalov <[email protected]>
To: Maxim Orlov <[email protected]>
Cc: [email protected]
Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
Date: Mon, 27 Jan 2025 09:46:35 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACG=ezYRyy0quCUPAOze+zRA6qdndFbJ_pKDGVVHa+rZVv390w@mail.gmail.com>
References: <[email protected]>
	<CACG=ezYRyy0quCUPAOze+zRA6qdndFbJ_pKDGVVHa+rZVv390w@mail.gmail.com>

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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox