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.
On Fri, May 6, 2022 at 9:23 PM Bryn Llewellyn <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;
