MIME-Version: 1.0
References: <4f0cea802476d23c6e799512ffd17aff@postgrespro.ru>
 <CACG=ezYRyy0quCUPAOze+zRA6qdndFbJ_pKDGVVHa+rZVv390w@mail.gmail.com>
 <0be55d1548c3b1bc29b8ae23f6db24e3@postgrespro.ru>
 <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>
 <f3cfc7bf178f64a1684241ecd9881cf7@postgrespro.ru>
 <CAPpHfdutHWetLMuhYnpfUZoqVnzRr_YyfwE2AzvJPQoK_B+vPQ@mail.gmail.com>
In-Reply-To: 
 <CAPpHfdutHWetLMuhYnpfUZoqVnzRr_YyfwE2AzvJPQoK_B+vPQ@mail.gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Wed, 16 Jul 2025 00:55:44 +0300
Message-ID: 
 <CAPpHfdvYO92VgOjOKeejqoGavg5q3pcYB55C_n+2OkTs4m-71w@mail.gmail.com>
Subject: Re: postgres_fdw could deparse ArrayCoerceExpr
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Cc: Maxim Orlov <orlovmg@gmail.com>, pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000004eb5430639fed7a4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAPpHfdvYO92VgOjOKeejqoGavg5q3pcYB55C_n%2B2OkTs4m-71w%40mail.gmail.com>
Precedence: bulk

--0000000000004eb5430639fed7a4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Jun 4, 2025 at 11:52=E2=80=AFPM Alexander Korotkov <aekorotkov@gmai=
l.com> wrote:
> On Wed, Jun 4, 2025 at 6:15=E2=80=AFPM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:
> > Alexander Korotkov =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0) 2025-06-04 14=
:29:
> > > On Wed, Jan 29, 2025 at 11:59=E2=80=AFAM Maxim Orlov <orlovmg@gmail.c=
om> wrote:
> > >>
> > >> One important note here. This patch will change cast behaviour in ca=
se
> > >> of local and foreign types are mismatched.
> > >> The problem is if we cannot convert types locally, this does not mea=
n
> > >> 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 =3D force_generic_plan;
> > PREPARE s(varchar) AS SELECT count(*) FROM ft_conversions where d=3D$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 =3D
> > $1::character varying))
> > (4 rows)
> >
> > EXECUTE s('1');
> > ERROR:  operator does not exist: public.enum_of_int_like =3D 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=3DANY($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 =3D A=
NY
> > ($1::character varying[])))
> > (4 rows)
> >
> > EXECUTE s(ARRAY['1','2']);
> > ERROR:  operator does not exist: public.enum_of_int_like =3D character
> > varying
> > HINT:  No operator matches the given name and argument types. You might
> > need to add explicit type casts.
>
> Got it, thank you for the explanation.  I thin it's fair that array
> coercion works the same way as a regular cast.

I've written a commit message for this patch.  I'm going to push this
if no objections.

------
Regards,
Alexander Korotkov
Supabase

--0000000000004eb5430639fed7a4
Content-Type: application/octet-stream;
	name="v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch"
Content-Disposition: attachment;
	filename="v1-0001-postgres_fdw-could-deparse-ArrayCoerceExpr.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_md52myjl0>
X-Attachment-Id: f_md52myjl0

