Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 19 '12 at 22:36

This question came from our site for professional and enthusiast programmers.

    
My first instinct would be to disable the application during this process... Or perhaps you can make slony lock more aggressively to prevent the application from gaining locks? –  Frank Farmer Jan 19 '12 at 20:32
    
Thanks Frank. But I am doing this on a HA setup and application is entering data in secondary DB and I can not disable that –  user1159389 Jan 19 '12 at 20:39
    
Are you unable to remove it from the HA cluster before uninstalling slony? –  Ilion Jan 20 '12 at 5:14
    
Yes, while dropping the node I get the deadlock –  user1159389 Jan 20 '12 at 17:03
    
Are you running drop node or uninstall node first? –  Ilion Jan 22 '12 at 12:03
show 1 more comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.