Re: "A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one"

po 13. 6. 2022 v 21:02 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> *pavel(dot)stehule(at)gmail(dot)com <pavel(dot)stehule(at)gmail(dot)com> wrote:*
>
> *bryn(at)yugabyte(dot)com <bryn(at)yugabyte(dot)com> napsal:*
>
> Does the “Tip” call-out box, from which the “Subject” here is copied, and
> the larger story that I copied below, apply even when the executable
> section of the block statement in question does nothing at all that could
> be rolled back?
>
> This is my block:
>
>
>
>
>
>
> *begin year_as_int := year_as_text;exception when
> invalid_text_representation then bad_integer := true;end;*
>
> The typecast to integer will cause an error if the input text does not
> represent an integer.
>
>
> The block is a PLpgSQL statement (internally it is not just syntax) - and
> a safepoint is created before execution of any statement inside the block,
> when the block has EXCEPTION parts. PL/pgSQL is an AST interpreter. It
> knows nothing about statements inside the block. The lazy implementation
> theoretically can be possible, but why? Any statement can raise an
> exception. There is not any evidence what statements are safe and what not.
>
>
> Thanks for the quick response. I'll take this to mean that the present
> behavior will never change—in spite of your:
>
> The lazy implementation [that Bryn sketched] theoretically can be possible
>
>
> You then said:
>
> but why?
>
>
> OK, so I'm obliged to answer.
>
> Because SQL rests on the principle that you just say *what* you want but
> not *how*. Here, I want to cast my string, which putatively represents an
> integer, to an "int" value. The text comes from the outside world, and what
> is meant to be "42017" might arrive as "42O17". Or, might arrive properly,
> as "42000e-03".
>
> Consider this:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *create function is_int_nn(t in text) returns boolean language plpgsqlas
> $body$declare ok boolean not null := (t is not null);begin if ok then
> -- Placeholder naïve REGEXPR test. ok := (t != '') and not (t ~
> 'x'); end if; if ok then declare n constant numeric not null :=
> t; r constant numeric not null := round(n); begin ok := (r =
> n); end; end if; return ok;end;$body$;select (select is_int_nn(null
> )::text) as test_1, (select is_int_nn('' )::text) as
> test_2, (select is_int_nn('42000x-04')::text) as test_3, (select
> is_int_nn('42000e-04')::text) as test_4, (select
> is_int_nn('42000e-03')::text) as test_5;*
>
> Neither the design nor the implementation of the code is by any means
> finished yet. And this holds, therefore, for the tests too. So this is a
> loud denial of « just say *what* you want ».
>
> You might argue that any junior programmer could manage the complete
> exercise in a morning. But then somebody else has to review it. And it's
> another artifact to be managed. Generic utilities like this always present
> a challenge when they need to be used in more than one distinct
> application. You need to invent a "single source of truth" scheme.
>
> Compare everything that "function is_int_nn(t in text)" implies with the
> block that I showed above.
>
> Oracle Database 12c Release 2 (and later) has a validate_conversion()
> built-in.
>
> https://docs.oracle.com/en/database/oracle/oracle-
> database/19/sqlrf/VALIDATE_CONVERSION.html#GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD
>
> This arrived in (some time around) 2017.
>
> *Is there any chance that PG might ship an equivalent*?
>

I remember a long and still not closed discussion about fault tolerant copy
implementation. The problem is a lot of possibly redundant code for
exception safe input functions, if I remember well. And it is not enough
for fault tolerant copy still. Maybe it needs some refactoring of the
PostgreSQL exceptions handling system to be able to handle some exceptions
that come from a non-storage engine without the necessity to use
safepoints. I have no idea if somebody is working on this issue now, but I
don't expect so it is easy to fix it. Maybe a more probable fix can be to
reduce an overhead of savepoints. This issue is more complex than can be
visible from user perspective - and the complexity is based on how pg has
implemented exceptions.

Regards

Pavel