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

8.97. setaddsequence_int(integer, integer, text, text)

Function Properties

PLPGSQLinteger
setAddSequence_int (set_id, seq_id, seq_fqname, seq_comment) This processes the SET_ADD_SEQUENCE event. On remote nodes that subscribe to set_id, add the sequence to the replication set.
    declare
    	p_set_id			alias for $1;
    	p_seq_id			alias for $2;
    	p_fqname			alias for $3;
    	p_seq_comment		alias for $4;
    	v_local_node_id		int4;
    	v_set_origin		int4;
    	v_sub_provider		int4;
    	v_relkind			char;
    	v_seq_reloid		oid;
    	v_seq_relname		name;
    	v_seq_nspname		name;
    	v_sync_row			record;
    begin
    	-- ----
    	-- Grab the central configuration lock
    	-- ----
    	lock table sl_config_lock;
    
    	-- ----
    	-- For sets with a remote origin, check that we are subscribed 
    	-- to that set. Otherwise we ignore the sequence because it might 
    	-- not even exist in our database.
    	-- ----
    	v_local_node_id := getLocalNodeId('_schemadoc');
    	select set_origin into v_set_origin
    			from sl_set
    			where set_id = p_set_id;
    	if not found then
    		raise exception 'Slony-I: setAddSequence_int(): set % not found',
    				p_set_id;
    	end if;
    	if v_set_origin != v_local_node_id then
    		select sub_provider into v_sub_provider
    				from sl_subscribe
    				where sub_set = p_set_id
    				and sub_receiver = getLocalNodeId('_schemadoc');
    		if not found then
    			return 0;
    		end if;
    	end if;
    	
    	-- ----
    	-- Get the sequences OID and check that it is a sequence
    	-- ----
    	select PGC.oid, PGC.relkind, PGC.relname, PGN.nspname 
    		into v_seq_reloid, v_relkind, v_seq_relname, v_seq_nspname
    			from "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
    			where PGC.relnamespace = PGN.oid
    			and slon_quote_input(p_fqname) = slon_quote_brute(PGN.nspname) ||
    					'.' || slon_quote_brute(PGC.relname);
    	if not found then
    		raise exception 'Slony-I: setAddSequence_int(): sequence % not found', 
    				p_fqname;
    	end if;
    	if v_relkind != 'S' then
    		raise exception 'Slony-I: setAddSequence_int(): % is not a sequence',
    				p_fqname;
    	end if;
    
            select 1 into v_sync_row from sl_sequence where seq_id = p_seq_id;
    	if not found then
                   v_relkind := 'o';   -- all is OK
            else
                    raise exception 'Slony-I: setAddSequence_int(): sequence ID % has already been assigned', p_seq_id;
            end if;
    
    	-- ----
    	-- Add the sequence to sl_sequence
    	-- ----
    	insert into sl_sequence
    		(seq_id, seq_reloid, seq_relname, seq_nspname, seq_set, seq_comment) 
    		values
    		(p_seq_id, v_seq_reloid, v_seq_relname, v_seq_nspname,  p_set_id, p_seq_comment);
    
    	-- ----
    	-- On the set origin, fake a sl_seqlog row for the last sync event
    	-- ----
    	if v_set_origin = v_local_node_id then
    		for v_sync_row in select coalesce (max(ev_seqno), 0) as ev_seqno
    				from sl_event
    				where ev_origin = v_local_node_id
    					and ev_type = 'SYNC'
    		loop
    			insert into sl_seqlog
    					(seql_seqid, seql_origin, seql_ev_seqno, 
    					seql_last_value) values
    					(p_seq_id, v_local_node_id, v_sync_row.ev_seqno,
    					sequenceLastValue(p_fqname));
    		end loop;
    	end if;
    
    	return p_seq_id;
    end;

Google
Contact me at cbbrowne@acm.org