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

8.76. logswitch_finish()

Function Properties

PLPGSQLinteger
logswitch_finish() Attempt to finalize a log table switch in progress return values: -1 if switch in progress, but not complete 0 if no switch in progress 1 if performed truncate on sl_log_2 2 if performed truncate on sl_log_1
    DECLARE
    	v_current_status	int4;
    	v_dummy				record;
    	v_origin	int8;
    	v_seqno		int8;
    	v_xmin		bigint;
    	v_purgeable boolean;
    BEGIN
    	-- ----
    	-- Grab the central configuration lock to prevent race conditions
    	-- while changing the sl_log_status sequence value.
    	-- ----
    	lock table sl_config_lock;
    
    	-- ----
    	-- Get the current log status.
    	-- ----
    	select last_value into v_current_status from sl_log_status;
    
    	-- ----
    	-- status value 0 or 1 means that there is no log switch in progress
    	-- ----
    	if v_current_status = 0 or v_current_status = 1 then
    		return 0;
    	end if;
    
    	-- ----
    	-- status = 2: sl_log_1 active, cleanup sl_log_2
    	-- ----
    	if v_current_status = 2 then
    		v_purgeable := 'true';
    		
    		-- ----
    		-- Attempt to lock sl_log_2 in order to make sure there are no other transactions 
    		-- currently writing to it. Exit if it is still in use. This prevents TRUNCATE from 
    		-- blocking writers to sl_log_2 while it is waiting for a lock. It also prevents it 
    		-- immediately truncating log data generated inside the transaction which was active 
    		-- when logswitch_finish() was called (and was blocking TRUNCATE) as soon as that 
    		-- transaction is committed.
    		-- ----
    		begin
    			lock table sl_log_2 in exclusive mode nowait;
    		exception when lock_not_available then
    			raise notice 'Slony-I: could not lock sl_log_2 - sl_log_2 not truncated';
    			return -1;
    		end;
    
    		-- ----
    		-- The cleanup thread calls us after it did the delete and
    		-- vacuum of both log tables. If sl_log_2 is empty now, we
    		-- can truncate it and the log switch is done.
    		-- ----
    	        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
    			if exists (select 1 from sl_log_2 where log_origin = v_origin and log_txid >= v_xmin limit 1) then
    				v_purgeable := 'false';
    			end if;
    	        end loop;
    		if not v_purgeable then
    			-- ----
    			-- Found a row ... log switch is still in progress.
    			-- ----
    			raise notice 'Slony-I: log switch to sl_log_1 still in progress - sl_log_2 not truncated';
    			return -1;
    		end if;
    
    		raise notice 'Slony-I: log switch to sl_log_1 complete - truncate sl_log_2';
    		truncate sl_log_2;
    		if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_log_2' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then
    	                execute 'alter table sl_log_2 set without oids;';
    		end if;		
    		perform "pg_catalog".setval('sl_log_status', 0);
    		-- Run addPartialLogIndices() to try to add indices to unused sl_log_? table
    		perform addPartialLogIndices();
    
    		return 1;
    	end if;
    
    	-- ----
    	-- status = 3: sl_log_2 active, cleanup sl_log_1
    	-- ----
    	if v_current_status = 3 then
    		v_purgeable := 'true';
    
    		-- ----
    		-- Attempt to lock sl_log_1 in order to make sure there are no other transactions 
    		-- currently writing to it. Exit if it is still in use. This prevents TRUNCATE from 
    		-- blocking writes to sl_log_1 while it is waiting for a lock. It also prevents it 
    		-- immediately truncating log data generated inside the transaction which was active 
    		-- when logswitch_finish() was called (and was blocking TRUNCATE) as soon as that 
    		-- transaction is committed.
    		-- ----
    		begin
    			lock table sl_log_1 in exclusive mode nowait;
    		exception when lock_not_available then
    			raise notice 'Slony-I: could not lock sl_log_1 - sl_log_1 not truncated';
    			return -1;
    		end;
    
    		-- ----
    		-- The cleanup thread calls us after it did the delete and
    		-- vacuum of both log tables. If sl_log_2 is empty now, we
    		-- can truncate it and the log switch is done.
    		-- ----
    	        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
    			if (exists (select 1 from sl_log_1 where log_origin = v_origin and log_txid >= v_xmin limit 1)) then
    				v_purgeable := 'false';
    			end if;
    	        end loop;
    		if not v_purgeable then
    			-- ----
    			-- Found a row ... log switch is still in progress.
    			-- ----
    			raise notice 'Slony-I: log switch to sl_log_2 still in progress - sl_log_1 not truncated';
    			return -1;
    		end if;
    
    		raise notice 'Slony-I: log switch to sl_log_2 complete - truncate sl_log_1';
    		truncate sl_log_1;
    		if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_log_1' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then
    	                execute 'alter table sl_log_1 set without oids;';
    		end if;		
    		perform "pg_catalog".setval('sl_log_status', 1);
    		-- Run addPartialLogIndices() to try to add indices to unused sl_log_? table
    		perform addPartialLogIndices();
    		return 2;
    	end if;
    END;

Google
Contact me at cbbrowne@acm.org