Return Type: integerRebuildListenEntries() 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; v_cnt integer; begin -- ---- -- Grab the central configuration lock -- ---- lock table sl_config_lock; -- 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 and pa_conninfo<>'<event pending>' 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, N2.no_failed as failed 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 , sl_node p 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 and p.no_active and p.no_id=sub_provider ) 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); -- 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. else if exists (select true from sl_set, sl_subscribe, sl_node provider where set_origin = v_row.origin and sub_set = set_id and sub_provider=provider.no_id and provider.no_failed = false 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; end if; end if; if v_row.failed then --for every failed node we delete all sl_listen entries --except via providers (listed in sl_subscribe) --or failover candidates (sl_failover_targets) --we do this to prevent a non-failover candidate --that is more ahead of the failover candidate from --sending events to the failover candidate that --are 'too far ahead' --if the failed node is not an origin for any --node then we don't delete all listen paths --for events from it. Instead we leave --the listen network alone. select count(*) into v_cnt from sl_subscribe sub, sl_set s where s.set_origin=v_row.origin and s.set_id=sub.sub_set; if v_cnt > 0 then delete from sl_listen where li_origin=v_row.origin and li_receiver=v_row.receiver and li_provider not in (select sub_provider from sl_subscribe, sl_set where sub_set=set_id and set_origin=v_row.origin); end if; end if; -- insert into sl_listen -- (li_origin,li_provider,li_receiver) -- SELECT v_row.origin, pa_server -- ,v_row.receiver -- FROM sl_path where -- pa_client=v_row.receiver -- and (v_row.origin,pa_server,v_row.receiver) not in -- (select li_origin,li_provider,li_receiver -- from sl_listen); -- end if; end loop ; return null ; end ;