Re: why dropping a trigger may cause a deadlock

On Fri, 05 Jun 2009 17:35:19 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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