MIME-Version: 1.0
References: 
 <CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com>
 <CACJufxHGxB7KPAStUHZcWhFEDXsnU06qOugh60X4hHG6J-xoUA@mail.gmail.com>
 <CADkLM=f0xruKRH+6XvCsM1EVic9y4BL4AFo3VUfvRLdu-Qp5Gg@mail.gmail.com>
 <CADkLM=e4y2bLv-6_JY5c6vC9QtweW9VfxVmVJFpcriy8Rs23TA@mail.gmail.com>
 <CACJufxGO_qmyGjBvkwhCZzm6bWiqj8iyd1dGbL+_guok6yPMCQ@mail.gmail.com>
 <CADkLM=cZ7N+f4Bj-8MiccFcTASjBB2r1_s3BD9OFbbQOwK01cg@mail.gmail.com>
 <CACJufxE7yoH42juViFvYuCQMPwXMWxDVwiugs-BW+N4oUXB=1w@mail.gmail.com>
 <CADkLM=dFZ8-mBJDkiZZ4JadnJy8zLExUoo_b1FOs2Ozrtge=gQ@mail.gmail.com>
 <CACJufxHynx40aHHUCR4wZh1qnL=X0yw6baW4Z-+vkUgT0OhjOg@mail.gmail.com>
 <CADkLM=eCPiVhGKcbNW5bUEYpozUaKNXjd_UmBVR256p2zWtYQQ@mail.gmail.com>
 <CACJufxH4LrCpL63SRYO3zVk46YdD4--VYQoBL7GmHmCm=NCAJQ@mail.gmail.com>
 <CADkLM=cmv_bmxBe8KmZd6rEgiqSdoDfHnJa63u7rdRuAsqOwDA@mail.gmail.com>
 <CACJufxGbw9iNT8QVm4QD9cPFKnDnvDBQp7AGxkoqDa-JjzVXmg@mail.gmail.com>
 <CACJufxFkLLuX1VJ-J3fppCr37PHtxkvwyd_e4zNd+VYK0v0gnQ@mail.gmail.com>
 <5ae9578e-f25e-49c5-97ab-ad27bc2050b5@eisentraut.org>
In-Reply-To: <5ae9578e-f25e-49c5-97ab-ad27bc2050b5@eisentraut.org>
From: Corey Huinker <corey.huinker@gmail.com>
Date: Tue, 24 Mar 2026 14:57:27 -0400
Message-ID: 
 <CADkLM=cuzki8H9kqmbFqkk8bk7sxNbTGGyQ14edNGLGdnkxarg@mail.gmail.com>
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User
 Functions
To: Peter Eisentraut <peter@eisentraut.org>
Cc: jian he <jian.universality@gmail.com>, Amul Sul <sulamul@gmail.com>,
	Kirill Reshke <reshkekirill@gmail.com>,
 Vik Fearing <vik@postgresfriends.org>,
	Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b30d0a064dc9b980"
Archived-At: 
 <https://www.postgresql.org/message-id/CADkLM%3Dcuzki8H9kqmbFqkk8bk7sxNbTGGyQ14edNGLGdnkxarg%40mail.gmail.com>
Precedence: bulk

--000000000000b30d0a064dc9b980
Content-Type: text/plain; charset="UTF-8"

>
> In the comment for patch 0021 you write that the function casting type
> circle to type polygon cannot be error safe, because it's a SQL
> language function.  I suggest to convert this to a C function and make
> the required changes there.
>


> About the main feature patch: I'm not a fan of the CREATE CAST
> ... WITH SAFE FUNCTION syntax.  First, the CREATE CAST syntax is part
> of the SQL standard.  It would be weird if we needed a nonstandard
> syntax to make two standard features work together.  Second, we didn't
> do this when we introduced error-safe type input functions.  There is
> a note on the CREATE TYPE man page that use of ereturn() is
> encouraged, and then we left it to extension authors to do the right
> thing.  (And we should now put a similar note on the CREATE CAST man
> page.)  And third, requiring this would require a lot of churn in all
> affected extensions, requiring new extension SQL files and forcing
> upgrades.  The changes you did in patch 0023 don't do this correctly,
> for example.


