> 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?
IN SHARE MODE is not enough, you can get deadlocks. You require IN
SHARE ROW EXCLUSIVE MODE. other than that, it's a sucky solution
because it breaks concurrency. In pgsql 8, you can do it using pl/pgsql
exception handling.
Chris