CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions
Corey Huinker <corey.huinker@gmail.com>
From: Corey Huinker <corey.huinker@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2022-12-19T22:56:37Z
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 →
-
Make cast functions to type money error safe
- b36b95640487 19 (unreleased) landed
-
Make cast function from circle to polygon error safe
- 26f9012beecf 19 (unreleased) landed
-
Make geometry cast functions error safe
- 45cdaf3665be 19 (unreleased) landed
-
Make cast functions from jsonb error safe
- 10e4d8aaf46f 19 (unreleased) landed
-
Make many cast functions error safe
- e2f289e5b9b8 19 (unreleased) landed
-
Add SQL/JSON query functions
- 6185c9737cf4 17.0 cited
-
Add soft error handling to some expression nodes
- aaaf9449ec6b 17.0 cited
Attachments
- 0001-add-OidInputFunctionCall.patch (text/x-patch) patch 0001
- 0002-add-stringTypeDatumSafe.patch (text/x-patch) patch 0002
- 0003-CAST-ON-DEFAULT-work-in-progress.patch (text/x-patch) patch 0003
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 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.
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 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.
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:
- 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.