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

8.84. rebuildlistenentries()

Function Properties

PLPGSQLinteger
RebuildListenEntries() Invoked by various subscription and path modifying functions, this rewrites the sl_listen entries, adding in all the ones required to allow communications between nodes in the Slony-I cluster.
    declare
    	v_row	record;
    begin
    	-- First remove the entire configuration
    	delete from sl_listen;
    
    	-- Second populate the sl_listen configuration with a full
    	-- network of all possible paths.
    	insert into sl_listen
    				(li_origin, li_provider, li_receiver)
    			select pa_server, pa_server, pa_client from sl_path;
    	while true loop
    		insert into sl_listen
    					(li_origin, li_provider, li_receiver)
    			select distinct li_origin, pa_server, pa_client
    				from sl_listen, sl_path
    				where li_receiver = pa_server
    				  and li_origin <> pa_client
    			except
    			select li_origin, li_provider, li_receiver
    				from sl_listen;
    
    		if not found then
    			exit;
    		end if;
    	end loop;
    
    	-- We now replace specific event-origin,receiver combinations
    	-- with a configuration that tries to avoid events arriving at
    	-- a node before the data provider actually has the data ready.
    
    	-- Loop over every possible pair of receiver and event origin
    	for v_row in select N1.no_id as receiver, N2.no_id as origin
    			from sl_node as N1, sl_node as N2
    			where N1.no_id <> N2.no_id
    	loop
    		-- 1st choice:
    		-- If we use the event origin as a data provider for any
    		-- set that originates on that very node, we are a direct
    		-- subscriber to that origin and listen there only.
    		if exists (select true from sl_set, sl_subscribe
    				where set_origin = v_row.origin
    				  and sub_set = set_id
    				  and sub_provider = v_row.origin
    				  and sub_receiver = v_row.receiver
    				  and sub_active)
    		then
    			delete from sl_listen
    				where li_origin = v_row.origin
    				  and li_receiver = v_row.receiver;
    			insert into sl_listen (li_origin, li_provider, li_receiver)
    				values (v_row.origin, v_row.origin, v_row.receiver);
    			continue;
    		end if;
    
    		-- 2nd choice:
    		-- If we are subscribed to any set originating on this
    		-- event origin, we want to listen on all data providers
    		-- we use for this origin. We are a cascaded subscriber
    		-- for sets from this node.
    		if exists (select true from sl_set, sl_subscribe
    						where set_origin = v_row.origin
    						  and sub_set = set_id
    						  and sub_receiver = v_row.receiver
    						  and sub_active)
    		then
    			delete from sl_listen
    					where li_origin = v_row.origin
    					  and li_receiver = v_row.receiver;
    			insert into sl_listen (li_origin, li_provider, li_receiver)
    					select distinct set_origin, sub_provider, v_row.receiver
    						from sl_set, sl_subscribe
    						where set_origin = v_row.origin
    						  and sub_set = set_id
    						  and sub_receiver = v_row.receiver
    						  and sub_active;
    			continue;
    		end if;
    
    	end loop ;
    
    	return null ;
    end ;

Google
Contact me at cbbrowne@acm.org