MIME-Version: 1.0
References: 
 <CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com>
 <CAMsGm5dpfm2PHL8XZvC-JSd+UPkgx3rpReUA=G=4+rUCH+Ntcw@mail.gmail.com>
 <CADkLM=eD_S8mGhPfu5+hXXvXgR0-cxGpGd9dgPzD+nCuO7HFaQ@mail.gmail.com>
 <CACJufxHCMzrHOW=wRe8L30rMhB3sjwAv1LE928Fa7sxMu1Tx-g@mail.gmail.com>
 <04afcd1f-ed7d-4c0a-add1-50e3719ccbf9@postgresfriends.org>
 <CACJufxGRAnwJzu7nMq4ZP=yqa1Sz=qR+mR1TmY0aCDjJoJRRtg@mail.gmail.com>
 <762ae707-7fdc-43d8-a77a-3a10d12ce21d@postgresfriends.org>
 <CACJufxFy+DFpJ2e-czyCTAgSJXNFaQGWFKA4mjbW-LAMGc1YBA@mail.gmail.com>
 <CADkLM=f1Jv81=s5Ckazx3zZq=M5KoBJMJkOZux_-L+gezODCEQ@mail.gmail.com>
 <CACJufxGw_OY7K3rfG4kDb902O2guhT-wgTjTJQ=pWeVWRTHpHQ@mail.gmail.com>
In-Reply-To: 
 <CACJufxGw_OY7K3rfG4kDb902O2guhT-wgTjTJQ=pWeVWRTHpHQ@mail.gmail.com>
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 4 Aug 2025 01:09:21 -0400
Message-ID: 
 <CADkLM=cFSg3+6Sk00dLAF7Q7jnrKBk6+N5gRxT5BCxRvaGtR-g@mail.gmail.com>
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User
 Functions
To: jian he <jian.universality@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>,
 Isaac Morland <isaac.morland@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000f0670063b831dd1"
Archived-At: 
 <https://www.postgresql.org/message-id/CADkLM%3DcFSg3%2B6Sk00dLAF7Q7jnrKBk6%2BN5gRxT5BCxRvaGtR-g%40mail.gmail.com>
Precedence: bulk

--0000000000000f0670063b831dd1
Content-Type: text/plain; charset="UTF-8"

>
> so we need to handle numeric source types with fractional points with
> special care.
> currently, this applies only to numeric, float4, and float8.
> (hope this is all the corner case we need to catch...)
>

I'm fairly certain that the committers won't like us special-casing the
internal cast functions, as we would have to maintain these special cases
as new core types are added, and it still bypasses the defined cast
function for user-defined types, which could have similar issues similar to
the rounding issue.

I think the way forward here is either to:

1.  add a second function definition to CAST. The potential syntax forr a
second function gets clumsy, but might look something like this:

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]
   [
    WITH SAFE FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]
   ]

That doesn't seem right to me, it seems easier to:

2. Modify the CAST definition to indicate whether the existing cast
function has the regular function signature or a -Safe one. In cases where
a CAST has a defined function but the safe flag is turned off, we would
have to fail the query with an error like "Defined CAST function from
srctype to desttype is not error-safe".

This would involve changing the syntax of CREATE CAST by adding an option
SAFE, or ERROR SAFE, or similar:

CREATE CAST (source_type AS target_type)
    WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]

We would add a new value to pg_cast.castmethod, 's' for "safe".

We could refactor all the numeric types to use the modified functions, so
no special-case code there anymore, and it gives extension writers an
incentive to (eventually) make their own cast functions error-safe.

While method 2 seems a lot cleaner, there may be a performance regression
in the now error-safe typecast functions. If so, that might tip the balance
to having two functions defined.

--0000000000000f0670063b831dd1
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">
so we need to handle numeric source types with fractional points with<br>
special care.<br>
currently, this applies only to numeric, float4, and float8.<br>
(hope this is all the corner case we need to catch...)<br></blockquote><div=
><br></div><div>I&#39;m fairly certain that the committers won&#39;t like u=
s special-casing the internal cast functions, as we would have to maintain =
these special cases as new core types are added, and it still bypasses the =
defined cast function for user-defined types, which could have similar issu=
es similar to the rounding issue.<br><br></div><div>I think the way forward=
 here is either to:<br></div><div><br></div><div>1.=C2=A0 add a second func=
tion definition to CAST. The potential syntax forr a second function gets c=
lumsy, but might look something like this:<br><br><div style=3D"margin-left=
:40px">CREATE CAST (source_type AS target_type)<br>=C2=A0 =C2=A0 WITH FUNCT=
ION function_name [ (argument_type [, ...]) ]<br>=C2=A0 =C2=A0 [ AS ASSIGNM=
ENT | AS IMPLICIT ]<br>=C2=A0 =C2=A0[</div><div><div style=3D"margin-left:4=
0px">=C2=A0 =C2=A0 WITH SAFE FUNCTION function_name [ (argument_type [, ...=
]) ]<br>=C2=A0 =C2=A0 [ AS ASSIGNMENT | AS IMPLICIT ]<br></div><div style=
=3D"margin-left:40px">=C2=A0 =C2=A0]<br></div><div><br></div><div>That does=
n&#39;t seem right to me, it seems easier to:</div></div></div><div></div><=
div><br></div><div>2. Modify the CAST definition to indicate whether the ex=
isting cast function has the regular function signature or a -Safe one. In =
cases where a CAST has a defined function=C2=A0but the safe flag is turned =
off, we would have to fail the query with an error like &quot;Defined CAST =
function from srctype to desttype is not error-safe&quot;.</div><div><br></=
div><div>This would involve changing the syntax of CREATE CAST by adding an=
 option SAFE, or ERROR SAFE, or similar:</div><div><br><div style=3D"margin=
-left:40px">CREATE CAST (source_type AS target_type)<br>=C2=A0 =C2=A0 WITH =
[SAFE] FUNCTION function_name [ (argument_type [, ...]) ]<br>=C2=A0 =C2=A0 =
[ AS ASSIGNMENT | AS IMPLICIT ]<br></div></div><div><br></div><div>We would=
 add a new value to pg_cast.castmethod, &#39;s&#39; for &quot;safe&quot;.<b=
r><br></div><div>We could refactor all the numeric types to use the modifie=
d functions, so no special-case code there anymore, and it gives extension =
writers an incentive to (eventually) make their own cast functions error-sa=
fe.</div><div><br></div><div>While method 2 seems a lot cleaner, there may =
be a performance regression in the now error-safe typecast functions. If so=
, that might tip the balance to having two functions defined.</div><div></d=
iv></div></div>

--0000000000000f0670063b831dd1--