Best practices: MERGE

Folks,

Although the SQL:2003 command MERGE has not yet been implemented in
PostgreSQL, I'm guessing that there are best practices for how to
implement the MERGE functionality.

To recap, MERGE means (roughly) INSERT the tuple if no tuple matches
certain criteria, otherwise UPDATE using similar criteria.

The "correct" solution, as far as I can tell, is to acquire a LOCK on
the table IN SHARE MODE at the beginning of the transaction, but this
has (at least for many applications) unacceptable performance
characteristics. Accepting that there is a slight risk of a race
condition when *not* locking the table at the beginning of the
transaction, what procedure minimizes this risk and recovers well from
said race condition, should it occur?

TIA for any hints, tips or pointers on this :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!