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

8.75. lockset(integer)

Function Properties

PLPGSQLinteger
lockSet(set_id) Add a special trigger to all tables of a set that disables access to it.
    declare
    	p_set_id			alias for $1;
    	v_local_node_id		int4;
    	v_set_row			record;
    	v_tab_row			record;
    begin
    	-- ----
    	-- Grab the central configuration lock
    	-- ----
    	lock table sl_config_lock;
    
    	-- ----
    	-- Check that the set exists and that we are the origin
    	-- and that it is not already locked.
    	-- ----
    	v_local_node_id := getLocalNodeId('_schemadoc');
    	select * into v_set_row from sl_set
    			where set_id = p_set_id
    			for update;
    	if not found then
    		raise exception 'Slony-I: set % not found', p_set_id;
    	end if;
    	if v_set_row.set_origin <> v_local_node_id then
    		raise exception 'Slony-I: set % does not originate on local node',
    				p_set_id;
    	end if;
    	if v_set_row.set_locked notnull then
    		raise exception 'Slony-I: set % is already locked', p_set_id;
    	end if;
    
    	-- ----
    	-- Place the lockedSet trigger on all tables in the set.
    	-- ----
    	for v_tab_row in select T.tab_id,
    			slon_quote_brute(PGN.nspname) || '.' ||
    			slon_quote_brute(PGC.relname) as tab_fqname
    			from sl_table T,
    				"pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
    			where T.tab_set = p_set_id
    				and T.tab_reloid = PGC.oid
    				and PGC.relnamespace = PGN.oid
    			order by tab_id
    	loop
    		execute 'create trigger "_schemadoc_lockedset" ' || 
    				'before insert or update or delete on ' ||
    				v_tab_row.tab_fqname || ' for each row execute procedure
    				lockedSet (''_schemadoc'');';
    	end loop;
    
    	-- ----
    	-- Remember our snapshots xmax as for the set locking
    	-- ----
    	update sl_set
    			set set_locked = "pg_catalog".txid_snapshot_xmax("pg_catalog".txid_current_snapshot())
    			where set_id = p_set_id;
    
    	return p_set_id;
    end;

Google
Contact me at cbbrowne@acm.org