Re: why dropping a trigger may cause a deadlock

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

If it's only one table, then I'd wonder whether the transaction
already has a lower-grade lock on the table before it tries to
do DROP TRIGGER. Lock escalation is another common way to get
yourself deadlocked.

regards, tom lane