> 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