"A transaction cannot be ended inside a block with exception handlers." - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | "A transaction cannot be ended inside a block with exception handlers." |
| Date | |
| Msg-id | 40EB34E2-F9CA-4D29-B0BE-6DF3D4D0FBA3@yugabyte.com Whole thread Raw |
| Responses |
Re: "A transaction cannot be ended inside a block with exception handlers."
Re: "A transaction cannot be ended inside a block with exception handlers." |
| List | pgsql-general |
The PG doc section 43.8. Transaction Management:
says "A transaction cannot be ended inside a block with exception handlers." It's easy to demonstrate the restriction by adding this just before the final "end;" in the simple example at the start of the section:
exception
when invalid_transaction_termination then
raise info 'invalid_transaction_termination caught';
when invalid_transaction_termination then
raise info 'invalid_transaction_termination caught';
The procedure now terminates with "invalid_transaction_termination caught".
The "Transaction Management" section is new, in the "PL/pgSQL - SQL Procedural Language" chapter, in Version 11. The caveat is there. And it's been there ever since. I'd been hoping that the restriction would have been lifted by version 14.
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. Such errors occur, for example, when two "serializable" sessions contend. It's easy to provoke a commit-time error in a single session demo with an "initially deferred" constraint trigger. (See the code at the end.) Another example would be an "initially deferred" FK constraint where an application code bug occasionally fails to meet the required conditions before the commit.
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?
------------------------------------------------------------
-- "initially deferred" constraint trigger demo
create table t(k int primary key);
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
-- Uncomment to observe when trg fires.
-- raise info 'trg fired';
if bad then
assert 'false', 'tigger trg caused exception';
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 trg_fn()
returns trigger
language plpgsql
as $body$
declare
bad constant boolean not null :=
(select exists(select 1 from t where k = 42));
begin
-- Uncomment to observe when trg fires.
-- raise info 'trg fired';
if bad then
assert 'false', 'tigger trg caused exception';
end if;
return null;
end;
$body$;
create constraint trigger trg
after insert on t
initially deferred
for each row
execute function trg_fn();
------------------------------------------------------------
-- Do by hand, statement by statement.
-- OK
-- Notice that "trg" fire, five times, at "commit" time.
start transaction;
insert into t(k) values(1), (2), (3), (4), (5);
commit;
-- "ERROR: tigger trg caused exception" occurs at "commit" time.
start transaction;
insert into t(k) values(42);
commit;
-- Notice that "trg" fire, five times, at "commit" time.
start transaction;
insert into t(k) values(1), (2), (3), (4), (5);
commit;
-- "ERROR: tigger trg caused exception" occurs at "commit" time.
start transaction;
insert into t(k) values(42);
commit;
------------------------------------------------------------
pgsql-general by date: