Re: Revisited: Transactions, insert unique.

Today, in a message to pgsql-general, David Boerwinkle wrote:
>
> it seems like this is something that ought to be handled
> programmatically. That is, query the table to see if the row exists,
> then decide what you are going to do (insert or update) based on the
> results of your query.

It certainly 'can' be handled the way you describe, but to say that it
'ought' to be handled this way is going too far. It is common practice in
database programming to simply try the most likely case and fall back to
alternatives when an error is encountered. For example, if one expects 99%
of inserts to be unique one may simply try the insert and when this fails
because of a duplicate key error one can update instead. This is slightly
more efficient than doing the extra query in 100% of cases.

In any case, if I'm not mistaken the SQL standard permits an automatic
rollback only for deadlock errors and equivalent types of errors where the
rollback may be required to resolve a lockup situation.

Joachim

--
private: joachim(at)kraut(dot)bc(dot)ca (http://www.kraut.bc.ca)
work: joachim(at)mercury(dot)bc(dot)ca (http://www.mercury.bc.ca)