public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alexander Pyhalov <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Maxim Orlov <[email protected]>
Cc: [email protected]
Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
Date: Wed, 04 Jun 2025 18:15:11 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdtoZNDzqm=n9tDFRtCrswJJ533zWmyZ_EZdqew+7EYxeQ@mail.gmail.com>
References: <[email protected]>
	<CACG=ezYRyy0quCUPAOze+zRA6qdndFbJ_pKDGVVHa+rZVv390w@mail.gmail.com>
	<[email protected]>
	<CACG=ezamc2JY7SkhfBMWsybf-O+nSPzK36cMcDg8yCjFWM3R0Q@mail.gmail.com>
	<CACG=ezYDjmTtVqKHxcMzq8KrnNV1sMTBuf1ZxLPx7jPKTKCoaw@mail.gmail.com>
	<CACG=ezZ5g=8nKHqdc-rJQNd31jJ1t_aLgWSDcFPNBMxfG4-KsA@mail.gmail.com>
	<CAPpHfdtoZNDzqm=n9tDFRtCrswJJ533zWmyZ_EZdqew+7EYxeQ@mail.gmail.com>

Hi.

Alexander Korotkov писал(а) 2025-06-04 14:29:
> On Wed, Jan 29, 2025 at 11:59 AM Maxim Orlov <[email protected]> wrote:
>> 
>> One important note here. This patch will change cast behaviour in case 
>> of local and foreign types are mismatched.
>> The problem is if we cannot convert types locally, this does not mean 
>> that it is also true for a foreign wrapped data.
>> In any case, it's up to the committer to decide whether this change is 
>> needed or not.
> 
> I have two question regarding this aspect.
> 1) Is it the same with regular type conversion?

Yes, it's the same.

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');
SET plan_cache_mode = force_generic_plan;
PREPARE s(varchar) AS SELECT count(*) FROM ft_conversions where d=$1;
EXPLAIN (VERBOSE, COSTS OFF)
EXECUTE s('1');
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Foreign Scan
    Output: (count(*))
    Relations: Aggregate on (public.ft_conversions)
    Remote SQL: SELECT count(*) FROM public.conversions WHERE ((d = 
$1::character varying))
(4 rows)

EXECUTE s('1');
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.

> 2) Can we fallback to remote type conversion in local type conversion 
> fails?

It's the opposite - we've already planned (and deparsed) statement, 
using remote type conversion.
When plan execution fails, there's nothing we can do.
We'll get

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.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

view thread (8+ messages)  latest in thread

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], [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