Thread: 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 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
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
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@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
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