From: | Scott Shattuck <ss(at)technicalpursuit(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: locking of referenced table during constraint |
Date: | 2002-09-05 04:29:24 |
Message-ID: | 1031200164.9345.1576.camel@idearat |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-09-05 04:49:56 | Re: locking of referenced table during constraint |
Previous Message | Bruce Momjian | 2002-09-05 04:19:11 | Re: Beta1 schedule |