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

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. I need to detect this, when I get such a text value, and then go on to do something appropriate after the block. My tests show that I get exactly the behavior that I want. It would be tedious to program the test (presumably by typecasting to a numeric value and comparing that with the result of rounding it) to avoid the "significant" performance penalty that the doc describes.

It seems to me that the PG implementation has, in a block that has an exception section, an opportunity to wait to start a subtraction until it first hits a statement whose effect could be rolled back—and, of course, not do this if it doesn't hit such a statement. I see that this would require first preparing the statement and only then, if it needs it, starting its subtransaction. But this sounds doable.

________________________________________________________________________________

The current PL/pgSQL doc, in this section:

43.6.8. Trapping Errors

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

says this:

When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. 
…
A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

and then on Stack Overflow, here

https://stackoverflow.com/questions/46409856/why-is-catching-errors-inside-a-loop-causing-performance-issues

Tom Lane said:

Yeah, it's about the overhead of setting up and ending a subtransaction. That's a fairly expensive mechanism, but we don't have anything cheaper that is able to recover from arbitrary errors.

and David G. Johnston said:

Setting up the pl/pgsql execution layer to trap "arbitrary SQL-layer exceptions" is fairly expensive. Even if the user specifies specific errors the error handling mechanism in pl/pgsql is code for generic (arbitrary) errors being given to it.

pavel.stehule@gmail.com wrote:

bryn@yugabyte.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 plpgsql
as $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?