On 07/08/2014 11:14 AM, Jan Wieck wrote:
> Why and what application is holding locks on the Slony log tables?
>

His application will hold locks on the sl_log tables by virtue of them 
inserting data into replicated tables.

The application performs an insert => call the log trigger => inserts 
into sl_log_X which will aquire a non exclusive lock for the duration of 
the transaction.

You can't tell from sl_log if the 'log trigger' got the lock or if the 
application directly used the log tables.

My reading of the code in logswitch_finish for current_status=3 is that 
it tries to get the exclusive lock before determining if the table is 
empty or not.   Does this mean if the slon remoteHelper thread is 
pulling data from the log tables (since with log state=3 it queries both 
tables) it will stop the logswitch from going ahead?



> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>
> On Jul 8, 2014 11:06 AM, "Soni M" <diptatapa at gmail.com
> <mailto:diptatapa at gmail.com>> wrote:
>
>     Sorry for late response,
>     currently :
>     NOTICE:  Slony-I: could not lock sl_log_1 - sl_log_1 not truncated
>     dbname=# select last_value from sl_log_status;
>       last_value
>     ------------
>                3
>     (1 row)
>
>     That's what it should be, right?
>
>     But now, i see application transaction holding locks on sl_log_1,
>     some select, insert and update transaction and also the <IDLE> in
>     transaction just like before. But the row numbers of sl_log_1 not
>     increasing anyway. The row num is increasing in sl_log_2. Is that
>     normal?
>
>     The bug on 2.0.x as You said does happen in our environment, and it
>     happens when the slave falls further behind the master. I should
>     plan to move to 2.1 or 2.2.
>
>
>
>     On Wed, Jul 2, 2014 at 11:02 PM, Steve Singer
>     <ssinger at ca.afilias.info <mailto:ssinger at ca.afilias.info>> wrote:
>
>         On 07/01/2014 08:24 PM, Soni M wrote:
>
>
>             Master : Ubuntu 10.04 LTS, Postgre 9.1.13, Slony 2.0.7 from
>             Ubuntu Package
>             Slave : Ubuntu 10.04 LTS, Postgres 9.1.13, Slony 2.0.7 seems
>             build from
>
>
>
>         I should also point out that slony 2.0.x does not properly
>         support PG 9.1 or higher.  You should use slony 2.1.x or 2.2.x
>         with PG 9.1+.
>
>         If you are seeing a lot of transactions being aborted due to
>         serialization conflicts then this is because of the issues with
>         PG 9.1 and slony 2.0 (though it doesn't sound like that is
>         causing this particular issue)
>
>
>
>             source.
>             DB size 1.5 TB, Master utilize relative high number of temp
>             tables and
>             relative High number of locks on busy time
>
>             DB=# select mode, count(*) from pg_locks group by mode;
>                          mode           | count
>             --------------------------+---__-----
>                ExclusiveLock            |    112
>                RowShareLock             |     37
>                AccessExclusiveLock      | 208577
>                RowExclusiveLock         |  33087
>                ShareUpdateExclusiveLock |      5
>                ShareLock                |  54906
>                AccessShareLock          |  93607
>             (7 rows)
>             but the slave is relative low on load.
>
>             this is the connection from slave that open from May 25th
>             postgres 14090  0.1  1.1 4512644 230760 ?      Ss   May25  59:08
>             postgres: slony_user dbname master_ip_address(58554) idle
>
>             On slave, this connection seems always keep on idle status,
>             but on
>             master, this connection often in "<IDLE> in transaction"
>             status for some
>             minutes and hold lock on sl_log_2 while transaction are
>             filling up sl_log_1.
>
>             Such condition usually happen on server high load time, but
>             on low load
>             it sometimes happen, but not many and does not affect on
>             replication lag.
>
>             On Wed, Jul 2, 2014 at 12:37 AM, Steve Singer
>             <ssinger at ca.afilias.info <mailto:ssinger at ca.afilias.info>
>             <mailto:ssinger at ca.afilias.__info
>             <mailto:ssinger at ca.afilias.info>>> wrote:
>
>                  On 06/30/2014 01:05 PM, Soni M wrote:
>
>                      it seems a slony process that has <IDLE> in
>             transaction for many
>                      times.
>                      the client address and the user are identical to
>             slony slave.
>
>
>
>                  Which version of slony are you on?
>
>
>
>
>
>             --
>             Regards,
>
>             Soni Maula Harriz
>
>
>
>
>
>     --
>     Regards,
>
>     Soni Maula Harriz
>
>     _______________________________________________
>     Slony1-general mailing list
>     Slony1-general at lists.slony.info <mailto:Slony1-general at lists.slony.info>
>     http://lists.slony.info/mailman/listinfo/slony1-general
>