Обсуждение: Dropping constraints on a table locks referenced table... why?

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