Re: PROPOSAL - User's exception in PL/pgSQL

Hello

I did some work on implementation of user's exception.

Generally:

o add pseudotype EXCEPTION

DECLARE excpt EXCEPTION [= 'SQLSTATE']

o change RAISE stmt

RAISE error_level [excpt_var|sys_excpt_name] errmsg, ...

o change EXCEPTION

EXCEPTION WHEN excpt_var|sys_excpt_name THEN ...

Rules:
o User can specify SQLSTATE only from class 'U1'
o Default values for SQLSTATE usr excpt are from class 'U0'
o Every exception's variable has unique SQLSTATE
o User's exception or system's exception can be raised only with
level EXCEPTION

Any comments, notes?

Regards
Pavel Stehule

Regres test:

create function innerfx() returns integer as $$
declare my_excpt exception = 'U0001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
psql:regres.sql:6: ERROR: Invalid class for SQLSTATE value 'U0001' for
user's exception.
HINT: Select any unoccupied value from class U1 which is reserved for
user's exception.
CONTEXT: compile of PL/pgSQL function "innerfx" near line 1
create function innerfx() returns integer as $$
declare
my_excpt exception = 'U1001';
my_sec_excpt exception = 'U1001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
psql:regres.sql:15: ERROR: Invalid SQLSTATE value 'U1001' for user's
exception.
HINT: Select any unoccupied value from class U1 which is reserved for
user's exception.
CONTEXT: compile of PL/pgSQL function "innerfx" near line 3
create function innerfx() returns integer as $$
declare my_excpt exception = 'U1001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
CREATE FUNCTION
create function outerfx() returns integer as $$
declare
my_excpt exception = 'U1001';
alias_div_by_zero exception = 'U1002';
my_excpt_def_sqlstate exception;
begin
begin
raise exception my_excpt_def_sqlstate 'foo';
exception when my_excpt_def_sqlstate then
raise notice '01 catch: %, %', sqlstate, sqlerrm;
end;
begin
raise notice '%', innerfx();
exception when my_excpt then
raise notice '02 catch: %, %', sqlstate, sqlerrm::timestamp;
end;
begin
raise exception division_by_zero 'testing';
exception when division_by_zero then
raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
end;
raise exception alias_div_by_zero 'Unhandled exception';
return 1;
end; $$ language plpgsql;
CREATE FUNCTION
select innerfx();
psql:regres.sql:50: ERROR: 2005-06-16 10:12:53.27408+02
DETAIL: User's exception/notice - sqlstate: U1001, name: my_excpt
HINT: from RAISE stmt on line 3
select outerfx();
psql:regres.sql:51: NOTICE: 01 catch: U0001, foo
psql:regres.sql:51: NOTICE: 02 catch: U1001, 2005-06-16 10:12:53.274656
psql:regres.sql:51: NOTICE: Divison by zero: 22012, testing
psql:regres.sql:51: ERROR: Unhandled exception
DETAIL: User's exception/notice - sqlstate: U1002, name:
alias_div_by_zero
HINT: from RAISE stmt on line 21
drop function outerfx();
DROP FUNCTION
drop function innerfx();
DROP FUNCTION

Pavel,

>  o User can specify SQLSTATE only from class 'U1'
>  o Default values for SQLSTATE usr excpt are from class 'U0'
>  o Every exception's variable has unique SQLSTATE
>  o User's exception or system's exception can be raised only with
>    level EXCEPTION
>
> Any comments, notes?

Looks great to me, pending a code examination. Will it also be possible to
query the SQLSTATE/ERRSTRING inside the EXCEPTION clause? i.e.

WHEN OTHERS THEN
RAISE NOTICE '%',sqlstate;
ROLLBACK;

That's something missing from 8.0 exception handling that makes it hard to
improve SPs with better error messages.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, 16 Jun 2005, Josh Berkus wrote:

> Pavel,
>
> >  o User can specify SQLSTATE only from class 'U1'
> >  o Default values for SQLSTATE usr excpt are from class 'U0'
> >  o Every exception's variable has unique SQLSTATE
> >  o User's exception or system's exception can be raised only with
> >    level EXCEPTION
> >
> > Any comments, notes?
>
> Looks great to me, pending a code examination. Will it also be possible to
> query the SQLSTATE/ERRSTRING inside the EXCEPTION clause? i.e.
>
> WHEN OTHERS THEN
> RAISE NOTICE '%',sqlstate;
> ROLLBACK;

yes, ofcourse. CVS can it now

>
> That's something missing from 8.0 exception handling that makes it hard to
> improve SPs with better error messages.
>
>

Pavel Stehule wrote:
> DECLARE excpt EXCEPTION [= 'SQLSTATE']

What would this default to? (i.e. if no '= SQLSTATE' is specified)

> Rules:
> o User can specify SQLSTATE only from class 'U1'
> o Default values for SQLSTATE usr excpt are from class 'U0'

Can you elaborate on what you mean?

> o Every exception's variable has unique SQLSTATE
> o User's exception or system's exception can be raised only with
> level EXCEPTION

It might be worth allowing a custom SQLSTATE to be specified for
non-exception RAISEs -- there are already WARNING SQLSTATE error codes
(see ERRCODE_WARNING).

-Neil

On Sun, 19 Jun 2005, Neil Conway wrote:

> Pavel Stehule wrote:
> > DECLARE excpt EXCEPTION [= 'SQLSTATE']
>
> What would this default to? (i.e. if no '= SQLSTATE' is specified)

I wont to prohibit synonyms in exception (every exception has unique
sqlstate). If I need better control for sqlstate - exception can leave one
function I can specify own sql state. If I need only name, its unimportant
sqlstate value.

>
> > Rules:
> > o User can specify SQLSTATE only from class 'U1'
> > o Default values for SQLSTATE usr excpt are from class 'U0'
>

It's my idiot implentation uniques sqlstates. No more ;-)

> Can you elaborate on what you mean?
>
> > o Every exception's variable has unique SQLSTATE
> > o User's exception or system's exception can be raised only with
> > level EXCEPTION
>
> It might be worth allowing a custom SQLSTATE to be specified for
> non-exception RAISEs -- there are already WARNING SQLSTATE error codes
> (see ERRCODE_WARNING).
>

Hmm. True. Maybe:

o System's exception can be raised with relevant level exception.

Regards
Pavel

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> I wont to prohibit synonyms in exception (every exception has unique
> sqlstate).

I don't think that's a particularly good idea --- maybe if SQL had been
designed according to your worldview, it'd be like that, but it isn't
and you can't retroactively force it to be. The SQLSTATEs are
deliberately designed to be fairly coarse, not unique. I believe the
design intention is to distinguish between two cases when it's likely
that client application code would do something different in the two
cases. Not to be "unique for uniqueness' sake".

regards, tom lane

On Tue, 21 Jun 2005, Tom Lane wrote:

> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> > I wont to prohibit synonyms in exception (every exception has unique
> > sqlstate).
>
> I don't think that's a particularly good idea --- maybe if SQL had been
> designed according to your worldview, it'd be like that, but it isn't
> and you can't retroactively force it to be. The SQLSTATEs are
> deliberately designed to be fairly coarse, not unique. I believe the
> design intention is to distinguish between two cases when it's likely
> that client application code would do something different in the two
> cases. Not to be "unique for uniqueness' sake".
>

it's can be source of bugs. For me, uniqueness sqlstates is 20 lines more.
Ok. I will send patch without unique states.

Pavel