Function execution is taking more than 2hrs

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?*

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425