How come having foreign keys take out a read lock on a parent row rather than
a write lock is not on the todo list? I had someone tell me that this is
difficult because the SQL standard does not include syntax for read lock.
Does the fact that it will be difficult mean that it isn't needed?
I'm having trouble getting Postgre to scale when the db is properly
normalized and related and there are many clients. One of the main
contention points is with the product table. When a product first comes out,
there may be many many people that are interested. Because the FK triggers
take out a write lock, orders being placed referencing the same product must
wait in line to get a lock. Under high load, that wait can be substantial.
I wish I had the resources to help with Postgre development. For now, I can
only offer my observations.