Thread: "A transaction cannot be ended inside a block with exception handlers."
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;
------------------------------------------------------------
Re: "A transaction cannot be ended inside a block with exception handlers."
From
"David G. Johnston"
Date:
On Fri, May 6, 2022 at 4:40 PM Bryn Llewellyn <bryn@yugabyte.com> 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.
David J.
Re: "A transaction cannot be ended inside a block with exception handlers."
From
Bryn Llewellyn
Date:
david.g.johnston@gmail.com wrote:bryn@yugabyte.com 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$;
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
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.
Bryn Llewellyn <bryn@yugabyte.com> writes:
> 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
namesand 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—likea too-wide varchar value) insert diagnostic info into an incident log table and return an "unexpected error"
application-specificcode 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
impossibleto 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
fromthe 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.
regards, tom lane
Re: "A transaction cannot be ended inside a block with exception handlers."
From
Bryn Llewellyn
Date:
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.
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.
Your point about a certain class of server side error is well taken. (In Oracle Database, at least, errors like that cannot be handled in an exception block. They inevitably escape to the client. Is it the same in PG?
But I'm not convinced by this "what-about-ism" argument that it's pointless to handle those errors that allow it so that hackers get only the bare minimum information on things like schema-object names and the like. The more that hackers know about a system's internals, the better are their chances of doing evil.
You said "it's only a problem if you use deferred constraints, and that's not a necessary feature". My example was contrived. But some requirements (like entity level constraints) require commit-time checking. Like, say, a department must have just one or two staff whose job is 'Admin'. (This is presumably why the feature exists.) Using the serializable isolation level is another possible approach. But doing that can also lead to commit-time errors.
Is there really no sympathy for what I want to achieve?
Re: "A transaction cannot be ended inside a block with exception handlers."
From
"David G. Johnston"
Date:
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;
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;
David J.
Re: "A transaction cannot be ended inside a block with exception handlers."
From
Bryn Llewellyn
Date:
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;
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@lists.postgresql.org:
The P0004 assert_failure exception assert_failure exception seems to be unhandleable
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
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
error: 23505
error: P0001