RnJvbSBlZmM1ZWIzMzhkYjY2MjEyNDNlMjVlMGIwMjgxYWU2OWM0NjU5NzRkIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4YW5kZXIgUHloYWxvdiA8YS5weWhhbG92QHBvc3RncmVz
cHJvLnJ1PgpEYXRlOiBXZWQsIDI3IE5vdiAyMDI0IDE0OjA3OjM5ICswMzAwClN1YmplY3Q6IFtQ
QVRDSF0gcG9zdGdyZXNfZmR3IGNvdWxkIGRlcGFyc2UgQXJyYXlDb2VyY2VFeHByCgotLS0KIGNv
bnRyaWIvcG9zdGdyZXNfZmR3L2RlcGFyc2UuYyAgICAgICAgICAgICAgICB8IDUwICsrKysrKysr
KysrKysrKysrKysKIC4uLi9wb3N0Z3Jlc19mZHcvZXhwZWN0ZWQvcG9zdGdyZXNfZmR3Lm91dCAg
ICB8IDIxICsrKysrKysrCiBjb250cmliL3Bvc3RncmVzX2Zkdy9zcWwvcG9zdGdyZXNfZmR3LnNx
bCAgICAgfCAgOSArKysrCiAzIGZpbGVzIGNoYW5nZWQsIDgwIGluc2VydGlvbnMoKykKCmRpZmYg
LS1naXQgYS9jb250cmliL3Bvc3RncmVzX2Zkdy9kZXBhcnNlLmMgYi9jb250cmliL3Bvc3RncmVz
X2Zkdy9kZXBhcnNlLmMKaW5kZXggNDY4MGQ1MTczMzEuLjAwODIzN2NiOGY4IDEwMDY0NAotLS0g
YS9jb250cmliL3Bvc3RncmVzX2Zkdy9kZXBhcnNlLmMKKysrIGIvY29udHJpYi9wb3N0Z3Jlc19m
ZHcvZGVwYXJzZS5jCkBAIC0xNjAsNiArMTYwLDcgQEAgc3RhdGljIHZvaWQgZGVwYXJzZURpc3Rp
bmN0RXhwcihEaXN0aW5jdEV4cHIgKm5vZGUsIGRlcGFyc2VfZXhwcl9jeHQgKmNvbnRleHQpOwog
c3RhdGljIHZvaWQgZGVwYXJzZVNjYWxhckFycmF5T3BFeHByKFNjYWxhckFycmF5T3BFeHByICpu
b2RlLAogCQkJCQkJCQkJIGRlcGFyc2VfZXhwcl9jeHQgKmNvbnRleHQpOwogc3RhdGljIHZvaWQg
ZGVwYXJzZVJlbGFiZWxUeXBlKFJlbGFiZWxUeXBlICpub2RlLCBkZXBhcnNlX2V4cHJfY3h0ICpj
b250ZXh0KTsKK3N0YXRpYyB2b2lkIGRlcGFyc2VBcnJheUNvZXJjZUV4cHIoQXJyYXlDb2VyY2VF
eHByICpub2RlLCBkZXBhcnNlX2V4cHJfY3h0ICpjb250ZXh0KTsKIHN0YXRpYyB2b2lkIGRlcGFy
c2VCb29sRXhwcihCb29sRXhwciAqbm9kZSwgZGVwYXJzZV9leHByX2N4dCAqY29udGV4dCk7CiBz
dGF0aWMgdm9pZCBkZXBhcnNlTnVsbFRlc3QoTnVsbFRlc3QgKm5vZGUsIGRlcGFyc2VfZXhwcl9j
eHQgKmNvbnRleHQpOwogc3RhdGljIHZvaWQgZGVwYXJzZUNhc2VFeHByKENhc2VFeHByICpub2Rl
LCBkZXBhcnNlX2V4cHJfY3h0ICpjb250ZXh0KTsKQEAgLTY5Niw2ICs2OTcsMzQgQEAgZm9yZWln
bl9leHByX3dhbGtlcihOb2RlICpub2RlLAogCQkJCQlzdGF0ZSA9IEZEV19DT0xMQVRFX1VOU0FG
RTsKIAkJCX0KIAkJCWJyZWFrOworCQljYXNlIFRfQXJyYXlDb2VyY2VFeHByOgorCQkJeworCQkJ
CUFycmF5Q29lcmNlRXhwciAqZSA9IChBcnJheUNvZXJjZUV4cHIgKikgbm9kZTsKKworCQkJCS8q
CisJCQkJICogUmVjdXJzZSB0byBpbnB1dCBzdWJleHByZXNzaW9uLgorCQkJCSAqLworCQkJCWlm
ICghZm9yZWlnbl9leHByX3dhbGtlcigoTm9kZSAqKSBlLT5hcmcsCisJCQkJCQkJCQkJIGdsb2Jf
Y3h0LCAmaW5uZXJfY3h0LCBjYXNlX2FyZ19jeHQpKQorCQkJCQlyZXR1cm4gZmFsc2U7CisKKwkJ
CQkvKgorCQkJCSAqIFRfQXJyYXlDb2VyY2VFeHByIG11c3Qgbm90IGludHJvZHVjZSBhIGNvbGxh
dGlvbiBub3QKKwkJCQkgKiBkZXJpdmVkIGZyb20gYW4gaW5wdXQgZm9yZWlnbiBWYXIgKHNhbWUg
bG9naWMgYXMgZm9yIGEKKwkJCQkgKiBmdW5jdGlvbikuCisJCQkJICovCisJCQkJY29sbGF0aW9u
ID0gZS0+cmVzdWx0Y29sbGlkOworCQkJCWlmIChjb2xsYXRpb24gPT0gSW52YWxpZE9pZCkKKwkJ
CQkJc3RhdGUgPSBGRFdfQ09MTEFURV9OT05FOworCQkJCWVsc2UgaWYgKGlubmVyX2N4dC5zdGF0
ZSA9PSBGRFdfQ09MTEFURV9TQUZFICYmCisJCQkJCQkgY29sbGF0aW9uID09IGlubmVyX2N4dC5j
b2xsYXRpb24pCisJCQkJCXN0YXRlID0gRkRXX0NPTExBVEVfU0FGRTsKKwkJCQllbHNlIGlmIChj
b2xsYXRpb24gPT0gREVGQVVMVF9DT0xMQVRJT05fT0lEKQorCQkJCQlzdGF0ZSA9IEZEV19DT0xM
QVRFX05PTkU7CisJCQkJZWxzZQorCQkJCQlzdGF0ZSA9IEZEV19DT0xMQVRFX1VOU0FGRTsKKwkJ
CX0KKwkJCWJyZWFrOwogCQljYXNlIFRfQm9vbEV4cHI6CiAJCQl7CiAJCQkJQm9vbEV4cHIgICAq
YiA9IChCb29sRXhwciAqKSBub2RlOwpAQCAtMjkxMyw2ICsyOTQyLDkgQEAgZGVwYXJzZUV4cHIo
RXhwciAqbm9kZSwgZGVwYXJzZV9leHByX2N4dCAqY29udGV4dCkKIAkJY2FzZSBUX1JlbGFiZWxU
eXBlOgogCQkJZGVwYXJzZVJlbGFiZWxUeXBlKChSZWxhYmVsVHlwZSAqKSBub2RlLCBjb250ZXh0
KTsKIAkJCWJyZWFrOworCQljYXNlIFRfQXJyYXlDb2VyY2VFeHByOgorCQkJZGVwYXJzZUFycmF5
Q29lcmNlRXhwcigoQXJyYXlDb2VyY2VFeHByICopIG5vZGUsIGNvbnRleHQpOworCQkJYnJlYWs7
CiAJCWNhc2UgVF9Cb29sRXhwcjoKIAkJCWRlcGFyc2VCb29sRXhwcigoQm9vbEV4cHIgKikgbm9k
ZSwgY29udGV4dCk7CiAJCQlicmVhazsKQEAgLTM1MDEsNiArMzUzMywyNCBAQCBkZXBhcnNlUmVs
YWJlbFR5cGUoUmVsYWJlbFR5cGUgKm5vZGUsIGRlcGFyc2VfZXhwcl9jeHQgKmNvbnRleHQpCiAJ
CQkJCQkJCQkJICAgbm9kZS0+cmVzdWx0dHlwbW9kKSk7CiB9CiAKKy8qCisgKiBEZXBhcnNlIGEg
QXJyYXlDb2VyY2VFeHByIChhcnJheS10eXBlIGNvbnZlcnNpb24pIG5vZGUuCisgKi8KK3N0YXRp
YyB2b2lkCitkZXBhcnNlQXJyYXlDb2VyY2VFeHByKEFycmF5Q29lcmNlRXhwciAqbm9kZSwgZGVw
YXJzZV9leHByX2N4dCAqY29udGV4dCkKK3sKKwlkZXBhcnNlRXhwcihub2RlLT5hcmcsIGNvbnRl
eHQpOworCisJLyoKKwkgKiBObyBkaWZmZXJlbmNlIGhvdyB0byBkZXBhcnNlIGV4cGxpY2l0IGNh
c3QsIGJ1dCBpZiB3ZSBvbWl0IGltcGxpY2l0CisJICogY2FzdCBpbiB0aGUgcXVlcnksIGl0J2xs
IGJlIG1vcmUgdXNlci1mcmllbmRseQorCSAqLworCWlmIChub2RlLT5jb2VyY2Vmb3JtYXQgIT0g
Q09FUkNFX0lNUExJQ0lUX0NBU1QpCisJCWFwcGVuZFN0cmluZ0luZm8oY29udGV4dC0+YnVmLCAi
OjolcyIsCisJCQkJCQkgZGVwYXJzZV90eXBlX25hbWUobm9kZS0+cmVzdWx0dHlwZSwKKwkJCQkJ
CQkJCQkgICBub2RlLT5yZXN1bHR0eXBtb2QpKTsKK30KKwogLyoKICAqIERlcGFyc2UgYSBCb29s
RXhwciBub2RlLgogICovCmRpZmYgLS1naXQgYS9jb250cmliL3Bvc3RncmVzX2Zkdy9leHBlY3Rl
ZC9wb3N0Z3Jlc19mZHcub3V0IGIvY29udHJpYi9wb3N0Z3Jlc19mZHcvZXhwZWN0ZWQvcG9zdGdy
ZXNfZmR3Lm91dAppbmRleCBmMmJjZDZhYTk4Yy4uNTVhOGFjOTAyMGUgMTAwNjQ0Ci0tLSBhL2Nv
bnRyaWIvcG9zdGdyZXNfZmR3L2V4cGVjdGVkL3Bvc3RncmVzX2Zkdy5vdXQKKysrIGIvY29udHJp
Yi9wb3N0Z3Jlc19mZHcvZXhwZWN0ZWQvcG9zdGdyZXNfZmR3Lm91dApAQCAtMTE2OSw2ICsxMTY5
LDI3IEBAIFNFTEVDVCAqIEZST00gZnQxIFdIRVJFIENBU0UgYzMgQ09MTEFURSAiQyIgV0hFTiBj
NiBUSEVOIHRydWUgRUxTRSBjMyA8ICdiYXInIEVOCiAgICBSZW1vdGUgU1FMOiBTRUxFQ1QgIkMg
MSIsIGMyLCBjMywgYzQsIGM1LCBjNiwgYzcsIGM4IEZST00gIlMgMSIuIlQgMSIKICg0IHJvd3Mp
CiAKKy0tIFRlc3QgYXJyYXkgdHlwZSBjb252ZXJzaW9uIHB1c2hkb3duCitTRVQgcGxhbl9jYWNo
ZV9tb2RlID0gZm9yY2VfZ2VuZXJpY19wbGFuOworUFJFUEFSRSBzKHZhcmNoYXJbXSkgQVMgU0VM
RUNUIGNvdW50KCopIEZST00gZnQyIFdIRVJFIGM2ID0gQU5ZICgkMSk7CitFWFBMQUlOIChWRVJC
T1NFLCBDT1NUUyBPRkYpCitFWEVDVVRFIHMoQVJSQVlbJzEnLCcyJ10pOworICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0KKyBGb3JlaWduIFNjYW4KKyAgIE91dHB1dDogKGNvdW50KCopKQorICAgUmVsYXRpb25z
OiBBZ2dyZWdhdGUgb24gKHB1YmxpYy5mdDIpCisgICBSZW1vdGUgU1FMOiBTRUxFQ1QgY291bnQo
KikgRlJPTSAiUyAxIi4iVCAxIiBXSEVSRSAoKGM2ID0gQU5ZICgkMTo6Y2hhcmFjdGVyIHZhcnlp
bmdbXSkpKQorKDQgcm93cykKKworRVhFQ1VURSBzKEFSUkFZWycxJywnMiddKTsKKyBjb3VudCAK
Ky0tLS0tLS0KKyAgIDIwMAorKDEgcm93KQorCitERUFMTE9DQVRFIHM7CitSRVNFVCBwbGFuX2Nh
Y2hlX21vZGU7CiAtLSBhIHJlZ2NvbmZpZyBjb25zdGFudCByZWZlcnJpbmcgdG8gdGhpcyB0ZXh0
IHNlYXJjaCBjb25maWd1cmF0aW9uCiAtLSBpcyBpbml0aWFsbHkgdW5zaGlwcGFibGUKIENSRUFU
RSBURVhUIFNFQVJDSCBDT05GSUdVUkFUSU9OIHB1YmxpYy5jdXN0b21fc2VhcmNoCmRpZmYgLS1n
aXQgYS9jb250cmliL3Bvc3RncmVzX2Zkdy9zcWwvcG9zdGdyZXNfZmR3LnNxbCBiL2NvbnRyaWIv
cG9zdGdyZXNfZmR3L3NxbC9wb3N0Z3Jlc19mZHcuc3FsCmluZGV4IDM3MmZlNmRhZDE1Li4xNjRj
ZDhiODk1YSAxMDA2NDQKLS0tIGEvY29udHJpYi9wb3N0Z3Jlc19mZHcvc3FsL3Bvc3RncmVzX2Zk
dy5zcWwKKysrIGIvY29udHJpYi9wb3N0Z3Jlc19mZHcvc3FsL3Bvc3RncmVzX2Zkdy5zcWwKQEAg
LTQ1MCw2ICs0NTAsMTUgQEAgU0VMRUNUICogRlJPTSBmdDEgV0hFUkUgQ0FTRSBjMyBXSEVOIGM2
IFRIRU4gdHJ1ZSBFTFNFIGMzIDwgJ2JhcicgRU5EOwogRVhQTEFJTiAoVkVSQk9TRSwgQ09TVFMg
T0ZGKQogU0VMRUNUICogRlJPTSBmdDEgV0hFUkUgQ0FTRSBjMyBDT0xMQVRFICJDIiBXSEVOIGM2
IFRIRU4gdHJ1ZSBFTFNFIGMzIDwgJ2JhcicgRU5EOwogCistLSBUZXN0IGFycmF5IHR5cGUgY29u
dmVyc2lvbiBwdXNoZG93bgorU0VUIHBsYW5fY2FjaGVfbW9kZSA9IGZvcmNlX2dlbmVyaWNfcGxh
bjsKK1BSRVBBUkUgcyh2YXJjaGFyW10pIEFTIFNFTEVDVCBjb3VudCgqKSBGUk9NIGZ0MiBXSEVS
RSBjNiA9IEFOWSAoJDEpOworRVhQTEFJTiAoVkVSQk9TRSwgQ09TVFMgT0ZGKQorRVhFQ1VURSBz
KEFSUkFZWycxJywnMiddKTsKK0VYRUNVVEUgcyhBUlJBWVsnMScsJzInXSk7CitERUFMTE9DQVRF
IHM7CitSRVNFVCBwbGFuX2NhY2hlX21vZGU7CisKIC0tIGEgcmVnY29uZmlnIGNvbnN0YW50IHJl
ZmVycmluZyB0byB0aGlzIHRleHQgc2VhcmNoIGNvbmZpZ3VyYXRpb24KIC0tIGlzIGluaXRpYWxs
eSB1bnNoaXBwYWJsZQogQ1JFQVRFIFRFWFQgU0VBUkNIIENPTkZJR1VSQVRJT04gcHVibGljLmN1
c3RvbV9zZWFyY2gKLS0gCjIuNDMuMAoK
--0000000000004eb5430639fed7a4--