8.34. cleanupevent(p_interval interval)

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;
	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;
		delete from sl_log_script where log_origin = v_origin and log_txid < v_xmin;
    end loop;
	
	v_rc := logswitch_finish();
	if v_rc = 0 then   -- no switch in progress
		perform logswitch_start();
	end if;

	return 0;
end;