Thread: [pgAdmin III] #325: Wrong statistics for overloaded functions
#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
#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
------------------------+---------------------------------------------------
Comment(by brsa):
Guillaume:
Rather use the funcid for the query, so the pg_catalog view does not have
to change:
SELECT calls AS "Number of calls", total_time AS "Total Time", self_time
AS "Self Time" FROM pg_stat_user_functions WHERE funcid = '9273008'::oid;
--
Ticket URL: <http://code.pgadmin.org/trac/ticket/325#comment:1>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III
#325: Wrong statistics for overloaded functions
------------------------+---------------------------------------------------
Reporter: brsa | Owner: gleu
Type: bug | Status: assigned
Priority: minor | Milestone:
Component: pgadmin | Version: trunk
Keywords: statistics | Platform: all
------------------------+---------------------------------------------------
Changes (by gleu):
* status: new => assigned
* owner: dpage => gleu
--
Ticket URL: <http://code.pgadmin.org/trac/ticket/325#comment:2>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III
#325: Wrong statistics for overloaded functions
----------------------+-----------------------------------------------------
Reporter: brsa | Owner: gleu
Type: bug | Status: closed
Priority: minor | Milestone: 1.14
Component: pgadmin | Version: trunk
Resolution: fixed | Keywords: statistics
Platform: all |
----------------------+-----------------------------------------------------
Changes (by gleu):
* status: assigned => closed
* resolution: => fixed
* milestone: => 1.14
--
Ticket URL: <http://code.pgadmin.org/trac/ticket/325#comment:3>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III