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

> david(dot)g(dot)johnston(at)gmail(dot)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(at)yugabyte(dot)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;

Thanks for your "how the development of PostgreSQL works generally" comment. I've afraid that my full time job doesn't allow me time to study the PG codebase—and, anyway, its more than thirty years since I regularly wrote C programs so I wouldn't be able now to write a patch for consideration.

To the present use-case…

I was thinking too narrowly and so I asked the wrong question (for my "initially deferred" constraint trigger example). I had carelessly assumed that such a trigger would fire only at "commit" time. But you reminded me if the "set constraints all immediate" option. Thanks.

I'd also used "assert" in my toy trigger to make it end with an error. But I've now realized that the exception that this causes is unhandleable. See my separate email to pgsql-general(at)lists(dot)postgresql(dot)org:

The P0004 assert_failure exception assert_failure exception seems to be unhandleable
https://www.postgresql.org/message-id/5A915380-789D-4448-89F4-E3F945C1549A@yugabyte.com

I re-wrote my test, with a proper "raise exception" in the trigger and with "set constraints all immediate" in the PL/pgSQL block that can cause the trigger to fire. I also re-wrote the test as a function that returns the outcome status and allows more choices pf "case statement "legs". See below for the complete, self-contained, code.

It now works as I wanted to. So thank you very much, David, for showing me the technique that I needed to get that narrow example to work.

Now to your comment "the current limitation has no workaround that I can come up with". Tom has (I think) confirmed that there is no workaround. And I fear that the "set constraints all immediate" approach is unsafe without using "serializable". But when "serializable" is used, sometimes errors don't occur until commit time. Without using "serializable", the following race condition can occur. (Imagine changing the definition of "bad" in my example to:

(select exists(select 1 from t where k = 17))
and
(select exists(select 1 from t where k = 42))

- Two concurrent sessions both issue "set constraints all immediate" from a PL/pgSQL block as the last executable statement before the top-level PL/pgSQL call completes.

- One session inserts "17" and the other inserts "42".

- Each executes the constraint test before either commits—and so the test succeeds in each session. So the net effect of these changes violates the data rule that a trigger seeks to enforce. This is really just the same race condition that's used to show why entity-level constraints cannot be safely enforced by ordinary triggers that are not set "initially deferred".

So... in the bigger picture, when I want to honor the principle of modular software construction and hide all implementation details behind a PL/pgSQL API, I still have no general solution when, for example, entity level constraints are to be enforced.

It seems that what is needed is a commit-time event trigger. Could this be feasible?

Presumably, the mechanism must be at work at commit time, down in the implementation, with "initially deferred" constraints (and no programatically issued "set constraints all immediate").

B.t.w., Oracle database doesn't have a commit-time event trigger. But it does have a materialized view flavor that you declare to be refreshed at commit time. Some programmers use that as a workaround to enforce entity-level constraints.

--------------------------------------------------------------------------------
-- The complete testcase.

create table t(k int primary key);

drop function if exists trg_fn();
create function trg_fn()
returns trigger
language plpgsql
as $body$
declare
bad constant boolean not null :=
(select exists(select 1 from t where k = 42));
begin
if bad then raise exception using
errcode = 'raise_exception',
message = '"k = 42" is illegal',
hint = 'Use a different value';
end if;
return null;
end;
$body$;

create constraint trigger trg
after insert on t
initially deferred
for each row
execute function trg_fn();

create function do_insert_outcome(mode in text)
returns text
language plpgsql
as $body$
declare
ret text not null := '';
err text not null := '';
begin
begin
case mode
when 'good data' then
for j in 10..20 loop
insert into t(k) values(j);
end loop;
when 'unq key violation' then
insert into t(k) values(15);
when 'fires trigger' then
insert into t(k) values(42);
end case;
set constraints all immediate;
ret := 'no error';
exception when others then
get stacked diagnostics err = returned_sqlstate;
ret := 'error: '||err;
end;
return ret;
end;
$body$;

\t on
\o spool.txt
select do_insert_outcome('good data');
select do_insert_outcome('unq key violation');
select do_insert_outcome('fires trigger');
\o
\t off

It produces this output (in a single-user test):

no error

error: 23505

error: P0001