Re: locking of referenced table during constraint

On Wed, 2002-09-04 at 15:51, Stephan Szabo wrote:
> 
> On 4 Sep 2002, Scott Shattuck wrote:
> 
> > Under what conditions would the following statement cause the USERS
> > table to lock out selects?
> >
> >
> > alter table my_coupons
> >   add constraint FK_mc_user_id
> >   FOREIGN KEY (mc_frn_user_id)
> >   REFERENCES users(user_ID);
> 
> If I'm reading code correctly, an exclusive lock
> on the pk table is grabbed which will block selects
> as well. You're effectively altering both tables
> (you need to add triggers to both tables) and
> both get locked.
> 
> 

Ok, if I understand things correctly the USERS table gets a constraint
that says don't delete/update the USER_ID in any way that would orphan a
row in the MY_COUPONS table. The MY_COUPONS table gets one that says
don't insert/update MC_FRN_USER_ID such that it isn't found in
USERS.USER_ID. 

But...

There are no rows in the my_coupons table so it's not possible to orphan
a row there -- were it even the case that an update or delete were
running...which they aren't. Even if there were rows in the referring
table I don't understand why an exclusive table-level lock is being
taken out to add a trigger. If I add user-level triggers to do the same
task they go in without a hitch but cause other problems in 7.2 since I
can't control their order of execution yet (thanks Tom for the 7.3
patch! :)).

ss


> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)