Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I've been having this:
> psql:include/custom/import_update.custom.sql:63: ERROR: deadlock
> detected DETAIL: Process 13349 waits for AccessExclusiveLock on
> relation 250510 of database 248569; blocked by process 14153.
> Process 14153 waits for ShareLock on transaction 59160779; blocked
> by process 13349. CONTEXT: SQL statement "drop trigger if exists
> FT1IDX_catalog_brands_update_trigger on catalog_brands" PL/pgSQL
> function "ft1idx_trigger_drop" line 2 at SQL statement
I'd suggest not using DROP TRIGGER in operations that need to run
concurrently with other accesses to the same table. Consider fixing
things so the trigger is always there but knows enough to not do
anything when it doesn't need to.
The particular case here seems to be that the transaction doing DROP
has already modified some rows in the same table, and there are now
other transactions blocked waiting to modify those same rows. Perhaps
you could avoid combining the DROP with the data modifications. Or
if you must do it like that, take exclusive lock via LOCK TABLE at the
start of the whole transaction, so you aren't trying to do a lock
upgrade partway through.
regards, tom lane
