Device id column logic has changed in my organization. Therefore, I need to modify all the old device id values to new device id value of the tables which contains the device id column. Old device id and new device id columns are mapped in “old_new_deviceids” table.
There are twenty tables which contains device id column. Therefore I need to modify the device id’s in all those twenty tables based on “old_new_deviceids” table . Each and every table will contains around 2Lakhs records.
I have created a dynamic procedure using EXECUTE FORMAT ,which accepts table name and column name as input parameter as below.
CREATE OR REPLACE FUNCTION pop_endpoints_with_new_deviceid()
RETURNS void
AS $$
DECLARE
--tables text[] = ARRAY['tcconfig_endpointlist','medianode','calldetailrecord','calldetailrecord','statsciscotbgcallstreamsource','statsciscotbgcallchannelsaudio','statsciscotbgcallchannelsvideo','statsciscotbgperipheral','statsciscotbgperipheralhistory','statsciscotbgcall','statsciscotpcall','statsciscotpcallstreamsource','statsciscotpperipheral','statsciscotpperipheralhistory','statsciscotpcallstreamtype','statsciscophonecallstream','monthlyendpointnoshow','monthlyendpointutilization','mtg_src_nd_prtcpnts'];
--columns text[]= ARRAY['element','deviceid','deviceid','destdeviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','deviceid','source'];
--v_select varchar(5000);
-- rec record;
BEGIN
--This funciton updates the deviceid column for all tables which contains endpoint details using endpoint_deviceids_barediscovery table through pop_new_deviceid funciton
RAISE NOTICE 'Updation of deviceid column for dependent tables which contains endpoints related information has started';
PERFORM insert_log('INFO' ,'pop_endpoints_with_new_deviceid' ,'Updation of deviceid column for dependent tables which contains endpoints related information has started');
PERFORM pop_new_deviceid_for_table(‘Table_Name1','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name2','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name3','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name4','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name5','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name6','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name7','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name8','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name9','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name10','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name11','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name12',deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name13','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name14','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name15','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name16','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name17','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name18','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name19','deviceid');
PERFORM pop_new_deviceid_for_table(‘Table_Name20','deviceid');
RAISE NOTICE 'Updation of deviceid column for dependent tables which contains endpoints related information has completed successfully';
PERFORM insert_log('INFO' ,'pop_endpoints_with_new_deviceid' ,'Updation of deviceid column for dependent tables which contains endpoints related information has completed successfully');
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Error occurred while executing pop_endpoints_with_new_deviceid % %', SQLERRM, SQLSTATE;
PERFORM insert_log('ERROR' ,'pop_endpoints_with_new_deviceid' ,'Error occurred while executing pop_endpoints_with_new_deviceid '||SQLSTATE||' '||SQLERRM);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION pop_new_deviceid_for_table(p_table varchar,p_column varchar)
RETURNS void
AS $$
DECLARE
v_select varchar(5000);
id_error_count int:=0;
rec record;
BEGIN
--This funciton updates the deviceid column for spcified table using endpoint_deviceids_barediscovery table after rediscovery
v_select:='SELECT distinct t2.deviceid_old,t2.deviceid_new
FROM '|| p_table ||' t1,endpoint_deviceids_barediscovery t2
WHERE t1.'||p_column||'=t2.deviceid_old
AND t2.deviceid_new is not null';
RAISE NOTICE 'Updation of endpoints with newdeviceid for % started and query is %',p_table,v_select;
PERFORM insert_log('INFO' ,'pop_new_deviceid_for_table' ,'Updation of endpoints with newdeviceid for '||p_table||' started.Query is '|| v_select);
FOR rec IN EXECUTE v_select LOOP
BEGIN
EXECUTE FORMAT('UPDATE %I set %I = %s where %I=%s',p_table,p_column,rec.deviceid_new,p_column,rec.deviceid_old);
EXCEPTION
WHEN OTHERS THEN
id_error_count:=id_error_count+1;
RAISE NOTICE 'Error occurred while updating new deviceid column of % table for deviceid (%) % using pop_new_deviceid_for_table % %',p_table,p_column,rec.deviceid_old, SQLERRM, SQLSTATE;
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Error occurred while executing pop_new_deviceid_for_table for % table % %', p_table,SQLERRM, SQLSTATE;
PERFORM insert_log('ERROR' ,'pop_new_deviceid_for_table' ,'Error occurred while executing pop_endpoints_with_old_deviceid for '||p_table||' table '||SQLSTATE||' '||SQLERRM);
END;
$$ LANGUAGE plpgsql;
When I execute select pop_endpoints_with_new_deviceid() it will update 20 tables in single shot. Some of the environments it got completed in 5 minutes and some of the environments it is taking around 2hrs 25 minutes. I have experienced this issue several times with different environments. But the data and configuration settings of the all environments are same. There are no locks in the database while this script is executing.
Please guide me
Sometimes “select pop_endpoints_with_new_deviceid()” is taking just 5 minutes and some times more than 2hrs 25 minutes. how to narrow down the issue
How to do the bulk update /insert/delete in postgres? Do I need to modify any configuration parameters in the database?
How to tack the time taken by each function in postgres?
Raghavendra Rao J S V
Mobile- 8861161425
