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(at)iglou(dot)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(at)Yahoo(dot)com #
>
>
>