Re: Meaningful Exception handling - Mailing list pgsql-jdbc

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
>>
>
>