Re: do foreign key checks lock parent table ?

swalker@iglou.com wrote:
>
> If you try the example I have below - the second transaction will block
> waiting for the first one to complete.  This doesn't seem correct to me.
> Is this a known problem / feature?  If I create the table w/ a deferrable
> intially deferred foreign key - I don't get the problem.  But this is a
> very unexpected default behavior ( At least to me :) ).

    It  is a known 'requirement' (read minor problem), because in
    order to prevent someone else from removing the PK row,  your
    transaction  needs  to place a shared read lock at least. Now
    PostgreSQL does not have this sort of shared  read  lock,  so
    the lightest lock a FK trigger can set is one FOR UPDATE.

    The  fact  that  this behaviour is annoying to you is somehow
    suspicious (at least to me :).  Does your application try  to
    hold  transactions  across  user interaction?  If so, locking
    issues will not be your biggest problem, so don't worry about
    them too much.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #