Re: why dropping a trigger may cause a deadlock

On Fri, 05 Jun 2009 10:46:11 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> > I've encountered this error for the first time
> > psql:./import_stock_scratch.sql:9: ERROR: deadlock detected
> > DETAIL: Process 11095 waits for AccessExclusiveLock on relation
> > 250545 of database 248569; blocked by process 11099. Process
> > 11099 waits for AccessShareLock on relation 250510 of database
> > 248569; blocked by process 11095.
> > CONTEXT: SQL statement "drop trigger if exists
> > FT1IDX_catalog_items_update_trigger on catalog_items" PL/pgSQL
> > function "ft1idx_trigger_drop" line 3 at SQL statement
>
> > The function just drop 2 triggers that update a tsvector that is
> > gist indexed.
>
> Are the triggers on two different tables? It looks like you're

You're right. I forgot the exact schema.
I've a table that contains most of the fields that end up in the
tsvector and a reference to another table that contain a column that
end up in the tsvector.

create table a(
fti tsvector,
c1 text,
c2 text,
c3id int reference b(c3)
);
create table b(
c3id int primary key,
c3 text
);

One trigger is fired when c1, c2, c3id are modified.
The other is fired when c3 is modified.
Both trigger rebuild the tsvector that is obtained concatenating c1,
c2, c3

> probably trying to acquire exclusive lock on two tables, and
> deadlocking against some other process that gets a lesser lock
> on the same tables but in the other order.

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.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it