On Fri, 26 Oct 2001, Eric Ridge wrote:
> Hi!
>
> I've got a UNIQUE constraint on a field, and obviously, when I try to
> insert a duplicate value, I get a WARNING via psql (and an Exception via
> the JDBC drivers) saying I tried to violate the constraint. No biggie.
> This is what I expect.
>
> The tricky part comes in when I violate the constraint inside a
> transaction. It seems once a WARNING happens inside a transaction you
> can't do anything else in the transaction. You can only COMMIT or
> ROLLBACK. In my situation, it's not fatal that I tried to insert a
> duplicate value... I just want to continue on in the transaction.
It depends on what you plan to do after the insert failed. In my case I
often want to insert a value if the primary key does not exist, or
update the tuple if it already exists. In this case, the simplest way to
do it is to UPDATE it and if the number of affected tuples is 0,
INSERT. I believe it's also more efficient, since it's done in one
transaction (you don't have to rollback and open a new one).
Another way to do it could be SELECT count WHERE ... and if it's zero,
INSERT, else choose a new number.
I think in both cases you may need "transaction isolation level
serializable" if you want to be very strict. You may end up with
rollbacked transaction anyway...
--
Alvaro Herrera (<alvherre[@]atentus.com>)
"La conclusion que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusion de ellos" (Tannenbaum)
