## 1.20. cleanupevent( )

Function Properties
Language: PLPGSQL
Return Type: integer

cleaning old data out of sl_confirm, sl_event. Removes all but the last sl_confirm row per (origin,receiver), and then removes all events that are confirmed by all nodes in the whole cluster up to the last SYNC.

    declare
    	v_max_row	record;
    	v_min_row	record;
    	v_max_sync	int8;
    begin
    	-- ----
    	-- First remove all but the oldest confirm row per origin,receiver pair
    	-- ----
    	delete from sl_confirm
    				where con_origin not in (select no_id from sl_node);
    	delete from sl_confirm
    				where con_received not in (select no_id from sl_node);
    	-- ----
    	-- Next remove all but the oldest confirm row per origin,receiver pair.
    	-- Ignore confirmations that are younger than 10 minutes. We currently
    	-- have an not confirmed suspicion that a possibly lost transaction due
    	-- to a server crash might have been visible to another session, and
    	-- that this led to log data that is needed again got removed.
    	-- ----
    	for v_max_row in select con_origin, con_received, max(con_seqno) as con_seqno
    				from sl_confirm
    				where con_timestamp < (CURRENT_TIMESTAMP - '10 min'::interval)
    				group by con_origin, con_received
    	loop
    		delete from sl_confirm
    				where con_origin = v_max_row.con_origin
    				and con_received = v_max_row.con_received
    				and con_seqno < v_max_row.con_seqno;
    	end loop;

    	-- ----
    	-- Then remove all events that are confirmed by all nodes in the
    	-- whole cluster up to the last SYNC
    	-- ----
    	for v_min_row in select con_origin, min(con_seqno) as con_seqno
    				from sl_confirm
    				group by con_origin
    	loop
    		select coalesce(max(ev_seqno), 0) into v_max_sync
    				from sl_event
    				where ev_origin = v_min_row.con_origin
    				and ev_seqno <= v_min_row.con_seqno
    				and ev_type = 'SYNC';
    		if v_max_sync > 0 then
    			delete from sl_event
    					where ev_origin = v_min_row.con_origin
    					and ev_seqno < v_max_sync;
    		end if;
    	end loop;

    	return 0;
    end;