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?