Re: UNIQUE constraint violations and transactions

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

I see what you mean. In my case, if the INSERT fails I just want to
keep going. I don't really care if I can't INSERT again (but I do care
if I can't insert the first time!).

> Another way to do it could be SELECT count WHERE ... and if it's zero,
> INSERT, else choose a new number.

That was an option I was exploring...

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

... but I ended up restructing my transaction into 2 parts. 1
transaction for the data, and a bunch of small atomic INSERTS against
the table with the UNIQUE constraint. Overall, it's probably slower
this way, but my code is a little easier to follow.

thanks for your time!

eric