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

Hi,

In subquery_planner -> preprocess_expression ->
eval_const_expressions, we attempt
to evaluate certain constant expressions in an error-safe manner by introducing
an ErrorSaveContext in eval_const_expressions_context. However, this may
introduce consistency issues, since not all functions are error-safe.

Consider the following two queries:
SELECT CAST(65536 AS int2 DEFAULT NULL ON CONVERSION ERROR);
SELECT CAST(65536/0 AS int2 DEFAULT NULL ON CONVERSION ERROR);
ERROR:  division by zero

The first query returns NULL, while the second throws an error. Should we accept
this inconsistency, or make the first query error out too?
Similarly, should we reject uncastable CASTs, like `CAST(NULL::date AS int2)`?

More broadly, the patch involved significant effort (refactoring) in
parsing (transformTypeCast),
planning (eval_const_expressions) to ensure expressions do not fail.
These efforts cannot ensure that all operations are error-safe,
therefore there will be consistency issues.
----------------------------------------------------------------
[1] explains why SQL-language functions are inherently not error-safe.

This raises the question: how should we handle user-defined cast functions in
CAST(expr AS target_type DEFAULT defexpr ON CONVERSION ERROR)?

There are three options:
Option 1: Disallow them entirely.
Reject any cast expression backed by a user-defined function at parse time.

Option 2: Allow them with documented caveats.
Permit user-defined cast functions, but explicitly document the limitations:
- SQL-language functions are inherently incapable of error-safe execution.
- C-language functions must ensure that all their subroutines are also
error-safe.
- It is the user's responsibility to ensure the cast function is error-safe; if
it is not, errors will propagate as normal rather than being caught by the
DEFAULT clause.

Option 2 means that CAST(expr AS target_type DEFAULT defexpr ON
CONVERSION ERROR) does not check if the underlying cast function
actually supports error-safe operations.

Option 3: Disallow SQL-language functions, but allow C-language and
Internal-language functions.
Also document that you must ensure the C function is error-safe if you
want it to catch the error.


Currently, we are using Option 1.
Anyway, I rebased the patch and added comments in a few places that I
realized were necessary in hindsight.

[1]: https://postgr.es/m/CACJufxHM2e3DQmbRdDZvWyG3ZCLyOg6XFifvOz_TGy1tGw7NHw@mail.gmail.com



--
jian
https://www.enterprisedb.com/