Right you are! That query returns five rows from sl_event from 2005-08-18 -
one's got ev_origin = <master node> and the other four are from the four
slave nodes.

I don't think I should be getting messages from the slaves to the master in
the first place - I deleted the rows from sl_listen where li_origin !=
<master_node> because it seemed to solve a problem I was having with the
whole thing falling over, plus it cut down on the logging - but that's
another story.

I deleted the five rows from sl_event, there's nothing in sl_confirm, and
I'm still waiting for it to clean out sl_log_1...I'll see how we go!

thanks,
Len

On 1/10/06, Christopher Browne <cbbrowne at ca.afilias.info> wrote:
>
> Len Walter wrote:
>
> >G'day ,
> >
> >My environment is postgres 7.4.8 / Slony 1.1.0 / RHEL4ES on x86.
> >
> >I've got a three-or-four month old slony cluster which currently has a
> >large sl_log_1 table:
> >db=# select count(*) from _slony.sl_log_1;
> >  count
> >---------
> > 1031377
> >(1 row)
> >
> >I've run the step at http://cbbrowne.com/info/faq.html#AEN42511 and
> >deleted a few old entries from sl_confirm, then waited for the cleanup
> >thread to purge the log table, as well as running the task manually.
> >
> >The FAQ says (in the same entry) "each cleanupEvent run .... is the
> >event in which old data is purged from sl_log_1 and sl_seqlog".
> >However, I've looked at the function as installed in the slony schema
> >and it doesn't do that, as far as I can tell. (attached below)
> >
> >Can anyone give me a pointer to finding where this task does get run?
> >
> >
> The trouble here is that "cleanupEvent" is overloaded.  There are two
> things by that name:
>
> 1.  There is a pl/pgsql function, cleanupEvent(), in
> src/backend/slony1_funcs.sql, which is strictly about cleaning out events
>
> 2.  There is a C function, cleanupEvent_main(), in
> src/slon/cleanup_thread.c, which runs the pl/pgsql function *as well as*
> doing other things that notably includes purging data from sl_log_1.
>
> You might look at the queries in src/slon/cleanup_thread.c to see what
> it is doing.
>
> The query most likely to be useful is the following...
>
>                      "select ev_origin, ev_seqno, ev_minxid "
>                      "from %s.sl_event "
>                      "where (ev_origin, ev_seqno) in "
>                      "    (select ev_origin, min(ev_seqno) "
>                      "     from %s.sl_event "
>                      "     where ev_type = 'SYNC' "
>                      "     group by ev_origin); ",
>
> If confirmations haven't made it back from some node, that would block
> deletions from taking place...
>
> In the past, the following query used to be useful; it ought to be
> obsolete in 1.1...
>
> select * from _oxrsbar.sl_confirm where con_origin not in (select no_id
> from _oxrsbar.sl_node) or con_received not in (select no_id from
> _oxrsbar.sl_node)
>



--
Len Walter len.walter at gmail.com http://crookedtimbre.net skype:len.walter
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://gborg.postgresql.org/pipermail/slony1-general/attachments/20060112/cf2e3054/attachment.html