wrong "calls" and "AVG duration" displayed with PL/pgSQL blocks (and triggers)

[legrandlegrand]

when lauching

DO $$ DECLARE i int; BEGIN for i in 1..10 loop execute 'select pg_sleep(2)'; end loop; END $$;

Displayed QUERY in pash is "select pg_sleep($)" and "AVG duration (ms)" is 20 000 being computed on the TOP_LEVEL_QUERY, that is wrong ... In this case "calls" = 1 but should be 10, "calls" and "AVG duration" can not be computed when QUERY and TOP_LEVEL_QUERY are different.

Regards PAscal

[legrandlegrand]

here is an enhancement proposal

replace query by "/ top_level_query / query " Calls = -1 AVG = -1 add a new SQL type = "Child Query"

change ASHDatabasePG10ASH.java

private String queryASH = "SELECT ash_time, "
        + "datname, pid, usesysid, usename, "
        + "application_name, backend_type, "
        + "coalesce(client_hostname, client_addr::text, 'localhost') as client_hostname, "
        + "wait_event_type, wait_event, query, queryid, "
        + "coalesce(query_start, xact_start, backend_start) as query_start, 1000 * EXTRACT(EPOCH FROM (ash_time - coalesce(query_start, xact_start, backend_start))) as duration "
        + "from pg_active_session_history "
        + "where state='active' and pid != pg_backend_pid()";

with

private String queryASH = "SELECT ash_time, "
        + "datname, pid, usesysid, usename, "
        + "application_name, backend_type, "
        + "coalesce(client_hostname, client_addr::text, 'localhost') as client_hostname, "
        + "wait_event_type, wait_event, case when top_level_query like query||'%' then query else '/* '||top_level_query||' */ '||query end query, queryid, "
        + "coalesce(query_start, xact_start, backend_start) as query_start, case when top_level_query like query||'%' then 1000 * EXTRACT(EPOCH FROM (ash_time - coalesce(query_start, xact_start, backend_start))) else -1 end as duration "
        + "from pg_active_session_history "
        + "where state='active' and pid != pg_backend_pid()";

add in Gantt.java

        if(callCount > 0) avgDuration = sumDuration/callCount;
        if(avgDuration < 0) callCount = -1L;