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

PROPOSAL - User's exception in PL/pgSQL

From
Pavel Stehule
Date:
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
Everyexception's variable has unique SQLSTATEo 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
'02catch: %, %', sqlstate, sqlerrm::timestamp; end; begin   raise exception division_by_zero 'testing'; exception when
division_by_zerothen   raise notice 'Divison by zero: %, %', sqlstate, sqlerrm; end; raise exception alias_div_by_zero
'Unhandledexception'; 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





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

From
Josh Berkus
Date:
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 THENRAISE 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


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

From
Pavel Stehule
Date:
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.
> 
> 



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

From
Neil Conway
Date:
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


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

From
Pavel Stehule
Date:
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 



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

From
Tom Lane
Date:
Pavel Stehule <stehule@kix.fsv.cvut.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


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

From
Pavel Stehule
Date:
On Tue, 21 Jun 2005, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.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