Re: good exception handling archiecutre

On 13 Aug 2010, at 14:07, <Atul.Goel@globaldatapoint.com> <Atul.Goel@globaldatapoint.com> wrote:

> How can we do error logging in Postgres. I am trying to create a LOG_AND_STOP method which would be generic to the code. I have pasted the code I have written. This code returns no data which is understandable as the error is thrown to the external world.
>  
> Can I write the code somehow.
>  
> CREATE SCHEMA test
>   AUTHORIZATION postgres;
>  
> CREATE TABLE test.error_log
> (
>   error_desc text
> );
>  
>  
> CREATE OR REPLACE FUNCTION test.log_and_stop(err_desc text)
>   RETURNS void AS
> $BODY$
> Declare
> x integer;
> begin
> begin
> insert into test.error_log values (err_desc);
> end;
> begin
> raise exception '%',err_desc;

Your transaction got rolled back here, hence the lack of data in your error_log table.

I don't think there's any way to log errors into a table from within the same transaction, you'll probably need to use a separate connection for the logging. I think LISTEN/NOTIFY may come in handy there.

> end;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> ALTER FUNCTION test.log_and_stop(text) OWNER TO postgres;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c6577d7967632072599737!