[pgAdmin III] #325: Wrong statistics for overloaded functions - Mailing list pgadmin-hackers

#325: Wrong statistics for overloaded functions
------------------------+---------------------------------------------------
 Reporter:  brsa        |       Owner:  dpage
     Type:  bug         |      Status:  new  
 Priority:  minor       |   Milestone:       
Component:  pgadmin     |     Version:  trunk
 Keywords:  statistics  |    Platform:  all  
------------------------+---------------------------------------------------
 Applies to 1.12 as well as 1.14.0 Beta 2(Jun 10...)


 Testcase:

 CREATE FUNCTION f_test(integer) RETURNS boolean AS 'BEGIN RETURN TRUE;
 END;' LANGUAGE plpgsql;
 CREATE FUNCTION f_test(text)    RETURNS boolean AS 'BEGIN RETURN TRUE;
 END;' LANGUAGE plpgsql;

 "Statistics"-tab shows the same data of one function for all of them.
 Which one is randomly chosen by the ordering of the bogus query. Try and
 see:

 SELECT f_test('1'::text);
 SELECT f_test('1'::integer);

 ----
 Why?

 postgres allows function overloading, i.e. several functions can be
 identical except for its arguments.
 http://www.postgresql.org/docs/8.4/interactive/xfunc-overload.html

 The statistics view
    pg_catalog.pg_stat_user_functions
 fails to include the field pg_proc.proargtypes.

 Subsequently, the query fails to distinguish between overloaded functions
 like so:

 SELECT calls AS "Number of calls", total_time AS "Total Time", self_time
 AS "Self Time" FROM pg_stat_user_functions WHERE schemaname = 'public' AND
 funcname = 'f_test';

 Returns multiple rows in the case of overloaded functions, the first same
 row is displayed for all of them, which is plain wrong.

 ----
 Fix:

 Corrected Version of pg_stat_user_functions, including p.proargtypes AS
 argtypes:

 CREATE OR REPLACE VIEW pg_stat_user_functions AS
  SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname,
 p.proargtypes AS argtypes, pg_stat_get_function_calls(p.oid) AS calls,
 pg_stat_get_function_time(p.oid) / 1000 AS total_time,
 pg_stat_get_function_self_time(p.oid) / 1000 AS self_time
    FROM pg_proc p
    LEFT JOIN pg_namespace n ON n.oid = p.pronamespace
   WHERE p.prolang <> 12::oid AND pg_stat_get_function_calls(p.oid) IS NOT
 NULL;

 Corrected query for the "Statistics" tab using view above.

 SELECT calls AS "Number of calls", total_time AS "Total Time", self_time
 AS "Self Time" FROM pg_stat_user_functions WHERE schemaname = 'public' AND
 funcname = 'f_test' AND argtypes = '23';

--
Ticket URL: <http://code.pgadmin.org/trac/ticket/325>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III