Re: do foreign key checks lock parent table ?

We are not holding a transaction open.

But now that I understand how postgres is implementing fk constraints I
can see the problem.

Our table structure is bascially something like this.

order_header
    - Just basic order info

order_detail
    - A product
    - and it's quantity

order_detail_status
    - if an order detail has a quantity of 5 - there will be 5
          order_detail_status rows in this table

So, when we insert a record - we go from the top down.  But as the
products are made - a process marks each order_detail_status as complete.
Once all the order_detail_status's for a give order_header are complete -
the order_header status is updated to complete.

So, we are inserting from the top down - but updating from the bottom up.
But I didn't forsee this as a problem - because none of the status's
should overlap.  Status's are for instance 'New Order', 'In progress',
'Complete'.

We have many processes taking orders - and a process updating them to
the 'Complete' status.

Any ideas on how to remedy this?  Is this a problem that is being
addressed ?  I'd be glad to help!

Steve

On Wed, 3 Apr 2002, Jan Wieck wrote:

> 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 #
>
>
>