Re: Meaningful Exception handling - Mailing list pgsql-jdbc

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