The P0004 assert_failure exception assert_failure exception seems to be unhandleable
I just stumbled on the fact that the "assert_failure" exception seems to be
unhandleable. My test is at the end.
Is this intended?
I looked at the section:
«
43.9.2. Checking Assertions
https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-ASSERT
»
It says this:
«
Note that ASSERT is meant for detecting program bugs, not for reporting
ordinary error conditions. Use the RAISE statement, described above, for that.
»
But it takes quite a stretch of the imagination to infer that this means that
the "assert_failure" exception cannot be handled.
B.t.w. this (in the same "43.9. Errors and Messages" chapter) looks like a typo:
«
If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command,
the default is to use ERRCODE_RAISE_EXCEPTION (P0001).
»
The spelling "errcode_raise_exception()" makes it look like a built-in
function. I believe that this is meant:
«
If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command,
the outcome is as if this:
ERRCODE = "RAISE_EXCEPTION"
or this:
ERRCODE = "P0001"
was used
»
--
-- The test
create function demo_outcome(which in text)
returns text
language plpgsql
as $body$
declare
err text not null := '';
msg text not null := '';
hint text not null := '';
n int not null := 0;
begin
case which
when 'OK' then
n := 42;
when 'null_value_not_allowed' then
n := null;
when 'raise_exception' then
raise exception using
errcode = 'raise_exception',
message = 'U1234: Not allowed!',
hint = 'Do something else!';
when 'assert_failure' then
assert false, 'Assert failed';
end case;
return 'no error';
exception when others then
get stacked diagnostics
err = returned_sqlstate,
msg = message_text,
hint = pg_exception_hint;
return 'Handled: '||err||' | '||msg||' | '||hint;
end;
$body$;
\set VERBOSITY verbose
\t on
\o spool.txt
select demo_outcome('OK');
select demo_outcome('null_value_not_allowed');
select demo_outcome('raise_exception');
\o
\t off
It outputs this to "spool.txt".
no error
Handled: 22004 | null value cannot be assigned to variable "n" declared NOT
NULL |
Handled: P0001 | U1234: Not allowed! | Do something else!
But doing this:
select demo_outcome('assert_failure');
causes this outcome:
ERROR: P0004: Assert failed
CONTEXT: PL/pgSQL function demo_outcome(text) line 22 at ASSERT
LOCATION: exec_stmt_assert, pl_exec.c:3918
Re: The P0004 assert_failure exception assert_failure exception seems to be unhandleable
On Sunday, May 8, 2022, Bryn Llewellyn wrote: > > « > Note that ASSERT is meant for detecting program bugs, not for reporting > ordinary error conditions. Use the RAISE statement, described above, > for that. > » > > But it takes quite a stretch of the imagination to infer that this means > that the "assert_failure" exception cannot be handled. > > Agreed. But as the pl/pgsql section “trapping errors” notes: “The special condition name OTHERS matches every error type except QUERY_CANCELED and ASSERT_FAILURE. (It is possible, but often unwise, to trap those two error types by name.)” i.e., you must trap it explicitly, not as part of others. > « > If no condition name nor SQLSTATE is specified in a RAISE > EXCEPTION command, the default is to use ERRCODE_RAISE_EXCEPTION (P0001). > » > > The spelling "errcode_raise_exception()" makes it look like a built-in > function. > > The fix I’d do is remove the “ERRCODE_” from the front of the name since that is an internal symbol that probably doesn’t even work in user code; the actual condition name is just the “raise_exception” part. That P0001 is simply the SQLSTATE for that name is perfectly clear to me and doesn’t warrant the verbosity of the proposal to avoid. David J.
Re: "A transaction cannot be ended inside a block with exception handlers."
> david.g.johns...@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. > >> b...@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 https://www.postgresql.org/message-id/5a915380-789d-4448-89f4-e3f945c15...@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 de
Re: The P0004 assert_failure exception assert_failure exception seems to be unhandleable
> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> Note that ASSERT is meant for detecting program bugs, not for reporting >> ordinary error conditions. Use the RAISE statement, described above, for >> that. >> » >> >> But it takes quite a stretch of the imagination to infer that this means >> that the "assert_failure" exception cannot be handled. > > Agreed. But as the pl/pgsql section “trapping errors” notes: > > “The special condition name OTHERS matches every error type except > QUERY_CANCELED and ASSERT_FAILURE. (It is possible, but often unwise, to trap > those two error types by name.)” > > i.e., you must trap it explicitly, not as part of others. Thanks again, David. I don't yet know my way around the overall PG doc well enough to make sure that I read everything that relates to my current interest. Thanks for reminding me about this: 43.6.8. Trapping Errors https://www.postgresql.org/docs/14/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING in the section: 43.6. Control Structures https://www.postgresql.org/docs/14/plpgsql-control-structures.html in chapter: Chapter 43. PL/pgSQL — SQL Procedural Language https://www.postgresql.org/docs/14/plpgsql.html All is clear now. And the caveat "It is possible, but often unwise, to trap those two error types by name" makes sense.
Re: pg_dump: VACUUM and REINDEXING
On Sat, 2022-05-07 at 12:06 +0200, Guillaume Lelarge wrote: > Le sam. 7 mai 2022 à 10:21, Ron a écrit : > > On 5/6/22 21:35, Hasan Marzooq wrote: > > > I've some questions around Backup & Restore. > > > > > > 1: Is it necessary to perform a VACUUM and REINDEXING operation after > > > restoring the > > > dump from Postgres 9.6 to Postgres 13? The dump size could be 1/2 TB to > > > 1 TB. > > > > Perform VACUUM after there have been many updates and deletes. There have > > been zero > > updates and deleted after pg_restore; therefore, no need to vacuum. > > I disagree. You're right about the "zero updates and deletes", so no need to > vacuum for bloat. > But you need vacuum to get the visibility map of each relation, so that the > planner can use index-only scans. But from PostgreSQL v13 on, autovacuum is also triggered by INSERTs. So I'd say that there is nothing to do after restoring a pg_dump, except to wait until autovacuum is done. Yours, Laurenz Albe
