On Wed, Feb 04, 2009 at 02:46:35PM -0800, TroyWolf wrote:
> 
> I don't think I've ever found a good answer to this question: Why does Slony
> lock ALL tables in the database regardless of whether those tables have
> anything to do with the objects in the replication set? I explained a
> real-world issue in detail in a post to this group, but never received any
> real help. (See
> http://www.nabble.com/Re:-Slony-locks-tables-that-are-not-even-in-replication-sets--td15677103.html#a15677103)

The link you post there doesn't suggest that slony _does_ lock all
tables, and indeed that link includes a quote from me asking for more
information, which you didn't provide.  We can't give you "real help"
if we don't have enough data.

There are several levels of lock in Postgres, and I'm not even sure
that what you are attributing to Slony is its fault.  Slony should
_not_ lock tables that it doesn't know about.  

But you should understand that Slony needs access to tables.  Index
creation is a blocking operation.  There may, however, be a clue in
what you say.

> 30 minutes or more--this is normal and expected. However, on occasion, Slony
> will start to do SOMETHING that apparently locks some tables but then gets
> stuck behind another transaction---like that long running index build.
> During this entire time Slony is waiting, the locks he took out on the other
> tables prevents other processes from accessing those tables. These other,
> locked tables are also NOT IN ANY REPLICATION SET. The fact that these other
> processes are blocked is a terrible problem for us. I cannot think of any
> reason Slony needs to lock those tables.

Are there any foreign keys involved?  Remember that the FK stuff is
implemented with triggers that themselves attempt locks.  If Slony's
transaction causes such a lock to fire, it'll have to wait in line to
do what it wants.  It also has to wait in line behind something else
that has a lock blocking Slony writes.  You seem to think that Slony
might be trying to "lock" something, but I suspect what's really
happening is that Slony is trying to get a low-level lock, and can't.

All of this is rank speculation, however, without accurate details
from pg_locks and access to the system catalogue to understand what's
locked and how.

A


-- 
Andrew Sullivan
ajs at crankycanuck.ca