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

8.99. setaddtable_int(integer, integer, text, name, text)

Function Properties

PLPGSQLinteger
setAddTable_int (set_id, tab_id, tab_fqname, tab_idxname, tab_comment) This function processes the SET_ADD_TABLE event on remote nodes, adding a table to replication if the remote node is subscribing to its replication set.
    declare
    
    	p_set_id		alias for $1;
    	p_tab_id		alias for $2;
    	p_fqname		alias for $3;
    	p_tab_idxname		alias for $4;
    	p_tab_comment		alias for $5;
    	v_tab_relname		name;
    	v_tab_nspname		name;
    	v_local_node_id		int4;
    	v_set_origin		int4;
    	v_sub_provider		int4;
    	v_relkind		char;
    	v_tab_reloid		oid;
    	v_pkcand_nn		boolean;
    	v_prec			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 table 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: setAddTable_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 tables OID and check that it is a real table
    	-- ----
    	select PGC.oid, PGC.relkind, PGC.relname, PGN.nspname into v_tab_reloid, v_relkind, v_tab_relname, v_tab_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: setAddTable_int(): table % not found', 
    				p_fqname;
    	end if;
    	if v_relkind != 'r' then
    		raise exception 'Slony-I: setAddTable_int(): % is not a regular table',
    				p_fqname;
    	end if;
    
    	if not exists (select indexrelid
    			from "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGC
    			where PGX.indrelid = v_tab_reloid
    				and PGX.indexrelid = PGC.oid
    				and PGC.relname = p_tab_idxname)
    	then
    		raise exception 'Slony-I: setAddTable_int(): table % has no index %',
    				p_fqname, p_tab_idxname;
    	end if;
    
    	-- ----
    	-- Verify that the columns in the PK (or candidate) are not NULLABLE
    	-- ----
    
    	v_pkcand_nn := 'f';
    	for v_prec in select attname from "pg_catalog".pg_attribute where attrelid = 
                            (select oid from "pg_catalog".pg_class where oid = v_tab_reloid) 
                        and attname in (select attname from "pg_catalog".pg_attribute where 
                                        attrelid = (select oid from "pg_catalog".pg_class PGC, 
                                        "pg_catalog".pg_index PGX where 
                                        PGC.relname = p_tab_idxname and PGX.indexrelid=PGC.oid and
                                        PGX.indrelid = v_tab_reloid)) and attnotnull <> 't'
    	loop
    		raise notice 'Slony-I: setAddTable_int: table % PK column % nullable', p_fqname, v_prec.attname;
    		v_pkcand_nn := 't';
    	end loop;
    	if v_pkcand_nn then
    		raise exception 'Slony-I: setAddTable_int: table % not replicable!', p_fqname;
    	end if;
    
    	select * into v_prec from sl_table where tab_id = p_tab_id;
    	if not found then
    		v_pkcand_nn := 't';  -- No-op -- All is well
    	else
    		raise exception 'Slony-I: setAddTable_int: table id % has already been assigned!', p_tab_id;
    	end if;
    
    	-- ----
    	-- Add the table to sl_table and create the trigger on it.
    	-- ----
    	insert into sl_table
    			(tab_id, tab_reloid, tab_relname, tab_nspname, 
    			tab_set, tab_idxname, tab_altered, tab_comment) 
    			values
    			(p_tab_id, v_tab_reloid, v_tab_relname, v_tab_nspname,
    			p_set_id, p_tab_idxname, false, p_tab_comment);
    	perform alterTableAddTriggers(p_tab_id);
    
    	return p_tab_id;
    end;

Google
Contact me at cbbrowne@acm.org