Re: PL/pgSQL, RAISE and error context

Hello

I played with this topic little bit

If I understand, the main problem is in console (or pgAdmin) output.

create or replace function foo()
returns void as $$
begin
for i in 1..5
loop
raise notice '>>>>> *****';
end loop;
raise exception '***************';
end;
$$ language plpgsql;

postgres=# select foo();
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
ERROR: ***************
Time: 2.024 ms
postgres=# \set VER
VERBOSITY VERSION
postgres=# \set VERBOSITY

postgres=# \set VERBOSITY

postgres=# \set VERBOSITY terse
postgres=# select foo();
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
NOTICE: >>>>> *****
ERROR: ***************
Time: 0.908 ms
postgres=# \set VERBOSITY verbose
postgres=# select foo();
NOTICE: 00000: >>>>> *****
LOCATION: exec_stmt_raise, pl_exec.c:3051
NOTICE: 00000: >>>>> *****
LOCATION: exec_stmt_raise, pl_exec.c:3051
NOTICE: 00000: >>>>> *****
LOCATION: exec_stmt_raise, pl_exec.c:3051
NOTICE: 00000: >>>>> *****
LOCATION: exec_stmt_raise, pl_exec.c:3051
NOTICE: 00000: >>>>> *****
LOCATION: exec_stmt_raise, pl_exec.c:3051
ERROR: P0001: ***************
LOCATION: exec_stmt_raise, pl_exec.c:3051

Time: 0.314 ms

I see a two little bit not nice issues:

a) in terse mode missing a CONTEXT for RAISED error
b) in verbose mode missing a CONTEXT for messages, for error too, and
useless LOCATION is showed.

LOCATION is absolutely useless for custom messages.

so I removed a context filtering

postgres=# select foo();
NOTICE: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
NOTICE: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
NOTICE: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
NOTICE: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
NOTICE: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
ERROR: ***************
CONTEXT: PL/pgSQL function foo() line 7 at RAISE
Time: 3.842 ms
postgres=# \set VERBOSITY verbose
postgres=# select foo();
NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
ERROR: P0001: ***************
CONTEXT: PL/pgSQL function foo() line 7 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
Time: 0.761 ms

We should not see a CONTEXT for DEFAULT verbosity and NOTICE level, after
little bit change I got a satisfied output

postgres=# select foo();
>>>NOTICE: >>>>> *****
>>>NOTICE: >>>>> *****
>>>NOTICE: >>>>> *****
>>>NOTICE: >>>>> *****
>>>NOTICE: >>>>> *****
ERROR: ***************
CONTEXT: PL/pgSQL function foo() line 7 at RAISE
Time: 2.434 ms
postgres=# \set VERBOSITY verbose
postgres=# select foo();
>>>NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
>>>NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
>>>NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
>>>NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
>>>NOTICE: 00000: >>>>> *****
CONTEXT: PL/pgSQL function foo() line 5 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
ERROR: P0001: ***************
CONTEXT: PL/pgSQL function foo() line 7 at RAISE
LOCATION: exec_stmt_raise, pl_exec.c:3046
Time: 0.594 ms

Probably we can introduce a new level of verbosity, but I am thinking so
this behave is reasonable. Everybody who use a VERBOSE level expect lot of
balast and it show expected info (context of error)

Can be this design good enough for you?

Regards

Pavel