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

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

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 nave 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(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