Обсуждение: good exception handling archiecutre
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;
end;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION test.log_and_stop(text) OWNER TO postgres;
CREATE OR REPLACE FUNCTION test.f1()
RETURNS void AS
$BODY$
Declare
x integer;
begin
x:=1;
x:=x/0;
exception
when others then
perform test.log_and_stop('error occured in function f1');
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION test.f1() OWNER TO postgres;
select test.f1();
/*----------------------------output-------------------------*/
-- ERROR: error occured in function f1
-- CONTEXT: SQL statement "SELECT test.log_and_stop('error occured in function f1')"
-- PL/pgSQL function "f1" line 8 at PERFORM
--
--
-- ********** Error **********
--
-- ERROR: error occured in function f1
-- SQL state: P0001
-- Context: SQL statement "SELECT test.log_and_stop('error occured in function f1')"
-- PL/pgSQL function "f1" line 8 at PERFORM
/*-------------------------------------------------------------*/
select * from test.error_log; --no data found;
Regards,
Atul Goel
SENIOR DEVELOPER
Global DataPoint
Middlesex House, 34-42 Cleveland Street
London W1T 4LB, UK
T: +44 (0)20 7079 4827
M: +44 (0)7846765098
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:737,4c6577d7967632072599737!
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
Heyho!
[[ logging from postgres ]]
I think you can use dblink to do this - everything you do via dblink happens
in a separate db session and so it shouldn't be affected by what happens in
your transaction.
Depending on your requirements, system resources may be an issue though
(you're opening a full additional db session.)
cheers
-- vbi
--
Although this syntax is required by the SQL standard, it's not clear
what the standard expects it to do, except that it's most likely not
what we were doing.
-- PostgreSQL 8.3 release notes
Вложения
On 16 Aug 2010, at 10:45, <Atul.Goel@globaldatapoint.com> <Atul.Goel@globaldatapoint.com> wrote: > 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. Ah, you're right! I totally forgot about that... > Is there any other way of inter process communication in Postgres. Of course there is :) There's nothing stopping you from writing a few custom functions in an untrusted language that does your IPC for you. C comesto mind, but PL/Perlu, PL/PythonU, etc. should be able to do it as well (and probably easier). Usually if you're talking about performing I/O external to the database you'll get warned that transactions involving thosecalls can't be rolled back, but in this case that's exactly what you want. Now I know that Postgres uses some IPC internally as well (at least shared memory), so maybe it's possible to use some ofthe internal mechanisms. I have no idea whether that's possible or at all advisable, I'm sure someone (probably Tom) willchime in regarding that. Cheers! > -----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 usea 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. > > > > > > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > 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,4c697052967632122118857!
Thanks Alban, I was able to do this using DBLINK. Thanks a lot for your help. Regards, Atul Goel -----Original Message----- From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl] Sent: 16 August 2010 18:07 To: Atul Goel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] good exception handling archiecutre On 16 Aug 2010, at 10:45, <Atul.Goel@globaldatapoint.com> <Atul.Goel@globaldatapoint.com> wrote: > 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. Ah, you're right! I totally forgot about that... > Is there any other way of inter process communication in Postgres. Of course there is :) There's nothing stopping you from writing a few custom functions in an untrusted language that does your IPC for you. C comesto mind, but PL/Perlu, PL/PythonU, etc. should be able to do it as well (and probably easier). Usually if you're talking about performing I/O external to the database you'll get warned that transactions involving thosecalls can't be rolled back, but in this case that's exactly what you want. Now I know that Postgres uses some IPC internally as well (at least shared memory), so maybe it's possible to use some ofthe internal mechanisms. I have no idea whether that's possible or at all advisable, I'm sure someone (probably Tom) willchime in regarding that. Cheers! > -----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 usea 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. > > > > > > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > 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,4c697045967631814717658! 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
Hi VBI,
DBLINK was a great idea. I have written the code to do that and it works perfectly.
Regards,
Atul Goel
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian von Bidder
Sent: 16 August 2010 12:27
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] good exception handling archiecutre
Heyho!
[[ logging from postgres ]]
I think you can use dblink to do this - everything you do via dblink happens in a separate db session and so it
shouldn'tbe affected by what happens in your transaction.
Depending on your requirements, system resources may be an issue though (you're opening a full additional db session.)
cheers
-- vbi
--
Although this syntax is required by the SQL standard, it's not clear what the standard expects it to do, except that
it'smost likely not what we were doing.
-- PostgreSQL 8.3 release notes
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