1.20. addpartiallogindices( )

Function Properties

Language: PLPGSQL

Return Type: integer

Add partial indexes, if possible, to the unused sl_log_? table for all origin nodes, and drop any that are no longer needed. This function presently gets run any time set origins are manipulated (FAILOVER, STORE SET, MOVE SET, DROP SET), as well as each time the system switches between sl_log_1 and sl_log_2.

DECLARE
	v_current_status	int4;
	v_log			int4;
	v_dummy		record;
	idef 		text;
	v_count		int4;
BEGIN
	v_count := 0;
	select last_value into v_current_status from sl_log_status;

	-- If status is 2 or 3 --> in process of cleanup --> unsafe to create indices
	if v_current_status in (2, 3) then
		return 0;
	end if;

	if v_current_status = 0 then   -- Which log should get indices?
		v_log := 2;
	else
		v_log := 1;
	end if;

	-- Add missing indices...
	for v_dummy in select distinct set_origin from sl_set
		where not exists 
                     (select * from pg_catalog.pg_indexes where schemaname = 'schemadoc'
                      and tablename = 'sl_log_' || v_log and 
                      indexname = 'PartInd_schemadoc_sl_log_' || v_log || '-node-' || set_origin) loop	   
		idef := 'create index "PartInd_schemadoc_sl_log_' || v_log || '-node-' || v_dummy.set_origin ||
                        '" on sl_log_' || v_log || ' USING btree(log_xid xxid_ops) where (log_origin = ' || v_dummy.set_origin || ');';
		execute idef;
		v_count := v_count + 1;
	end loop;

	-- Remove unneeded indices...
	for v_dummy in select indexname from pg_catalog.pg_indexes i where i.schemaname = '@NAMESPACE'
                       and i.tablename = 'sl_log_' || v_log and
                       not exists (select 1 from sl_set where
				i.indexname = 'PartInd_schemadoc_sl_log_' || v_log || '-node-' || set_origin)
	loop
		idef := 'drop index "schemadoc"."' || v_dummy.indexname || '";';
		execute idef;
		v_count := v_count - 1;
	end loop;
	return v_count;
END