Hi.
Summary of where we are for:
CAST(expr AS type DEFAULT defexpr ON CONVERSION ERROR)
A. The default expression (defexpr) is coerced to the target type with no
soft-error handling, if defexpr itself fails to convert, that's a hard error.
B. Not all functions are error-safe, CAST ... DEFAULT can never be made
bulletproof in general; in some cases we simply won't be able to fall back to
evaluating the default expression. This inconsistency is not solvable, i think.
C. At parsing stage, if the source expression (expr) is a Const of type UNKNOWN,
we try to use InputFunctionCallSafe to perform the conversion in an error-safe
manner, as mentioned in point A, we don't do this for the default expression.
D. At planning stage, eval_const_expressions tries to do constant
folding the transformed
cast expression in an error-safe manner, as mentioned before, not all function
is error safe, so this stage may encounter hard-error.
Example: SELECT CAST((1/0.0) AS numeric DEFAULT NULL ON CONVERSION ERROR).
E. We need a way to distinguish whether a given function is error-safe, since we
can't simply assume every function will be evaluated safely, that would a bad
user-experience.
F. A subexpression within the source expression may force error-safe evaluation
even where we'd rather not: we can't evaluate some subexpressions in an
error-safe manner while others aren't, once part of the expression requires it,
the rest ends up dragged into the same mode.
Example:
CREATE TABLE t AS SELECT 'bar' as b;
SELECT CAST(CAST('b' AS int DEFAULT b ON CONVERSION ERROR) AS int2
DEFAULT -1 ON CONVERSION ERROR)
FROM t;
SELECT CAST('b' AS int DEFAULT b ON CONVERSION ERROR) from t;
-------------------------------------------------------------------------------------------------------
To address point E: distinguishing whether an expression is error-safe-capable.
We first tried using CREATE CAST WITH [SAFE] FUNCTION in [1]. Then in
[2], Henson Choi pointed
out that being error safe or not is a property of the function itself,
not related to catalog pg_cast, and indeed it is.
Also storing this property on pg_proc rather than pg_cast is future-proof;
Since we eventually want to fully support
CAST(expr AS type FORMAT 'fmt' DEFAULT defexpr ON CONVERSION ERROR).
Looking at the CREATE FUNCTION synopsis, we already have
PARALLEL { UNSAFE | RESTRICTED | SAFE }
Adding a bare SAFE keyword (e.g. CREATE FUNCTION ... SAFE) would read
oddly alongside that.
That led me to
ERROR { SAFE | UNSAFE }
backed by a new pg_proc column, proerrorsafe, which is default to false; every
build-in pg_cast.castfunc function is marked proerrorsafe => true.
This works because error-safe evaluation either via CoerceViaIO or a
FuncExpr. (ArrayCoerce's element expression is likewise one of those two).
For CoerceViaIO we have EEOP_IOCOERCE_SAFE, which currently just assumes
unconditionally that the input function has been refactored to be error-safe.
pg_proc.proerrorsafe tells us check whether a given function is
error-safe-capable, instead of unconditionally assuming it.
------------------------------------------------------------------------------------------------------------
Summary of the attachment:
1.v31-0001-error-safe-for-casting-text-to-other-types-per-pg_cast.patch
Same as before, but the bug menion in [3] is resolved.
2. v31-0002-introduce-pg_proc.proerrorsafe.patch
This is for distinguishing whether a function is error-safe capable.
3. v31-0003-CAST-expr-AS-newtype-DEFAULT-expr-ON-CONVERSION-ERROR.patch
Implement CAST(expr AS newtype DEFAULT expr ON CONVERSION ERROR).
Using pg_proc.proerrorsafe flag in 0002. If a function's proerrrorsafe
is false, then it cannot participate in CAST DEFAULT.
[1] https://www.postgresql.org/message-id/CACJufxFEzD3mqc%2BMDpgzvdt%2B4Azbn2pF6TWW%3DdSCqSK7OHoL6A%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAAAe_zDuTTbDGy_OPrcw3DEHqcjMdrWoo3954v03r6HogGQ37w%40mail.gmail.com
[3] https://www.postgresql.org/message-id/CAAAe_zAi%3DPGO4j89w5Hu_Gu%2BnjKeWY9YeX0hp91qvWVMXt0igw%40mail.gmail.com
--
jian
https://www.enterprisedb.com/
