DECLARE
v_current_status int4;
v_log int4;
v_dummy record;
v_dummy2 record;
idef text;
v_count int4;
v_iname text;
v_ilen int4;
v_maxlen 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;
-- PartInd_test_db_sl_log_2-node-1
-- Add missing indices...
for v_dummy in select distinct set_origin from sl_set loop
v_iname := 'PartInd_schemadoc_sl_log_' || v_log::text || '-node-' || v_dummy.set_origin;
-- raise notice 'Consider adding partial index % on sl_log_%', v_iname, v_log;
-- raise notice 'schema: [_schemadoc] tablename:[sl_log_%]', v_log;
select * into v_dummy2 from pg_catalog.pg_indexes where tablename = 'sl_log_' || v_log::text and indexname = v_iname;
if not found then
-- raise notice 'index was not found - add it!';
v_iname := 'PartInd_schemadoc_sl_log_' || v_log::text || '-node-' || v_dummy.set_origin;
v_ilen := pg_catalog.length(v_iname);
v_maxlen := pg_catalog.current_setting('max_identifier_length'::text)::int4;
if v_ilen > v_maxlen then
raise exception 'Length of proposed index name [%] > max_identifier_length [%] - cluster name probably too long', v_ilen, v_maxlen;
end if;
idef := 'create index "' || v_iname ||
'" on sl_log_' || v_log::text || ' USING btree(log_txid) where (log_origin = ' || v_dummy.set_origin::text || ');';
execute idef;
v_count := v_count + 1;
else
-- raise notice 'Index % already present - skipping', v_iname;
end if;
end loop;
-- Remove unneeded indices...
for v_dummy in select indexname from pg_catalog.pg_indexes i where i.tablename = 'sl_log_' || v_log::text and
i.indexname like ('PartInd_schemadoc_sl_log_' || v_log::text || '-node-%') and
not exists (select 1 from sl_set where
i.indexname = 'PartInd_schemadoc_sl_log_' || v_log::text || '-node-' || set_origin::text)
loop
-- raise notice 'Dropping obsolete index %d', v_dummy.indexname;
idef := 'drop index "' || v_dummy.indexname || '";';
execute idef;
v_count := v_count - 1;
end loop;
return v_count;
END |