Postgres Pro
Downloads
Home   >   mailing lists

Re: PL/pgSQL, RAISE and error context - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: PL/pgSQL, RAISE and error context
Date
Msg-id CAFj8pRDFHqv7cKXP-K+uBzW6627HytT7bct4ZxDBseLXc1Rcgg@mail.gmail.com
Whole thread Raw
In response to Re: PL/pgSQL, RAISE and error context  (Merlin Moncure <mmoncure@gmail.com>)
Responses Re: PL/pgSQL, RAISE and error context
Re: PL/pgSQL, RAISE and error context
List pgsql-hackers
Tree view
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



Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: CAST Within EXCLUSION constraint
Next
From: Dean Rasheed
Date:
Subject: Re: Updatable view columns