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

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
Cc: Henson Choi <assam258(at)gmail(dot)com>, Peter <peter(at)eisentraut(dot)org>, Vik Fearing <vik(at)postgresfriends(dot)org>, Amul Sul <sulamul(at)gmail(dot)com>, Kirill Reshke <reshkekirill(at)gmail(dot)com>, Isaac Morland <isaac(dot)morland(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
Date: 2026-11-21 00:00:00
Message-ID: CACJufxEq=hvv9Zm+pAK4farogaeQ1RuTw5w5US49o3dXNEKzNw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Nov 6, 2026 at 2:37 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
>>
> Patch 0005 address requiring errors in the value expression not be ignored, but also to only evaluate the default expression if the cast operation itself failed. I'm unsure that adding no_eval to eval_const_expressions_context is the right way to go about it as there might be a more established pattern for doing such things, but it does appear to resolve all the issues I had with the behavior of the new feature.
>
> Obviously we want to give this generated code a lot of scrutiny, but it at least demonstrates that the desired behavior as I see it is at least possible. Whether or not my idea of the expected behavior matches other's expectations is still up for debate, obviously.

Hi.

https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-EXPRESS-EVAL
says
"""
Furthermore, if the result of an expression can be determined by
evaluating only some parts of it, then other subexpressions might not
be evaluated at all
"""

That means it is OK to let
SELECT CAST(1/0 AS date DEFAULT NULL ON CONVERSION ERROR);
SELECT CAST(ARRAY['x', (1/0)::text] AS date[] DEFAULT NULL ON CONVERSION ERROR);

not produce
ERROR: division by zero

Because we can determine that the source expression (1/0), ('x') is
not castable to the date data type before actually evaluating the
source expression.

Regarding V5:
+SELECT CAST('1' AS int DEFAULT 1/0 ON CONVERSION ERROR);
+ SELECT CAST(ARRAY['1'] AS int[] DEFAULT ('{' || (1/0) || '}')::int[]
ON CONVERSION ERROR);

I agree it is OK for the above 2 not produce ERROR: division by zero
------------------------------
+SELECT CAST(1/0 AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST((('abc'::text)::integer) AS date DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST((1/(g-g)) AS date DEFAULT NULL ON CONVERSION ERROR) FROM
generate_series(1,1) g;
+SELECT CAST(ARRAY['x', (1/0)::text] AS date[] DEFAULT NULL ON
CONVERSION ERROR);
+SELECT CAST(ARRAY['x', (1/(g-g))::text] AS date[] DEFAULT NULL ON
CONVERSION ERROR) FROM generate_series(1,1) g;

Since the source expression cannot be converted to the target type in
all the above cases, the result is always the DEFAULT.
So here, I disagree with you, the above examples should just return NULL, IMHO.

-----------------------------
https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-EXPRESS-EVAL
says "The order of evaluation of subexpressions is not defined".
So I want to follow this logic:
* The source expression's data type is known before it is evaluated.
* During planner constant folding:
If the source expression cannot be cast to the target type, skip
evaluating the source and return the DEFAULT.
If constant folding shows that the source expression converts
successfully to the target type, skip evaluating the DEFAULT.
* At run time, the source expression is evaluated only if a cast exists from
its type to the target type, and the DEFAULT only if the conversion fails.

What do you think?

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Zsolt Parragi 2026-11-21 00:00:28 Re: Table AM callback for logical decoding support (REPACK CONCURRENTLY)
Previous Message Robert Haas 2026-11-20 23:50:59 Re: Expanding HOT updates for expression and partial indexes