Tom Lane wrote:
SELECTs don't lock any rows. INSERTs don't create any lockable rows in themselves (other backends can't even see the rows yet). If that's all that your transaction is doing, then I think the only explanation is that the INSERTs are in a table that has foreign keys (correct?) and that the row conflicts are on the referenced table. Before 8.1, we take a row lock on the referenced row to ensure that it won't be deleted before we can commit the referencing row. If you've got other transactions that are actively modifying the referenced table, then that's the source of the problem. Worse, you can get conflicts just from inserting other referencing rows with the same key values. The latter case is fixed in 8.1 by using a share instead of exclusive row lock, but that mechanism isn't available in 7.4 ...
Actually, the transaction has UPDATEs as well, but yes, the INSERTS are on tables with foreign keys.
I'm still confused by the behavior however. The locking behaves as if its some kind of *table* level lock, because while the function is executing (a long time), a dozen updates and inserts build up waiting for some lock to be released. If the loop just occassionally puts a lock on a few different rows, I don't see how that could cause the experienced behavior -- presumably the lock on those particular rows is released after its iteration through the loop. Unless, of course, the lock isn't given up ...
ps - thanks for noting or recognizing that I'm using 7.4. forgot to mention that.
