Обсуждение: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
Attached is my work in progress to implement the changes to the CAST() function as proposed by Vik Fearing.
This work builds upon the Error-safe User Functions work currently ongoing.
The proposed changes are as follows:
CAST(expr AS typename)
continues to behave as before.
continues to behave as before.
CAST(expr AS typename ERROR ON ERROR)
has the identical behavior as the unadorned CAST() above.
has the identical behavior as the unadorned CAST() above.
CAST(expr AS typename NULL ON ERROR)
will use error-safe functions to do the cast of expr, and will return NULL if the cast fails.
CAST(expr AS typename DEFAULT expr2 ON ERROR)
will use error-safe functions to do the cast of expr, and will return expr2 if the cast fails.
will use error-safe functions to do the cast of expr, and will return expr2 if the cast fails.
There is an additional FORMAT parameter that I have not yet implemented, my understanding is that it is largely intended for DATE/TIME field conversions, but others are certainly possible.
CAST(expr AS typename FORMAT fmt DEFAULT expr2 ON ERROR)
What is currently working:
- Any scalar expression that can be evaluated at parse time. These tests from cast.sql all currently work:
VALUES (CAST('error' AS integer));
VALUES (CAST('error' AS integer ERROR ON ERROR));
VALUES (CAST('error' AS integer NULL ON ERROR));
VALUES (CAST('error' AS integer DEFAULT 42 ON ERROR));
SELECT CAST('{123,abc,456}' AS integer[] DEFAULT '{-789}' ON ERROR) as array_test1;
- Scalar values evaluated at runtime.
VALUES (CAST('error' AS integer ERROR ON ERROR));
VALUES (CAST('error' AS integer NULL ON ERROR));
VALUES (CAST('error' AS integer DEFAULT 42 ON ERROR));
SELECT CAST('{123,abc,456}' AS integer[] DEFAULT '{-789}' ON ERROR) as array_test1;
- Scalar values evaluated at runtime.
CREATE TEMPORARY TABLE t(t text);
INSERT INTO t VALUES ('a'), ('1'), ('b'), (2);
SELECT CAST(t.t AS integer DEFAULT -1 ON ERROR) AS foo FROM t;
foo
-----
-1
1
-1
2
(4 rows)
foo
-----
-1
1
-1
2
(4 rows)
Along the way, I made a few design decisions, each of which is up for debate:
First, I created OidInputFunctionCallSafe, which is to OidInputFunctionCall what InputFunctionCallSafe is to InputFunctionCall. Given that the only place I ended up using it was stringTypeDatumSafe(), it may be possible to just move that code inside stringTypeDatumSafe.
Next, I had a need for FuncExpr, CoerceViaIO, and ArrayCoerce to all report if their expr argument failed, and if not, just past the evaluation of expr2. Rather than duplicate this logic in several places, I chose instead to modify CoalesceExpr to allow for an error-test mode in addition to its default null-test mode, and then to provide this altered node with two expressions, the first being the error-safe typecast of expr and the second being the non-error-safe typecast of expr2.
I still don't have array-to-array casts working, as the changed I would likely need to make to ArrayCoerce get somewhat invasive, so this seemed like a good time to post my work so far and solicit some feedback beyond what I've already been getting from Jeff Davis and Michael Paquier.
I've sidestepped domains as well for the time being as well as avoiding JIT issues entirely.
Next, I had a need for FuncExpr, CoerceViaIO, and ArrayCoerce to all report if their expr argument failed, and if not, just past the evaluation of expr2. Rather than duplicate this logic in several places, I chose instead to modify CoalesceExpr to allow for an error-test mode in addition to its default null-test mode, and then to provide this altered node with two expressions, the first being the error-safe typecast of expr and the second being the non-error-safe typecast of expr2.
I still don't have array-to-array casts working, as the changed I would likely need to make to ArrayCoerce get somewhat invasive, so this seemed like a good time to post my work so far and solicit some feedback beyond what I've already been getting from Jeff Davis and Michael Paquier.
I've sidestepped domains as well for the time being as well as avoiding JIT issues entirely.
No documentation is currently prepared. All but one of the regression test queries work, the one that is currently failing is:
SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON ERROR) as array_test2;
Other quirks:
SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON ERROR) as array_test2;
Other quirks:
- an unaliased CAST ON DEFAULT will return the column name of "coalesce", which internally is true, but obviously would be quite confusing to a user.
As a side observation, I noticed that the optimizer already tries to resolve expressions based on constants and to collapse expression trees where possible, which makes me wonder if the work done to do the same in transformTypeCast/ and coerce_to_target_type and coerce_type isn't also wasted.
As a side observation, I noticed that the optimizer already tries to resolve expressions based on constants and to collapse expression trees where possible, which makes me wonder if the work done to do the same in transformTypeCast/ and coerce_to_target_type and coerce_type isn't also wasted.
Вложения
Corey Huinker <corey.huinker@gmail.com> writes:
> The proposed changes are as follows:
> CAST(expr AS typename)
> continues to behave as before.
> CAST(expr AS typename ERROR ON ERROR)
> has the identical behavior as the unadorned CAST() above.
> CAST(expr AS typename NULL ON ERROR)
> will use error-safe functions to do the cast of expr, and will return
> NULL if the cast fails.
> CAST(expr AS typename DEFAULT expr2 ON ERROR)
> will use error-safe functions to do the cast of expr, and will return
> expr2 if the cast fails.
While I approve of trying to get some functionality in this area,
I'm not sure that extending CAST is a great idea, because I'm afraid
that the SQL committee will do something that conflicts with it.
If we know that they are about to standardize exactly this syntax,
where is that information available? If we don't know that,
I'd prefer to invent some kind of function or other instead of
extending the grammar.
regards, tom lane
On Tue, 20 Dec 2022 at 04:27, Corey Huinker <corey.huinker@gmail.com> wrote:
>
>
> Attached is my work in progress to implement the changes to the CAST() function as proposed by Vik Fearing.
>
> This work builds upon the Error-safe User Functions work currently ongoing.
>
> The proposed changes are as follows:
>
> CAST(expr AS typename)
> continues to behave as before.
>
> CAST(expr AS typename ERROR ON ERROR)
> has the identical behavior as the unadorned CAST() above.
>
> CAST(expr AS typename NULL ON ERROR)
> will use error-safe functions to do the cast of expr, and will return NULL if the cast fails.
>
> CAST(expr AS typename DEFAULT expr2 ON ERROR)
> will use error-safe functions to do the cast of expr, and will return expr2 if the cast fails.
>
> There is an additional FORMAT parameter that I have not yet implemented, my understanding is that it is largely
intendedfor DATE/TIME field conversions, but others are certainly possible.
> CAST(expr AS typename FORMAT fmt DEFAULT expr2 ON ERROR)
>
> What is currently working:
> - Any scalar expression that can be evaluated at parse time. These tests from cast.sql all currently work:
>
> VALUES (CAST('error' AS integer));
> VALUES (CAST('error' AS integer ERROR ON ERROR));
> VALUES (CAST('error' AS integer NULL ON ERROR));
> VALUES (CAST('error' AS integer DEFAULT 42 ON ERROR));
>
> SELECT CAST('{123,abc,456}' AS integer[] DEFAULT '{-789}' ON ERROR) as array_test1;
>
> - Scalar values evaluated at runtime.
>
> CREATE TEMPORARY TABLE t(t text);
> INSERT INTO t VALUES ('a'), ('1'), ('b'), (2);
> SELECT CAST(t.t AS integer DEFAULT -1 ON ERROR) AS foo FROM t;
> foo
> -----
> -1
> 1
> -1
> 2
> (4 rows)
>
>
> Along the way, I made a few design decisions, each of which is up for debate:
>
> First, I created OidInputFunctionCallSafe, which is to OidInputFunctionCall what InputFunctionCallSafe is to
InputFunctionCall.Given that the only place I ended up using it was stringTypeDatumSafe(), it may be possible to just
movethat code inside stringTypeDatumSafe.
>
> Next, I had a need for FuncExpr, CoerceViaIO, and ArrayCoerce to all report if their expr argument failed, and if
not,just past the evaluation of expr2. Rather than duplicate this logic in several places, I chose instead to modify
CoalesceExprto allow for an error-test mode in addition to its default null-test mode, and then to provide this altered
nodewith two expressions, the first being the error-safe typecast of expr and the second being the non-error-safe
typecastof expr2.
>
> I still don't have array-to-array casts working, as the changed I would likely need to make to ArrayCoerce get
somewhatinvasive, so this seemed like a good time to post my work so far and solicit some feedback beyond what I've
alreadybeen getting from Jeff Davis and Michael Paquier.
>
> I've sidestepped domains as well for the time being as well as avoiding JIT issues entirely.
>
> No documentation is currently prepared. All but one of the regression test queries work, the one that is currently
failingis:
>
> SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON ERROR) as array_test2;
>
> Other quirks:
> - an unaliased CAST ON DEFAULT will return the column name of "coalesce", which internally is true, but obviously
wouldbe quite confusing to a user.
>
> As a side observation, I noticed that the optimizer already tries to resolve expressions based on constants and to
collapseexpression trees where possible, which makes me wonder if the work done to do the same in transformTypeCast/
andcoerce_to_target_type and coerce_type isn't also wasted.
CFBot shows some compilation errors as in [1], please post an updated
version for the same:
[02:53:44.829] time make -s -j${BUILD_JOBS} world-bin
[02:55:41.164] llvmjit_expr.c: In function ‘llvm_compile_expr’:
[02:55:41.164] llvmjit_expr.c:928:6: error: ‘v_resnull’ undeclared
(first use in this function); did you mean ‘v_resnullp’?
[02:55:41.164] 928 | v_resnull = LLVMBuildLoad(b, v_reserrorp, "");
[02:55:41.164] | ^~~~~~~~~
[02:55:41.164] | v_resnullp
[02:55:41.164] llvmjit_expr.c:928:6: note: each undeclared identifier
is reported only once for each function it appears in
[02:55:41.164] llvmjit_expr.c:928:35: error: ‘v_reserrorp’ undeclared
(first use in this function); did you mean ‘v_reserror’?
[02:55:41.164] 928 | v_resnull = LLVMBuildLoad(b, v_reserrorp, "");
[02:55:41.164] | ^~~~~~~~~~~
[02:55:41.164] | v_reserror
[02:55:41.165] make[2]: *** [<builtin>: llvmjit_expr.o] Error 1
[02:55:41.165] make[2]: *** Waiting for unfinished jobs....
[02:55:45.495] make[1]: *** [Makefile:42: all-backend/jit/llvm-recurse] Error 2
[02:55:45.495] make: *** [GNUmakefile:21: world-bin-src-recurse] Error 2
[1] - https://cirrus-ci.com/task/6687753371385856?logs=gcc_warning#L448
Regards,
Vignesh
On 2023-01-02 Mo 10:57, Tom Lane wrote: > Corey Huinker <corey.huinker@gmail.com> writes: >> The proposed changes are as follows: >> CAST(expr AS typename) >> continues to behave as before. >> CAST(expr AS typename ERROR ON ERROR) >> has the identical behavior as the unadorned CAST() above. >> CAST(expr AS typename NULL ON ERROR) >> will use error-safe functions to do the cast of expr, and will return >> NULL if the cast fails. >> CAST(expr AS typename DEFAULT expr2 ON ERROR) >> will use error-safe functions to do the cast of expr, and will return >> expr2 if the cast fails. > While I approve of trying to get some functionality in this area, > I'm not sure that extending CAST is a great idea, because I'm afraid > that the SQL committee will do something that conflicts with it. > If we know that they are about to standardize exactly this syntax, > where is that information available? If we don't know that, > I'd prefer to invent some kind of function or other instead of > extending the grammar. +1 cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Mon, Jan 2, 2023 at 10:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Corey Huinker <corey.huinker@gmail.com> writes:
> The proposed changes are as follows:
> CAST(expr AS typename)
> continues to behave as before.
> CAST(expr AS typename ERROR ON ERROR)
> has the identical behavior as the unadorned CAST() above.
> CAST(expr AS typename NULL ON ERROR)
> will use error-safe functions to do the cast of expr, and will return
> NULL if the cast fails.
> CAST(expr AS typename DEFAULT expr2 ON ERROR)
> will use error-safe functions to do the cast of expr, and will return
> expr2 if the cast fails.
While I approve of trying to get some functionality in this area,
I'm not sure that extending CAST is a great idea, because I'm afraid
that the SQL committee will do something that conflicts with it.
If we know that they are about to standardize exactly this syntax,
where is that information available? If we don't know that,
I'd prefer to invent some kind of function or other instead of
extending the grammar.
regards, tom lane
I'm going off the spec that Vik presented in https://www.postgresql.org/message-id/f8600a3b-f697-2577-8fea-f40d3e18bea8@postgresfriends.org which is his effort to get it through the SQL committee. I was alreading thinking about how to get the SQLServer TRY_CAST() function into postgres, so this seemed like the logical next step.
While the syntax may change, the underlying infrastructure would remain basically the same: we would need the ability to detect that a typecast had failed, and replace it with the default value, and handle that at parse time, or executor time, and handle array casts where the array has the default but the underlying elements can't.
It would be simple to move the grammar changes to their own patch if that removes a barrier for people.
Corey Huinker <corey.huinker@gmail.com> writes: > On Mon, Jan 2, 2023 at 10:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> While I approve of trying to get some functionality in this area, >> I'm not sure that extending CAST is a great idea, because I'm afraid >> that the SQL committee will do something that conflicts with it. > I'm going off the spec that Vik presented in > https://www.postgresql.org/message-id/f8600a3b-f697-2577-8fea-f40d3e18bea8@postgresfriends.org > which is his effort to get it through the SQL committee. I'm pretty certain that sending something to pgsql-hackers will have exactly zero impact on the SQL committee. Is there anything actually submitted to the committee, and if so what's its status? regards, tom lane
On 1/3/23 19:14, Tom Lane wrote: > Corey Huinker <corey.huinker@gmail.com> writes: >> On Mon, Jan 2, 2023 at 10:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> While I approve of trying to get some functionality in this area, >>> I'm not sure that extending CAST is a great idea, because I'm afraid >>> that the SQL committee will do something that conflicts with it. > >> I'm going off the spec that Vik presented in >> https://www.postgresql.org/message-id/f8600a3b-f697-2577-8fea-f40d3e18bea8@postgresfriends.org >> which is his effort to get it through the SQL committee. > > I'm pretty certain that sending something to pgsql-hackers will have > exactly zero impact on the SQL committee. Is there anything actually > submitted to the committee, and if so what's its status? I have not posted my paper to the committee yet, but I plan to do so before the working group's meeting early February. Just like with posting patches here, I cannot guarantee that it will get accepted but I will be arguing for it. I don't think we should add that syntax until I do get it through the committee, just in case they change something. -- Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
> I have not posted my paper to the committee yet, but I plan to do so
> before the working group's meeting early February. Just like with
> posting patches here, I cannot guarantee that it will get accepted but I
> will be arguing for it.
> I don't think we should add that syntax until I do get it through the
> committee, just in case they change something.
Agreed. So this is something we won't be able to put into v16;
it'll have to wait till there's something solid from the committee.
regards, tom lane
Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
От
"Gregory Stark (as CFM)"
Дата:
On Tue, 3 Jan 2023 at 14:16, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Vik Fearing <vik@postgresfriends.org> writes: > > > I don't think we should add that syntax until I do get it through the > > committee, just in case they change something. > > Agreed. So this is something we won't be able to put into v16; > it'll have to wait till there's something solid from the committee. I guess I'll mark this Rejected in the CF then. Who knows when the SQL committee will look at this... -- Gregory Stark As Commitfest Manager
On Mon, 19 Dec 2022 at 17:57, Corey Huinker <corey.huinker@gmail.com> wrote:
Attached is my work in progress to implement the changes to the CAST() function as proposed by Vik Fearing.CAST(expr AS typename NULL ON ERROR)will use error-safe functions to do the cast of expr, and will return NULL if the cast fails.CAST(expr AS typename DEFAULT expr2 ON ERROR)
will use error-safe functions to do the cast of expr, and will return expr2 if the cast fails.
Is there any difference between NULL and DEFAULT NULL?
On Tue, Mar 28, 2023 at 2:53 PM Gregory Stark (as CFM) <stark.cfm@gmail.com> wrote:
On Tue, 3 Jan 2023 at 14:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Vik Fearing <vik@postgresfriends.org> writes:
>
> > I don't think we should add that syntax until I do get it through the
> > committee, just in case they change something.
>
> Agreed. So this is something we won't be able to put into v16;
> it'll have to wait till there's something solid from the committee.
I guess I'll mark this Rejected in the CF then. Who knows when the SQL
committee will look at this...
--
Gregory Stark
As Commitfest Manager
Yes, for now. I'm in touch with the pg-people on the committee and will resume work when there's something to act upon.
On Tue, Mar 28, 2023 at 3:25 PM Isaac Morland <isaac.morland@gmail.com> wrote:
On Mon, 19 Dec 2022 at 17:57, Corey Huinker <corey.huinker@gmail.com> wrote:Attached is my work in progress to implement the changes to the CAST() function as proposed by Vik Fearing.CAST(expr AS typename NULL ON ERROR)will use error-safe functions to do the cast of expr, and will return NULL if the cast fails.CAST(expr AS typename DEFAULT expr2 ON ERROR)
will use error-safe functions to do the cast of expr, and will return expr2 if the cast fails.Is there any difference between NULL and DEFAULT NULL?
What I think you're asking is: is there a difference between these two statements:
SELECT CAST(my_string AS integer NULL ON ERROR) FROM my_table;
SELECT CAST(my_string AS integer DEFAULT NULL ON ERROR) FROM my_table;
And as I understand it, the answer would be no, there is no practical difference. The first case is just a convenient shorthand, whereas the second case tees you up for a potentially complex expression. Before you ask, I believe the ON ERROR syntax could be made optional. As I implemented it, both cases create a default expression which then typecast to integer, and in both cases that expression would be a const-null, so the optimizer steps would very quickly collapse those steps into a plain old constant.
hi.
more preparation work has been committed.
1. SQL/JSON patch [1] added keyword ERROR
2. CoerceViaIo, CoerceToDomain can be evaluated error safe. see commit [2].
3. ExprState added ErrorSaveContext point, so before calling ExecInitExprRec
set valid ErrorSaveContext for ExprState->escontext we should evaluate
expression error softly.
see commit [2] also.
I only found oracle implement, [3].
Based on my reading of [4], it seems CAST(EXPRESSION AS TYPE DEFAULT
def_expr ON ERROR)
is not included in SQL:2023.
anyway, just share my POC based on the previous patch in this thread.
it will work for domain over composite, composite over domain.
example:
CREATE DOMAIN d_char3_not_null as char(3) NOT NULL;
CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int);
SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON ERROR);
--return NULL
[1]:
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/gram.y?id=6185c9737cf48c9540782d88f12bd2912d6ca1cc
[2]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
[3] https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CAST.html
[4] https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new
Вложения
On 22/07/2025 03:59, jian he wrote: > Based on my reading of [4], it seems CAST(EXPRESSION AS TYPE DEFAULT > def_expr ON ERROR) > is not included in SQL:2023. > > [4]https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new It was accepted into the standard after 2023 was released. I am the author of this change in the standard, so feel free to ask me anything you're unsure about. -- Vik Fearing
On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <vik@postgresfriends.org> wrote: > > It was accepted into the standard after 2023 was released. I am the > author of this change in the standard, so feel free to ask me anything > you're unsure about. > is the generally syntax as mentioned in this thread: CAST(source_expression AS target_type DEFAULT default_expression ON ERROR) if so, what's the restriction of default_expression?
On 22/07/2025 12:19, jian he wrote:
> On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <vik@postgresfriends.org> wrote:
>> It was accepted into the standard after 2023 was released. I am the
>> author of this change in the standard, so feel free to ask me anything
>> you're unsure about.
>>
> is the generally syntax as mentioned in this thread:
> CAST(source_expression AS target_type DEFAULT default_expression ON ERROR)
>
> if so, what's the restriction of default_expression?
The actual syntax is:
<cast specification> ::=
CAST <left paren>
<cast operand> AS <cast target>
[ FORMAT <cast template> ]
[ <cast error behavior> ON CONVERSION ERROR ]
<right paren>
"CONVERSION" is probably a noise word, but it is there because A) Oracle
wanted it there, and B) it makes sense because if the <cast error
behavior> fails, that is still a failure of the entire CAST.
The <cast error behavior> is:
<cast error behavior> ::=
ERROR
| NULL
| DEFAULT <value expression>
but I am planning on removing the NULL variant in favor of having the
<value expression> be a <contextually typed value specification>. So it
would be either ERROR ON CONVERSION ERROR (postgres's current behavior),
or DEFAULT NULL ON CONVERSION ERROR.
An example of B) above would be: CAST('five' AS INTEGER DEFAULT 'six' ON
CONVERSION ERROR). 'six' is no more an integer than 'five' is, so that
would error out because the conversion error does not happen on the
operand but on the default clause. CAST('five' AS INTEGER DEFAULT 6 ON
CONVERSION ERROR) would work.
--
Vik Fearing
On 22/07/2025 14:26, Vik Fearing wrote: > The <cast error behavior> is: > > <cast error behavior> ::= > ERROR > | NULL > | DEFAULT <value expression> > > but I am planning on removing the NULL variant in favor of having the > <value expression> be a <contextually typed value specification>. So > it would be either ERROR ON CONVERSION ERROR (postgres's current > behavior), or DEFAULT NULL ON CONVERSION ERROR. Sorry, I meant <implicitly typed value specification>. The point being that CAST(ARRAY['1', '2', 'three'] AS INTEGER ARRAY DEFAULT NULL ON CONVERSION ERROR) will give you (CAST NULL AS INTEGER ARRAY) and *not* ARRAY[1, 2, NULL]. -- Vik Fearing
On Tue, Jul 22, 2025 at 2:45 AM Vik Fearing <vik@postgresfriends.org> wrote:
On 22/07/2025 03:59, jian he wrote:
> Based on my reading of [4], it seems CAST(EXPRESSION AS TYPE DEFAULT
> def_expr ON ERROR)
> is not included in SQL:2023.
>
> [4]https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new
It was accepted into the standard after 2023 was released. I am the
author of this change in the standard, so feel free to ask me anything
you're unsure about.
That's excellent news. I was already planning on retrying this for v19, but I'll try sooner now.
On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
> The actual syntax is:
>
>
> <cast specification> ::=
> CAST <left paren>
> <cast operand> AS <cast target>
> [ FORMAT <cast template> ]
> [ <cast error behavior> ON CONVERSION ERROR ]
> <right paren>
>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
> <cast error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
>
>
> but I am planning on removing the NULL variant in favor of having the
> <value expression> be a <contextually typed value specification>. So it
> would be either ERROR ON CONVERSION ERROR (postgres's current behavior),
> or DEFAULT NULL ON CONVERSION ERROR.
>
>
> An example of B) above would be: CAST('five' AS INTEGER DEFAULT 'six' ON
> CONVERSION ERROR). 'six' is no more an integer than 'five' is, so that
> would error out because the conversion error does not happen on the
> operand but on the default clause. CAST('five' AS INTEGER DEFAULT 6 ON
> CONVERSION ERROR) would work.
>
hi.
> <cast error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
for <value expression>
I disallow it from returning a set, or using aggregate or window functions.
For example, the following three cases will fail:
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) --error
(ret_setint function is warped as (select 1 union all select 2))
for array coerce, which you already mentioned, i think the following
is what we expected.
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON
CONVERSION ERROR);
+ int4
+---------
+ {-1011}
+(1 row)
I didn't implement the [ FORMAT <cast template> ] part for now.
please check the attached regress test and tests expected result.
Вложения
On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
>
> On 22/07/2025 12:19, jian he wrote:
> > On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <vik@postgresfriends.org> wrote:
> >> It was accepted into the standard after 2023 was released. I am the
> >> author of this change in the standard, so feel free to ask me anything
> >> you're unsure about.
> >>
> > is the generally syntax as mentioned in this thread:
> > CAST(source_expression AS target_type DEFAULT default_expression ON ERROR)
> >
> > if so, what's the restriction of default_expression?
>
>
> The actual syntax is:
>
>
> <cast specification> ::=
> CAST <left paren>
> <cast operand> AS <cast target>
> [ FORMAT <cast template> ]
> [ <cast error behavior> ON CONVERSION ERROR ]
> <right paren>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
>
> <cast error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
>
>
hi.
just want to confirm my understanding of ``[ FORMAT <cast template> ]``.
SELECT CAST('2022-13-32' AS DATE FORMAT 'YYYY-MM-DD' DEFAULT NULL ON
CONVERSION ERROR);
will return NULL.
because ``SELECT to_date('2022-13-32', 'YYYY-MM-DD');``
will error out, so the above query will fall back to the DEFAULT
expression evaluation.
On 24/07/2025 03:22, jian he wrote:
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
This seems like an arbitrary restriction. Can you explain why this is necessary? Those same expressions are allowed as the <cast operand>.
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) --error
(ret_setint function is warped as (select 1 union all select 2))This makes sense to me.
for array coerce, which you already mentioned, i think the following
is what we expected.
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON
CONVERSION ERROR);
+ int4
+---------
+ {-1011}
+(1 row)Yes, that looks correct to me.
I didn't implement the [ FORMAT <cast template> ] part for now.
That is fine, since it's separate feature
please check the attached regress test and tests expected result.
Except for the weird restriction on the default value, this all looks good to me (with the usual caveat that I am not an expert in C).
Are you planning to also implement the <castable predicate>?
--
Vik Fearing
On 24/07/2025 15:44, jian he wrote:
> just want to confirm my understanding of ``[ FORMAT <cast template> ]``.
>
> SELECT CAST('2022-13-32' AS DATE FORMAT 'YYYY-MM-DD' DEFAULT NULL ON
> CONVERSION ERROR);
> will return NULL.
> because ``SELECT to_date('2022-13-32', 'YYYY-MM-DD');``
> will error out, so the above query will fall back to the DEFAULT
> expression evaluation.
That is correct. Any error produced during typecasting will fall back
to the DEFAULT value. If not supplied, the behavior is ERROR ON ERROR
as it currently is.
Any error produced while converting the DEFAULT value to the requested
type is raised as an error.
--
Vik Fearing
I didn't implement the [ FORMAT <cast template> ] part for now.
please check the attached regress test and tests expected result.
Question about this:
+/*
+ * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary expressions.
+ * We already handle CoerceViaIO, CoerceToDomain, and ArrayCoerceExpr error
+ * softly. However, FuncExpr (e.g., int84) cannot be made error-safe.
+ * In such cases, we wrap the source expression and target type information into
+ * a CoerceViaIO node instead.
+ */
+ * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary expressions.
+ * We already handle CoerceViaIO, CoerceToDomain, and ArrayCoerceExpr error
+ * softly. However, FuncExpr (e.g., int84) cannot be made error-safe.
+ * In such cases, we wrap the source expression and target type information into
+ * a CoerceViaIO node instead.
+ */
I'm not sure we _can_ just fall back to the CoerceViaIO if there is a defined cast from TypeA -> TypeB. I seem to recall there was some reason we couldn't do that, possibly to do with how it handled rounding, but I have no clear memory of it.
Aside from that, I like what you've done with making SafeTypeCastExpr be its own node type and not saddling regular typecasts with the overhead.
On Thu, Jul 31, 2025 at 3:15 AM Corey Huinker <corey.huinker@gmail.com> wrote:
>
>
> Question about this:
>
> +/*
> + * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary expressions.
> + * We already handle CoerceViaIO, CoerceToDomain, and ArrayCoerceExpr error
> + * softly. However, FuncExpr (e.g., int84) cannot be made error-safe.
> + * In such cases, we wrap the source expression and target type information into
> + * a CoerceViaIO node instead.
> + */
>
> I'm not sure we _can_ just fall back to the CoerceViaIO if there is a defined cast from TypeA -> TypeB. I seem to
recallthere was some reason we couldn't do that, possibly to do with how it handled rounding, but I have no clear
memoryof it.
>
indeed.
select ('11.1'::numeric::int);
return 11, but '11.1' string can not coerce to int 11. So in this
case, we can not use CoerceViaIO.
so we need to handle numeric source types with fractional points with
special care.
currently, this applies only to numeric, float4, and float8.
(hope this is all the corner case we need to catch...)
select castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext
from pg_cast pc
where castsource::regtype = ANY('{numeric, float4, float8}'::regtype[])
and castmethod = 'f';
only return 17 rows. one row is cast numreic to money, function numeric_cash.
numeric_cash seems more trickly to be error safe, because it will call
numeric_mul.
so I made these 16 function errors safe.
see v3-0001-make-some-numeric-cast-function-error-safe.patch
Вложения
hi. fix the regress tests failure in https://api.cirrus-ci.com/v1/artifact/task/5894868779663360/testrun/build/testrun/regress/regress/regression.diffs
Вложения
so we need to handle numeric source types with fractional points with
special care.
currently, this applies only to numeric, float4, and float8.
(hope this is all the corner case we need to catch...)
I'm fairly certain that the committers won't like us special-casing the internal cast functions, as we would have to maintain these special cases as new core types are added, and it still bypasses the defined cast function for user-defined types, which could have similar issues similar to the rounding issue.
I think the way forward here is either to:
1. add a second function definition to CAST. The potential syntax forr a second function gets clumsy, but might look something like this:
CREATE CAST (source_type AS target_type)
WITH FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
[
WITH FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
[
WITH SAFE FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
[ AS ASSIGNMENT | AS IMPLICIT ]
]
That doesn't seem right to me, it seems easier to:
2. Modify the CAST definition to indicate whether the existing cast function has the regular function signature or a -Safe one. In cases where a CAST has a defined function but the safe flag is turned off, we would have to fail the query with an error like "Defined CAST function from srctype to desttype is not error-safe".
This would involve changing the syntax of CREATE CAST by adding an option SAFE, or ERROR SAFE, or similar:
CREATE CAST (source_type AS target_type)
WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
We would add a new value to pg_cast.castmethod, 's' for "safe".
We could refactor all the numeric types to use the modified functions, so no special-case code there anymore, and it gives extension writers an incentive to (eventually) make their own cast functions error-safe.
While method 2 seems a lot cleaner, there may be a performance regression in the now error-safe typecast functions. If so, that might tip the balance to having two functions defined.
On Mon, Aug 4, 2025 at 1:09 PM Corey Huinker <corey.huinker@gmail.com> wrote:
>>
>> so we need to handle numeric source types with fractional points with
>> special care.
>> currently, this applies only to numeric, float4, and float8.
>> (hope this is all the corner case we need to catch...)
>
>
> I'm fairly certain that the committers won't like us special-casing the internal cast functions, as we would have to
maintainthese special cases as new core types are added, and it still bypasses the defined cast function for
user-definedtypes, which could have similar issues similar to the rounding issue.
>
It's not special-casing the internal cast functions.
It's how the cast being evaluated.
There are two ways: CoerceViaIO, FuncExpr.
generally if there is a pg_cast entry, postgres will use FuncExpr. but to safely
cast evaluation (DEFAULT ON CONVERSION ERROR) we can not use FuncExpr in some
cases. Because the FuncExpr associate function is not error safe.
So in v4, we try to use CoerceViaIO to evaluate the case, but it turns
out CoerceViaIO results are
not the same as FuncExpr.
one of the example is:
select ('11.1'::numeric::int);
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.
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.
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.
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).
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 situationsaccordingly (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).
Вложения
- v6-0016-error-safe-for-casting-character-varying-to-other-types-p.patch
- v6-0015-error-safe-for-casting-timestamp-to-other-types-per-pg_ca.patch
- v6-0014-error-safe-for-casting-timestamptz-to-other-types-per-pg_.patch
- v6-0017-make-ArrayCoerceExpr-error-safe.patch
- v6-0018-CAST-expr-AS-newtype-DEFAULT-ON-ERROR.patch
- v6-0010-error-safe-for-casting-text-to-other-types-per-pg_cast.patch
- v6-0012-error-safe-for-casting-interval-to-other-types-per-pg_cas.patch
- v6-0013-error-safe-for-casting-macaddr8-to-other-types-per-pg_cas.patch
- v6-0009-error-safe-for-casting-date-to-other-types-per-pg_cast.patch
- v6-0011-error-safe-for-casting-inet-to-other-types-per-pg_cast.patch
- v6-0008-error-safe-for-casting-jsonb-to-other-types-per-pg_cast.patch
- v6-0006-error-safe-for-casting-float4-to-other-types-per-pg_cast.patch
- v6-0007-error-safe-for-casting-float8-to-other-types-per-pg_cast.patch
- v6-0005-error-safe-for-casting-numeric-to-other-types-per-pg_cast.patch
- v6-0004-error-safe-for-casting-bigint-to-other-types-per-pg_cast.patch
- v6-0003-error-safe-for-casting-integer-to-other-types-per-pg_cast.patch
- v6-0001-error-safe-for-casting-bytea-to-other-types-per-pg_cast.patch
- v6-0002-error-safe-for-casting-character-to-other-types-per-pg_ca.patch