Christopher B. Browne's Home Page
cbbrowne@acm.org

8.26. cleanupevent(p_interval interval)

Function Properties

PLPGSQLinteger
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;
    	v_origin	int8;
    	v_seqno		int8;
    	v_xmin		bigint;
    	v_rc            int8;
    begin
    	-- ----
    	-- First remove all confirmations where origin/receiver no longer exist
    	-- ----
    	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 - p_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;
    
    	-- ----
    	-- If cluster has only one node, then remove all events up to
    	-- the last SYNC - Bug #1538
            -- http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1538
    	-- ----
    
    	select * into v_min_row from sl_node where
    			no_id <> getLocalNodeId('_schemadoc') limit 1;
    	if not found then
    		select ev_origin, ev_seqno into v_min_row from sl_event
    		where ev_origin = getLocalNodeId('_schemadoc')
    		order by ev_origin desc, ev_seqno desc limit 1;
    		raise notice 'Slony-I: cleanupEvent(): Single node - deleting events < %', v_min_row.ev_seqno;
    			delete from sl_event
    			where
    				ev_origin = v_min_row.ev_origin and
    				ev_seqno < v_min_row.ev_seqno;
    
            end if;
    
    	if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_seqlog' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then
                    execute 'alter table sl_seqlog set without oids;';
    	end if;		
    	-- ----
    	-- Also remove stale entries from the nodelock table.
    	-- ----
    	perform cleanupNodelock();
    
    	-- ----
    	-- Find the eldest event left, for each origin
    	-- ----
        for v_origin, v_seqno, v_xmin in
    	select ev_origin, ev_seqno, "pg_catalog".txid_snapshot_xmin(ev_snapshot) from sl_event
              where (ev_origin, ev_seqno) in (select ev_origin, min(ev_seqno) from sl_event where ev_type = 'SYNC' group by ev_origin)
    	loop
    		delete from sl_seqlog where seql_origin = v_origin and seql_ev_seqno < v_seqno;
        end loop;
    	
    	v_rc := logswitch_finish();
    	if v_rc = 0 then   -- no switch in progress
    		perform logswitch_start();
    	end if;
    
    	return 0;
    end;

Google
Contact me at cbbrowne@acm.org