Re: good exception handling archiecutre

On 13 Aug 2010, at 14:07, <Atul(dot)Goel(at)globaldatapoint(dot)com> <Atul(dot)Goel(at)globaldatapoint(dot)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!