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

От:

Josh Berkus <josh@agliodbs.com>

Дата:

16 июня 2005 г. в 13:02:26

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

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

21 июня 2005 г. в 19:17:54

Pavel Stehule  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

От:

Neil Conway <neilc@samurai.com>

Дата:

19 июня 2005 г. в 05:14:42

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

PROPOSAL - User's exception in PL/pgSQL

От:

Pavel Stehule <stehule@kix.fsv.cvut.cz>

Дата:

16 июня 2005 г. в 05:18:10

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 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 '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

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

От:

Pavel Stehule <stehule@kix.fsv.cvut.cz>

Дата:

16 июня 2005 г. в 16:00:19

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

От:

Pavel Stehule <stehule@kix.fsv.cvut.cz>

Дата:

21 июня 2005 г. в 14:52:04

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

От:

Pavel Stehule <stehule@kix.fsv.cvut.cz>

Дата:

22 июня 2005 г. в 00:06:36

On Tue, 21 Jun 2005, Tom Lane wrote:

> Pavel Stehule  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