Thread: Meaningful Exception handling
First off, thanks for the quick help with my previous simple problem. Now I'd like to catch SQLExceptions selectively. For example, I have a uniquely indexed column in Table A. I would like to insert into this column and I don't care if the insert fails due to duplication. But I would want to know if something else went wrong. Is there a way to specifically catch and ignore this particular instance of the SQLException short of comparing a String to the database's returned error message? Are there any docs referring to SQLException messages thrown? Thanks again. -- ><><><><><><><><><><><>< AgentM agentm@cmu.edu
First off you do know that you can't catch an error in postgres and continue on, don't you? Any error in postgres aborts the current transaction. Thus after an error you must rollback and start a new transaction, and then you need to redo all the work up to the point of the error. This isn't generally something you can easily do when catching an exception. Answering your specific question: comparing the string error message is currently the only way to determine the error. There is a TODO item for the database to support error codes, and when the database adds that support the jdbc driver will as well. thanks, --Barry AgentM wrote: > First off, thanks for the quick help with my previous simple problem. > > Now I'd like to catch SQLExceptions selectively. For example, I have a > uniquely indexed column in Table A. I would like to insert into this > column and I don't care if the insert fails due to duplication. But I > would want to know if something else went wrong. Is there a way to > specifically catch and ignore this particular instance of the > SQLException short of comparing a String to the database's returned > error message? Are there any docs referring to SQLException messages > thrown? Thanks again.
Barry- Is this true of *all* SQLExceptions, or are some at a lower level that won't cause a transaction abort? My concern is that I'm contemplating a load process in which some inserts may be duplicated, and I was planning on depending on the unique index to kick these out. If I'm committing after every load instead of after every insert, would I lose all transactions up to the point where the unique constraint stopped an insert? Is there any way to tell PostgreSQL to continue on errors? Thanks -Nick > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind > Sent: Wednesday, April 17, 2002 12:23 AM > To: AgentM > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Meaningful Exception handling > > > First off you do know that you can't catch an error in postgres and > continue on, don't you? Any error in postgres aborts the current > transaction. Thus after an error you must rollback and start a new > transaction, and then you need to redo all the work up to the point of > the error. This isn't generally something you can easily do when > catching an exception. > > Answering your specific question: comparing the string error message is > currently the only way to determine the error. There is a TODO item for > the database to support error codes, and when the database adds that > support the jdbc driver will as well. > > thanks, > --Barry > > > > AgentM wrote: > > First off, thanks for the quick help with my previous simple problem. > > > > Now I'd like to catch SQLExceptions selectively. For example, I have a > > uniquely indexed column in Table A. I would like to insert into this > > column and I don't care if the insert fails due to duplication. But I > > would want to know if something else went wrong. Is there a way to > > specifically catch and ignore this particular instance of the > > SQLException short of comparing a String to the database's returned > > error message? Are there any docs referring to SQLException messages > > thrown? Thanks again. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Nick, *All* database errors have this behavior. Most but not all SQLExceptions are the result of errors occuring in the database. A duplicate key in index error will result in the entire transaction needing to be rolled back. So what you are proposing to do can't be done with postgres. However the way I work around this problem is to do the following: insert into foo (bar) values (?) where not exists select * from foo where bar = ? Inserts of this format will prevent duplicates from being inserted. You can even look at the result of the above statement to see the number of rows affected, and if it is zero (meaning the row was already there and you didn't insert), you can branch and do an update instead. thanks, --Barry Nick Fankhauser wrote: > Barry- > > Is this true of *all* SQLExceptions, or are some at a lower level that won't > cause a transaction abort? > > My concern is that I'm contemplating a load process in which some inserts > may be duplicated, and I was planning on depending on the unique index to > kick these out. If I'm committing after every load instead of after every > insert, would I lose all transactions up to the point where the unique > constraint stopped an insert? Is there any way to tell PostgreSQL to > continue on errors? > > Thanks > > -Nick > > > >>-----Original Message----- >>From: pgsql-jdbc-owner@postgresql.org >>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind >>Sent: Wednesday, April 17, 2002 12:23 AM >>To: AgentM >>Cc: pgsql-jdbc@postgresql.org >>Subject: Re: [JDBC] Meaningful Exception handling >> >> >>First off you do know that you can't catch an error in postgres and >>continue on, don't you? Any error in postgres aborts the current >>transaction. Thus after an error you must rollback and start a new >>transaction, and then you need to redo all the work up to the point of >>the error. This isn't generally something you can easily do when >>catching an exception. >> >>Answering your specific question: comparing the string error message is >>currently the only way to determine the error. There is a TODO item for >>the database to support error codes, and when the database adds that >>support the jdbc driver will as well. >> >>thanks, >>--Barry >> >> >> >>AgentM wrote: >> >>>First off, thanks for the quick help with my previous simple problem. >>> >>>Now I'd like to catch SQLExceptions selectively. For example, I have a >>>uniquely indexed column in Table A. I would like to insert into this >>>column and I don't care if the insert fails due to duplication. But I >>>would want to know if something else went wrong. Is there a way to >>>specifically catch and ignore this particular instance of the >>>SQLException short of comparing a String to the database's returned >>>error message? Are there any docs referring to SQLException messages >>>thrown? Thanks again. >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> > > >
Is there a way to catch and ignore this kind of errors in stored procedures (like an emtpy catch block in java)? > -----Original Message----- > From: Barry Lind [mailto:barry@xythos.com] > Sent: Wednesday, April 17, 2002 6:57 PM > To: nickf@ontko.com > Cc: AgentM; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Meaningful Exception handling > > > Nick, > > *All* database errors have this behavior. Most but not all > SQLExceptions are the result of errors occuring in the database. > > A duplicate key in index error will result in the entire transaction > needing to be rolled back. So what you are proposing to do can't be > done with postgres. However the way I work around this > problem is to do > the following: > > insert into foo (bar) values (?) > where not exists select * from foo where bar = ? > > Inserts of this format will prevent duplicates from being > inserted. You > can even look at the result of the above statement to see the > number of > rows affected, and if it is zero (meaning the row was already > there and > you didn't insert), you can branch and do an update instead. > > thanks, > --Barry > > > > Nick Fankhauser wrote: > > Barry- > > > > Is this true of *all* SQLExceptions, or are some at a lower > level that won't > > cause a transaction abort? > > > > My concern is that I'm contemplating a load process in > which some inserts > > may be duplicated, and I was planning on depending on the > unique index to > > kick these out. If I'm committing after every load instead > of after every > > insert, would I lose all transactions up to the point where > the unique > > constraint stopped an insert? Is there any way to tell PostgreSQL to > > continue on errors? > > > > Thanks > > > > -Nick > > > > > > > >>-----Original Message----- > >>From: pgsql-jdbc-owner@postgresql.org > >>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind > >>Sent: Wednesday, April 17, 2002 12:23 AM > >>To: AgentM > >>Cc: pgsql-jdbc@postgresql.org > >>Subject: Re: [JDBC] Meaningful Exception handling > >> > >> > >>First off you do know that you can't catch an error in postgres and > >>continue on, don't you? Any error in postgres aborts the current > >>transaction. Thus after an error you must rollback and start a new > >>transaction, and then you need to redo all the work up to > the point of > >>the error. This isn't generally something you can easily do when > >>catching an exception. > >> > >>Answering your specific question: comparing the string > error message is > >>currently the only way to determine the error. There is a > TODO item for > >>the database to support error codes, and when the database adds that > >>support the jdbc driver will as well. > >> > >>thanks, > >>--Barry > >> > >> > >> > >>AgentM wrote: > >> > >>>First off, thanks for the quick help with my previous > simple problem. > >>> > >>>Now I'd like to catch SQLExceptions selectively. For > example, I have a > >>>uniquely indexed column in Table A. I would like to insert > into this > >>>column and I don't care if the insert fails due to > duplication. But I > >>>would want to know if something else went wrong. Is there a way to > >>>specifically catch and ignore this particular instance of the > >>>SQLException short of comparing a String to the database's returned > >>>error message? Are there any docs referring to > SQLException messages > >>>thrown? Thanks again. > >> > >> > >> > >>---------------------------(end of > broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > >> > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
No. A database error forces the transaction to be aborted on the server side. What client you use doesn't matter. You can't catch errors and continue processing in postgres (well you can, but you need to rollback and start a new transaction first, which isn't what most people consider catching an error and continuing). --Barry Kovács Péter wrote: > Is there a way to catch and ignore this kind of errors in stored procedures > (like an emtpy catch block in java)? > > >>-----Original Message----- >>From: Barry Lind [mailto:barry@xythos.com] >>Sent: Wednesday, April 17, 2002 6:57 PM >>To: nickf@ontko.com >>Cc: AgentM; pgsql-jdbc@postgresql.org >>Subject: Re: [JDBC] Meaningful Exception handling >> >> >>Nick, >> >>*All* database errors have this behavior. Most but not all >>SQLExceptions are the result of errors occuring in the database. >> >>A duplicate key in index error will result in the entire transaction >>needing to be rolled back. So what you are proposing to do can't be >>done with postgres. However the way I work around this >>problem is to do >>the following: >> >>insert into foo (bar) values (?) >>where not exists select * from foo where bar = ? >> >>Inserts of this format will prevent duplicates from being >>inserted. You >>can even look at the result of the above statement to see the >>number of >>rows affected, and if it is zero (meaning the row was already >>there and >>you didn't insert), you can branch and do an update instead. >> >>thanks, >>--Barry >> >> >> >>Nick Fankhauser wrote: >> >>>Barry- >>> >>>Is this true of *all* SQLExceptions, or are some at a lower >> >>level that won't >> >>>cause a transaction abort? >>> >>>My concern is that I'm contemplating a load process in >> >>which some inserts >> >>>may be duplicated, and I was planning on depending on the >> >>unique index to >> >>>kick these out. If I'm committing after every load instead >> >>of after every >> >>>insert, would I lose all transactions up to the point where >> >>the unique >> >>>constraint stopped an insert? Is there any way to tell PostgreSQL to >>>continue on errors? >>> >>>Thanks >>> >>>-Nick >>> >>> >>> >>> >>>>-----Original Message----- >>>>From: pgsql-jdbc-owner@postgresql.org >>>>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind >>>>Sent: Wednesday, April 17, 2002 12:23 AM >>>>To: AgentM >>>>Cc: pgsql-jdbc@postgresql.org >>>>Subject: Re: [JDBC] Meaningful Exception handling >>>> >>>> >>>>First off you do know that you can't catch an error in postgres and >>>>continue on, don't you? Any error in postgres aborts the current >>>>transaction. Thus after an error you must rollback and start a new >>>>transaction, and then you need to redo all the work up to >>> >>the point of >> >>>>the error. This isn't generally something you can easily do when >>>>catching an exception. >>>> >>>>Answering your specific question: comparing the string >>> >>error message is >> >>>>currently the only way to determine the error. There is a >>> >>TODO item for >> >>>>the database to support error codes, and when the database adds that >>>>support the jdbc driver will as well. >>>> >>>>thanks, >>>>--Barry >>>> >>>> >>>> >>>>AgentM wrote: >>>> >>>> >>>>>First off, thanks for the quick help with my previous >>>> >>simple problem. >> >>>>>Now I'd like to catch SQLExceptions selectively. For >>>> >>example, I have a >> >>>>>uniquely indexed column in Table A. I would like to insert >>>> >>into this >> >>>>>column and I don't care if the insert fails due to >>>> >>duplication. But I >> >>>>>would want to know if something else went wrong. Is there a way to >>>>>specifically catch and ignore this particular instance of the >>>>>SQLException short of comparing a String to the database's returned >>>>>error message? Are there any docs referring to >>>> >>SQLException messages >> >>>>>thrown? Thanks again. >>>> >>>> >>>> >>>>---------------------------(end of >>> >>broadcast)--------------------------- >> >>>>TIP 1: subscribe and unsubscribe commands go to >>> >>majordomo@postgresql.org >> >>> >>> >> >> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 3: if posting/reading through Usenet, please send an appropriate >>subscribe-nomail command to majordomo@postgresql.org so that your >>message can get through to the mailing list cleanly >> > >