why dropping a trigger may cause a deadlock

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: why dropping a trigger may cause a deadlock
Date: 2009-06-05 08:17:20
Message-ID: 20090605101720.6b6e568a@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Before running import_stock_scratch.sql I'm making an update to the
columns that are then "aggregated" in the tsvector.

All scripts are wrapped in transactions and are run serially.

What's happening? How to prevent it?

I'd expect that previous scripts don't interfere with the deadlocked
one and at that moment the write activity on the table on which the
triggers are acting is minimal if not absent.

But I suspect my understanding of how these things work is very
naive... so some general clue would be appreciated as well.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rafal Pietrak 2009-06-05 09:26:55 sequences and RULEs
Previous Message Albe Laurenz 2009-06-05 07:11:34 Re: recursive execute