Обсуждение: "A transaction cannot be ended inside a block with exception handlers."

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com wrote:

I want to demonstrate how to meet this requirement:

«
Encapsulate each business function in a user-defined subprogram that hides all the implementation details like table names and the SQL statements that manipulate their contents so the they cannot be seen using SQL issued from the client. Further, don't allow raw errors to escape to the client. Rather, if an expected error occurs (like a unique key violation), then report this as an application-specific code that translates to, for example, "This nickname is already taken. Choose a different one." And if an "others" error occurs (typically because the programmer forgot to cater for it—like a too-wide varchar value) insert diagnostic info into an incident log table and return an "unexpected error" application-specific code together with the incident ID so that it can be reported to Support.
»

I've written proof-of-concept code that shows how to meet this requirement for most scenarios. But it seems to be impossible to meet the requirement for errors that occur at commit time.

So ... avoid those? It seems like it's only a problem if you use deferred constraints, and that's not a necessary feature.

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?

If you want a strict view of that you probably need to be doing the encapsulation on the client side. There's nothing you can do on the server side that would prevent, say, network-connection failures "escaping" to the client.  And that's actually one of the harder cases to deal with: if the connection drops just after you issue COMMIT, you can't tell whether the transaction got committed.

david.g.johnston@gmail.com wrote:

I do understand better now and indeed the current limitation has no workaround that I can come up with. I was hoping maybe subblocks would work but its pretty clear cut that to catch an error at the commit command you must catch it within a block and the commit error will be raised first.

bryn@yugabyte.com wrote:

I'll take this to mean that there is no plan for PG ever to allow txn control in a PL/pgSQL block that has an exception handler. Please tell me if I misunderstood.

You misunderstand how the development of PostgreSQL works generally. But, I suppose as a practical matter if you aren't going to spearhead the change you might as well assume it will not be possible until it is.
 

Is there really no sympathy for what I want to achieve?

I personally have sympathy, and if you submitted a patch to improve matters here I don't see anyone saying that it would be unwanted.

As for the circular dependency breaking use of deferred constraints (or your explicitly deferred triggers), you have the SET CONSTRAINTS ALL IMMEDIATE command:

postgres=# call do_insert(false);
ERROR:  tigger trg caused exception
CONTEXT:  PL/pgSQL function trg_fn() line 9 at ASSERT
SQL statement "SET CONSTRAINTS ALL IMMEDIATE"
PL/pgSQL function do_insert(boolean) line 12 at SQL statement
postgres=# create or replace procedure do_insert(good in boolean)
  language plpgsql
as $body$
begin
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;
SET CONSTRAINTS ALL IMMEDIATE;
commit;
end;
exception
when invalid_transaction_termination then
raise exception 'caught invalid';
when OTHERS then
raise notice 'others - ok';
commit;
end;