Horton

Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>, Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-08-11T06:18:44Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Make cast functions to type money error safe

  2. Make cast function from circle to polygon error safe

  3. Make geometry cast functions error safe

  4. Make cast functions from jsonb error safe

  5. Make many cast functions error safe

  6. Add SQL/JSON query functions

  7. Add soft error handling to some expression nodes

Attachments

On Tue, Aug 5, 2025 at 12:10 PM Corey Huinker <corey.huinker@gmail.com> wrote:
>>
>> In the end, it seems we need to make all these functions in the below
>> query error safe.
>> select castsource::regtype, casttarget::regtype, castfunc,
>> castcontext,castmethod, pp.prosrc, pp.proname from pg_cast pc join pg_proc pp on
>> pp.oid = pc.castfunc and pc.castfunc > 0
>> order by castsource::regtype;
>> It's a lot of work, but seems doable, after playing around with it.
>
> It is do-able. But that's just the cast functions that are part of core postgres.
>
hi.
it's doable for most of the data types.
but I found geometric type related cast function refactoring to error
safe is quite challenging,
so I skip that part refactoring.

>>
>> I don't think we need to change the pg_cast catalog entry,
>> we just need to make these function (pg_cast.castmethod) errors safe.
>
> That would break any user-defined cast functions that were not also error safe, which is to say all of them.
>
> We need a way for user-defined cast functions to indicate whether or not they are error safe, and handle both situations accordingly (i.e. fail a CAST ON DEFAULT when the user-defined cast is not error-safe).
>

I understand what you mean now.
CREATE CAST can use built-in functions too, we have no way to check
whether these CREATE CAST
associated functions are error safe or not.
for example:
CREATE CAST (jsonpath  AS bytea) WITH FUNCTION jsonpath_send (jsonpath
) AS ASSIGNMENT;
select '$'::jsonpath::bytea;

To avoid this situation, we have to add a new column to pg_cast to
indicate whether a cast function is error-safe.
It's unlikely a pg_cast entry has two functions, one is error safe, one is not,
adding pg_cast.casterrorsafefunc would not be that appropriate.

so I choose pg_cast.casterrorsafe would be fine.
pg_cast.casterrorsafe true means castfunc function is error safe, we
can use it as safe cast evaluation
(CAST... DEFAULT defexpr ON CONVERSION ERROR)

please check the attached V6 script:

v6-0001 to v6-0016 is about making existing pg_cast.castfunc function
error safe.
(commit message have associated query to explain the refactoring, as mentioned
above, geometric and money associated type not refactored yet)

v6-0017-make-ArrayCoerceExpr-error-safe.patch
v6-0018-CAST-expr-AS-newtype-DEFAULT-ON-ERROR.patch
is about (CAST... DEFAULT defexpr ON CONVERSION ERROR).