Обсуждение: FOREIGN KEY taking write locks on parent.

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

Rachit Siamwalla <rachit@ensim.com> writes:
> I was just wondering, isn't the fact that FOREIGN KEY takes a write lock on
> its parent a bug?

Yes, I think so.  Fixing it is not trivial (else we'd have done it right
to start with) ... but if you want to step up to the plate to fix it,
we're all ears ...
        regards, tom lane