Re: Function execution is taking more than 2hrs

Raghavendra Rao J S V wrote:
> 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.

That is a sign of a bad design.  Any value that can change should only
occur once in the database.

200000 rows is not a lot; you should stick with international measures
to make yourself understood.

> I have created a dynamic procedure  using EXECUTE FORMAT ,which accepts table name and column name as input parameter
asbelow.
 
>  
> CREATE OR REPLACE FUNCTION pop_endpoints_with_new_deviceid()
> RETURNS void
> AS $$
[...]
>             PERFORM pop_new_deviceid_for_table(‘Table_Name1','deviceid');
[...]
> $$ LANGUAGE plpgsql;
>  
>  
> CREATE OR REPLACE FUNCTION pop_new_deviceid_for_table(p_table varchar,p_column varchar)
> RETURNS void
> AS $$
[...]
>               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
[...]
>               FOR rec IN EXECUTE v_select    LOOP
[...]
>                   EXECUTE FORMAT('UPDATE %I set %I = %s where
%I=%s',p_table,p_column,rec.deviceid_new,p_column,rec.deviceid_old);
[...]
>               END LOOP;
[...]
> $$ LANGUAGE plpgsql;
[...]
> 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

The problem is clear.  Rather than changing all rows with a single UPDATE
statement, you perform one UPDATE per row.

> How to tack the time taken by each function in postgres?

You could use pg_stat_statements with pg_stat_statements.track = all
or use PL Profiler: https://bitbucket.org/openscg/plprofiler

Yours,
Laurenz Albe