This leaves us in a tricky situation if we can't tell ahead of time if a
cast function really can handle soft errors. We could just allow any and
all (CAST ... ON ERROR) calls even if the casting function can't actually
return soft errors. That's unfortunate, but it might be the simplest and
best way forward given that the alternative is to raise an error at planner
time guaranteeing a query failure when the values presented might not have
triggered the conversion errors.


> Tactical suggestion: Add an SQL-callable function, say,
> pg_cast_conversion_succeeds(srcvalue, desttype) that checks whether
> the cast would succeed.  This would be similar to the
> pg_input_is_valid() function that we added to test the type input
> functions.  (I did not call my proposal pg_cast_is_valid() because
> that might indicate merely that a casting path exists.)  With that,
> the higher-level functionality can be constructed by hand (CASE WHEN
> pg_cast_conversion_succeeds(...) THEN CAST(...) ELSE 'default value'
> END).  And then we can later work on building out the higher-level
> functionality and make more cast functions error safe.


You're describing the the "x IS CASTABLE AS y" syntax proposed in the same
standards proposal document :)

--000000000000b30d0a064dc9b980
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">In the comment for patch 0021 you =
write that the function casting type<br>
circle to type polygon cannot be error safe, because it&#39;s a SQL<br>
language function.=C2=A0 I suggest to convert this to a C function and make=
 <br>
the required changes there.<br></blockquote><div></div><div><br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">
<br>
About the main feature patch: I&#39;m not a fan of the CREATE CAST<br>
... WITH SAFE FUNCTION syntax.=C2=A0 First, the CREATE CAST syntax is part<=
br>
of the SQL standard.=C2=A0 It would be weird if we needed a nonstandard<br>
syntax to make two standard features work together.=C2=A0 Second, we didn&#=
39;t<br>
do this when we introduced error-safe type input functions.=C2=A0 There is<=
br>
a note on the CREATE TYPE man page that use of ereturn() is<br>
encouraged, and then we left it to extension authors to do the right<br>
thing.=C2=A0 (And we should now put a similar note on the CREATE CAST man<b=
r>
page.)=C2=A0 And third, requiring this would require a lot of churn in all<=
br>
affected extensions, requiring new extension SQL files and forcing<br>
upgrades.=C2=A0 The changes you did in patch 0023 don&#39;t do this correct=
ly, <br>
for example.</blockquote><div><br></div><div>This leaves us in a tricky sit=
uation if we can&#39;t tell ahead of time if a cast function really can han=
dle soft errors. We could just allow any and all=C2=A0(CAST ... ON ERROR) c=
alls even if the casting function can&#39;t actually return soft errors. Th=
at&#39;s unfortunate, but it might be the simplest and best way forward giv=
en that the alternative is to raise an error at planner time guaranteeing a=
 query failure when the values presented might not have triggered the conve=
rsion errors.</div><div></div><div></div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
Tactical suggestion: Add an SQL-callable function, say,<br>
pg_cast_conversion_succeeds(srcvalue, desttype) that checks whether<br>
the cast would succeed.=C2=A0 This would be similar to the<br>
pg_input_is_valid() function that we added to test the type input<br>
functions.=C2=A0 (I did not call my proposal pg_cast_is_valid() because<br>
that might indicate merely that a casting path exists.)=C2=A0 With that,<br=
>
the higher-level functionality can be constructed by hand (CASE WHEN<br>
pg_cast_conversion_succeeds(...) THEN CAST(...) ELSE &#39;default value&#39=
;<br>
END).=C2=A0 And then we can later work on building out the higher-level<br>
functionality and make more cast functions error safe.</blockquote><div><br=
></div><div>You&#39;re describing the the &quot;x IS CASTABLE AS y&quot; sy=
ntax proposed in the same standards proposal document :)=C2=A0</div></div><=
/div>

--000000000000b30d0a064dc9b980--