Wrong statistics for overloaded functions (RM #569)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/569 Originally created by Anonymous at 2011-07-06 19:19:20 UTC.

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';

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/569#note-1 Originally created by Anonymous at 2011-07-07 12:16:32 UTC.

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;

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/569#note-2 Originally created by Guillaume Lelarge at 2011-07-07 21:41:12 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	New	In Progress

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/569#note-3 Originally created by Guillaume Lelarge at 2011-07-07 21:41:23 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	In Progress	Resolved
Resolution changed		fixed

[dpage]

Issue closed on Redmine.