I sent a message a while back on this list on why an insert onto a table A
which has a foreign key constraint to table B obtains a write (exclusive)
lock on that row on table B (basically does a select for update). The answer
was there is no SQL construct to obtain read (shared) locks on a particular
row, therefore it took a write lock.
I was just wondering, isn't the fact that FOREIGN KEY takes a write lock on
its parent a bug? I was just wondering whether this is being worked on, and
if anyone has any ideas where to start in case I want to work on it, or can
I create my own function / constraint which will just emulate a shared lock
behavior for a FOREIGN KEY constrant. This is making it tough to sanely
handle concurrent long-running transactions, even if I use the INITIALLY
DEFERRED for the foreign key constrant.
Thanx a lot, and thanx for this wonderful DB.
-rchit