http://www.slony.info/bugzilla/show_bug.cgi?id=222

           Summary: cleanup thread can lock out new application
                    connections
           Product: Slony-I
           Version: 2.0
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: enhancement
          Priority: low
         Component: trigger SPI
        AssignedTo: slony1-bugs at lists.slony.info
        ReportedBy: ssinger at ca.afilias.info
                CC: slony1-bugs at lists.slony.info
   Estimated Hours: 0.0


We've had a report of the following scenario

1.  A remote slon executes the log fetch query.  This transaction will obtain
AccessShare locks on sl_log_1 and sl_log_2.    This transaction runs for a
while (in the example we were sent this took an unusually long time due to
network/WAN issues)

2. The cleanupThread ones and determines  logswitch_finish() is ready to
truncate a log table, ie sl_log_1.   The cleanup thread transaction blocks at
the 'trucnate sl_log_1' command.  It is blocking waiting for the AccessShare
lock to go away.

3. A new connection to the database is started, this connection tries to insert
into a replicated table.  The first time the log trigger runs it prepares
prepared statements for inserting into both sl_log_1 and sl_log_2.  Preparing
these requires a row exclusive lock on the table but this is stuck waiting for
the cleanupThread to obtain (and then finish with) its lock.  

This is only an issue if the connection in step (3) is a new connection, the
actual row into sl_log_x will not be inserted into the table being truncated
but because we prepare the statements ahead of time on both tables the system
is at risk.

The solution is to modify the log trigger to prepare the statements for
sl_log_1 and sl_log_2 the first time THAT statement is needed.

-- 
Configure bugmail: http://www.slony.info/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug.
You are the assignee for the bug.