Обсуждение: why dropping a trigger may cause a deadlock
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
Ivan Sergio Borgonovo <mail@webthatworks.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
On Fri, 05 Jun 2009 10:46:11 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.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
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. regards, tom lane
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