On Fri, 05 Jun 2009 17:35:19 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > I don't get it.
> > Why dropping the triggers would cause a deadlock anyway?
>
> > I bet it is due to my naïve view of the problem but I think a
> > trigger is a "function". Unless there is concurrent access to the
> > table where the function is defined... I can't see why dropping
> > the "functions" serially should cause a lock.
>
> They're not just functions, they are part of the DDL for a table.
> Adding or removing a trigger on a table requires exclusive lock
> on that table, otherwise you can't be sure what will happen in
> concurrent transactions that might (or might not) be supposed to
> fire the trigger.
I'm still wondering why there was anything else requiring a lock on
those tables.
Referring to the previous example
create table b(
c3id int primary key,
c3 text
);
create table a(
pid int primary key,
fti tsvector,
c1 text,
c2 text,
c3id int reference b(c3)
c4 int; -- not used to build up fti
);
there is a very small chance that while I was dropping the triggers
something like an
update a set c4=37 where pid=12;
was running when I dropped the trigger.
But I can't see how this should require a lock.. and still well...
the chances the update statement happened during trigger drop are
very very negligible.
And... still I'm quite surprised that even that update happening
when I was dropping the trigger resulted in a deadlock.
Everything happening on table a and b that involves writes already
happened in the same transaction dropping the triggers or is read
only.
Should I look into anything else to get a clue about what happened
and try to avoid it?
Thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it