Slony-I 1.2.23 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 1. Schema schemadoc | Fast Forward | Next |
1.22. 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; v_dummy2 record; idef text; v_count int4; v_iname text; 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 || '-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 and indexname = v_iname; if not found then -- raise notice 'index was not found - add it!'; 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; 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 and i.indexname like ('PartInd_schemadoc_sl_log_' || v_log || '-node-%') and not exists (select 1 from sl_set where i.indexname = 'PartInd_schemadoc_sl_log_' || v_log || '-node-' || set_origin) 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