On 06/25/2014 07:54 PM, Soni M wrote:
> I found that many transactions lock the sl_log_x tables.
>
> Why slony can't wait for the lock? How about letting slony wait for the
> lock?
>
> I found that postgres 9.3 has lock_timeout parameter. Can this be used
> in slony?
>


Which transactions are locking sl_log_2 when slony is in that state?

The slony log trigger should only be adding rows to sl_log_1 in this 
state.   If this isn't the case then there is a problem.

The problem with waiting for the lock is other transactions will the 
block and queue up behind the cleanup thread/transaction.



>
> On Wed, Jun 25, 2014 at 10:25 PM, Steve Singer <ssinger at ca.afilias.info
> <mailto:ssinger at ca.afilias.info>> wrote:
>
>     On 06/25/2014 11:21 AM, Soni M wrote:
>
>     Find out why slony can't truncate sl_log_2
>
>     Slony does
>     lock table @NAMESPACE at .sl_log_2 in access exclusive mode nowait;
>
>     so why can't it get the access exclusive lock on sl_log_2,  (hint:
>     maybe query pg_locks to see what process/transaction has a lock on
>     sl_log_2).
>
>
>
>         this is what i found on slony log :
>
>         postgres at myserver:~$ tail -f /var/log/slony1/slon-myserver.__log
>         | grep -i
>         -e lock -e "log switch"
>         NOTICE:  Slony-I: log switch to sl_log_2 complete - truncate
>         sl_log_1
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: log switch to sl_log_1 still in progress -
>         sl_log_2
>         not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: log switch to sl_log_1 still in progress -
>         sl_log_2
>         not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>         NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>
>         Truncating sl_log_2 always failed. and yes the slave is falling
>         behind
>         the master. usually the slave will catch up again later after
>         the busy
>         hours. Maybe this is due to high transactional load. But is
>         there a way
>         we can keep slave to catch up the master?
>
>         Thanks....
>
>
>
>         On Wed, Jun 25, 2014 at 10:04 PM, 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/25/2014 11:00 AM, Soni M wrote:
>
>                  Hello everyone,
>
>                  Is it OK to delete event log that has been replicated
>         in sl_log_x.
>
>
>
>              No you shouldn't do that.
>
>              You should instead figure out why the slony cleanup event isn't
>              performing a log switch.  Perhaps because you have a node
>         behind or
>              because confirmations are not making it back to the origin?
>
>
>
>                  Here's the function I created :
>
>                  CREATE OR REPLACE FUNCTION _slony_example.delete_sl_log()
>                     RETURNS void
>                     LANGUAGE plpgsql
>                  AS $function$
>                  DECLARE
>                            v_origin        int8;
>                            v_seqno         int8;
>                            v_xmin          bigint;
>                  BEGIN
>
>
>                                    for v_origin, v_seqno, v_xmin in
>                                      select ev_origin, ev_seqno,
>                  "pg_catalog".txid_snapshot_____xmin(ev_snapshot) from
>
>                  "_slony_example".sl_event
>                                      where (ev_origin, ev_seqno) in (select
>                  ev_origin,
>                  min(ev_seqno) from "_slony_example".sl_event where
>         ev_type = 'SYNC'
>                  group by ev_origin)
>                                    loop
>
>                  delete from _slony_example.sl_log_1 where log_origin =
>         v_origin and
>                  log_txid < v_xmin;
>                  delete from _slony_example.sl_log_2 where log_origin =
>         v_origin and
>                  log_txid < v_xmin;
>                                    end loop;
>
>                  END;
>                  $function$;
>
>                  The Slony failed to switch sl_log for several hours,
>         and event
>                  log keep
>                  accumulated in a table.
>
>                  Will this action help to reduce slony lag that falls
>         far behind ?
>
>                  Thanks all.
>
>                  --
>                  Regards,
>
>                  Soni Maula Harriz
>
>
>                  ___________________________________________________
>                  Slony1-general mailing list
>                  Slony1-general at lists.slony.____info
>                  <mailto:Slony1-general at lists.__slony.info
>         <mailto:Slony1-general at lists.slony.info>>
>         http://lists.slony.info/____mailman/listinfo/slony1-____general
>         <http://lists.slony.info/__mailman/listinfo/slony1-__general>
>
>
>         <http://lists.slony.info/__mailman/listinfo/slony1-__general
>         <http://lists.slony.info/mailman/listinfo/slony1-general>>
>
>
>
>
>
>         --
>         Regards,
>
>         Soni Maula Harriz
>
>
>
>
>
> --
> Regards,
>
> Soni Maula Harriz