Обсуждение: Dropping constraints on a table locks referenced table... why?
I have a table A which has an FK constraint referencing another table B. Dropping this FK constraint on A locks table B, as does trying to drop table A. Why is this?
Is there some more efficient way to remove FK constraints without locking the referenced table?
Wells Oliver <wells.oliver@gmail.com> writes: > I have a table A which has an FK constraint referencing another table B. > Dropping this FK constraint on A locks table B, as does trying to drop > table A. Why is this? There are triggers on both tables that have to be removed. regards, tom lane
Where can I see these triggers? They don't show under the PG Admin trigger tab of course, are they, I don't know, special triggers, stored elsewhere? Can I query an internal PG table?
Just trying to understand better...
On Tue, Apr 17, 2018 at 11:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> I have a table A which has an FK constraint referencing another table B.
> Dropping this FK constraint on A locks table B, as does trying to drop
> table A. Why is this?
There are triggers on both tables that have to be removed.
regards, tom lane
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes: > Where can I see these triggers? They don't show under the PG Admin trigger > tab of course, are they, I don't know, special triggers, stored elsewhere? > Can I query an internal PG table? Sure, like this: regression=# create table pk (f1 int primary key); CREATE TABLE regression=# create table fk (f1 int references pk); CREATE TABLE regression=# select tgname,tgfoid::regproc from pg_trigger where tgrelid = 'pk'::regclass; tgname | tgfoid ------------------------------+------------------------ RI_ConstraintTrigger_a_51649 | "RI_FKey_noaction_del" RI_ConstraintTrigger_a_51650 | "RI_FKey_noaction_upd" (2 rows) regression=# select tgname,tgfoid::regproc from pg_trigger where tgrelid = 'fk'::regclass; tgname | tgfoid ------------------------------+--------------------- RI_ConstraintTrigger_c_51651 | "RI_FKey_check_ins" RI_ConstraintTrigger_c_51652 | "RI_FKey_check_upd" (2 rows) psql and probably most other client tools hide these triggers figuring they're uninteresting, but they're pretty ordinary triggers otherwise. regards, tom lane