Re: "A transaction cannot be ended inside a block with exception handlers."

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

Is there simply no way that inserts into table "t" in my example can be encapsulated in PL/pgSQL so that the error from the failing trigger can be handled rather there than escaping, raw, to the client?

Any potential solution to this problem will involve writing a stored procedure (CREATE PROCEDURE) which becomes the API layer for the application and each one probably issues a commit just prior to returning control to the calling application.  Its exception handler can transform the at-commit errors to application errors and then return control to the calling application - which then needs to handle a clean return or an application-level error return.

Eh? A stored procedure that has a commit like you suggest cannot have an exception handler like you also suggest. That's what the doc says. I quoted the sentence in my "subject" line. More carefully stated, if you have such a procedure, then any txn control statement that it executes will cause this:

ERROR:  2D000: cannot roll back while a subtransaction is active

Sure, you can handle this. But that gets you nowhere. The procedure will always end this way and never do what you wanted it to do. Sorry if my email wasn't clear.

I'll be delighted if somebody can show me working PL/pgSQL code that uses the setup that I showed and takes this as a starting point:

create procedure do_insert(good in boolean)
  language plpgsql
as $body$
begin
  case good
    when true then
      for j in 10..20 loop
        insert into t(k) values(j);
      end loop;
    when false then
      insert into t(k) values(42);
  end case;
end;
$body$;

As presented, it ends like this when it's called with "false"

ERROR:  P0004: tigger trg caused exception
CONTEXT:  PL/pgSQL function trg_fn() line 9 at ASSERT
LOCATION:  exec_stmt_assert, pl_exec.c:3918

Modify it along the lines that David suggests so that when it's called with "true", it completes silently and makes the intended changes. And when it's called with "false", it reports that it handled the P0004 error via "raise info" and then returns without error.

The rest (presumably with an OUT parameter) is easy.