Return Type: integercleaning 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;