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-11-04T06:02:14Z
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, Oct 14, 2025 at 10:00 AM jian he <jian.universality@gmail.com> wrote:
> Please check the attached v8.
>
hi.

please see the attached V9 patchset.
v9-0001 to v9-0018: refactor pg_cast.castfunc entries, make it error safe.
v9-0019:  CAST(... AS ... DEFAULT def_expr ON CONVERSION ERROR)

select pc.castsource::regtype,pc.casttarget::regtype,
castfunc::regproc, pp.prosrc
from pg_cast pc join pg_proc pp on pp.oid = pc.castfunc
join pg_type pt on pt.oid = castsource
join pg_type pt1 on pt1.oid = casttarget
and pc.castfunc > 0 and pt.typnamespace = 'pg_catalog'::regnamespace
and pt1.typnamespace = 'pg_catalog'::regnamespace and not pc.casterrorsafe;

 castsource | casttarget |       castfunc       |    prosrc
------------+------------+----------------------+--------------
 circle     | polygon    | pg_catalog.polygon   |
 bigint     | money      | pg_catalog.money     | int8_cash
 integer    | money      | pg_catalog.money     | int4_cash
 numeric    | money      | pg_catalog.money     | numeric_cash
 money      | numeric    | pg_catalog."numeric" | cash_numeric
(5 rows)

The above result shows type casts using functions which cannot be error safe.
Money type related casts still can not be error safe.

Cast from circle to polygon cannot be error safe because the associated cast
function (pg_cast.castfunc) is written in SQL
(see src/backend/catalog/system_functions.sql LINE 112).
It appears impossible to make SQL language functions error safe, because
fmgr_sql ignores fcinfo->context.


eval_const_expressions cannot be error safe, so we need to handle
source_expr as an UNKNOWN constant in an error safe beforehand.
For example, we need handle ('1' AS DATE) in an error safe way
for
SELECT CAST('1' AS date  DEFAULT '2011-01-01' ON ERROR);

Since we must handle the source_expr when it is an UNKNOWN constant in an
error safe way, we can apply the same handling when source_expr is a
Const whose type is not UNKNOWN.
For example:
SELECT CAST('[(1,2),(3,4)]'::path AS polygon DEFAULT NULL ON CONVERSION ERROR);

If source_expr is a Const and the cast expression is a FuncExpr, we can be
certain that all arguments (FuncExpr->args) are also Const; see the function
chain coerce_to_target_type → coerce_type → build_coercion_expression.

We don’t need to worry about deparsing the expression because struct
SafeTypeCastExpr includes source_expr, cast_expr, and default_expr.  Even if
cast_expr is NULL, we can still use source_expr to reconstruct the original CAST
expression.

so I introduced:
evaluate_expr_safe: error safe version of evaluate_expr
CoerceUnknownConstSafe: tests whether an UNKNOWN Const can be coerced to the
target type.