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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings