Steve Singer-2 wrote:
> 
> On Mon, 26 Nov 2007, Jacques Caron wrote:
>> One thing that would actually add more complexity, but would probably be 
>> quite useful: add options to EXECUTE SCRIPT to only lock (and 
>> un-trigger/re-trigger) specific tables (i.e. the ones that are actually 
>> affected, but manually specified in the options of EXECUTE SCRIPT). No
>> sense 
>> locking everybody, removing and restoring trigger all over the place, if
>> you 
>> only change one table with no side effects (no constraints, triggers,
>> etc.).
> 

This is an under-handed attempt to get attention for something slightly
related from people who may know something about it. Slony locking is
killing us, and I desperately want to understand what and why Slony locks
what it does.

PostgreSQL 8.2, Slony 1.2.12

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 issue that is killing us goes like this....Slony is doing nothing more
than it's every 10 minute cleanupEvents and a rare occasional tiny row
change to replicate. During the evening, we have batch processes that import
large quantities of data into huge history tables and then build indexes on
those tables. Those tables are not in the replication set and do not have
any relationships to or from any other tables. A typical index build may run
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.

So my questions are:

1. When and What tables does Slony lock?
2. Why does Slony lock non-replicated tables?
3. Does Slony 2 alleviate any of these locking issues? (move to PostgreSQL
8.3 required)

While I have your eyes, I wonder if anyone has tips on solving the
logshipping file access BUG I described in detail here:
http://www.nabble.com/BUG--­Logshipping-causes-failure-­when-slony-waiting-on-older-­transactions-to-finish-­td21503953.html 

I eagerly hope for a reply, and thank you very much for your time. Thank you
for giving us a pg replication solution!

-- 
View this message in context: http://www.nabble.com/Patch-to-have-a-defined-table-lock-order-tp13888289p21841419.html
Sent from the Slony-I -- General mailing list archive at Nabble.com.