Re: good exception handling archiecutre

Hi Alban,

Thanks for the reply.

I checked that Notify will not work till the transaction is commited. So in our case since the transaction is being
rolledback there will be no notify. I am new to Postgres so i am not sure how to run LISTEN as demon so that it keeps
onrunning to be notified. 

Here is the extract form http://www.postgresql.org/docs/8.4/static/sql-notify.html
"NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction,
thenotify events are not delivered until and unless the transaction is committed. This is appropriate, since if the
transactionis aborted, all the commands within it have had no effect, including NOTIFY." 

Is there any other way of inter process communication in Postgres.

Regards,
Atul Goel

-----Original Message-----
From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
Sent: 13 August 2010 17:50
To: Atul Goel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
theexternal 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
aseparate 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:1157,4c6577d2967638551718946!


This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global
DataPointLimited does not accept liability for any statements made which are clearly the sender's own and not expressly
madeon behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means
ofe-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752
RegisteredOffice Middlesex House, 34-42 Cleveland Street, London W1T 4LB