Deadlock during Slony uninstall and concurrent access of DB

Question

I am using slony1-2.0.4.rc2 and postgresql-8.4.0-0. I faced a deadlock in the slony uninstall process.

I was trying to uninstall node from a cluster, as per slony's documents this requires access exclusive locks on all replicated tables. When the slony process was uninstalling the cluster, at the same time the application inserted a row in one of the replicated tables which I think resulted in a dead lock:

DETAIL: Process 22593 waits for AccessExclusiveLock on relation 16392 of database 16388; blocked by process 11808. Process 11808 waits for AccessShareLock on relation 18366 of database 16388; blocked by process 22593.

Can any body confirm that what I am thinking is correct and suggest a solution for this.

Answer 1

I suspect you are going to have to disable application access during the uninstall process. The basic problem is that Slony uses large numbers of triggers to maintain the binary logs for shipping. These triggers are going to be hard to delete while there is concurrent access.

Answer 2

I ran into the same issue and found a workaround in removing the slony triggers one by one.

select distinct 
    'drop trigger ' || trigger_name || 
    ' on ' || event_object_schema || 
    '.' || event_object_table || ';' as sql 
from information_schema.triggers t 
where t.trigger_name 
like '_slony_%' 
order by 1;

You would replace "slony" with the name of your Slony cluster. Then you run the output in psql.

Finally you can drop the slony schema once any queries that started before you removed the triggers finish running.