pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDropping constraints on a table locks referenced table... why?
4+ messages / 2 participants
[nested] [flat]
* Dropping constraints on a table locks referenced table... why?
@ 2018-04-17 18:25 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2018-04-17 18:25 UTC (permalink / raw)
To: pgsql-admin
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?
Thanks for clarification.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dropping constraints on a table locks referenced table... why?
@ 2018-04-17 18:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2018-04-17 18:57 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dropping constraints on a table locks referenced table... why?
@ 2018-04-18 18:14 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2018-04-18 18:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Dropping constraints on a table locks referenced table... why?
@ 2018-04-18 18:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2018-04-18 18:39 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-04-18 18:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-17 18:25 Dropping constraints on a table locks referenced table... why? Wells Oliver <wells.oliver@gmail.com>
2018-04-17 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-18 18:14 ` Wells Oliver <wells.oliver@gmail.com>
2018-04-18 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox