okbob/plpgsql_check

exception handling makes profiler to report empty values in plpgsql_profiler_function_tb results

pestryakovda opened this issue · 4 comments

pestryakovda commented

the latest version is used 0b417f8
shared_preload_libraries=plpgsql,pg_stat_statements,plpgsql_check
PostgreSQL 15.4 (Debian 15.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

how to reproduce the issue:

create or replace procedure demo_procedure() as
$body$
declare
    l_cur refcursor;
    l_str varchar;
begin
    open l_cur for execute 'select $1 as p_x' using 'some text';
    FETCH l_cur INTO l_str;
    for i in 1..100000000
    loop
        null;
    end loop;
exception
    when no_data_found then
        raise notice '%', l_str;
end;
$body$ language plpgsql;

set plpgsql_check.profiler = on;

call demo_procedure();

The numbers in the report are missing:

sv=# select lineno, avg_time, total_time, source from plpgsql_profiler_function_tb('demo_procedure()');
 lineno | avg_time | total_time |                               source
--------+----------+------------+--------------------------------------------------------------------
      1 |          |            | \r
      2 |          |            | declare\r
      3 |          |            |     l_cur refcursor;\r
      4 |          |            |     l_str varchar;\r
      5 |          |            | begin\r
      6 |          |            |     open l_cur for execute 'select $1 as p_x' using 'some text';\r
      7 |          |            |     FETCH l_cur INTO l_str;\r
      8 |          |            |     for i in 1..100000000\r
      9 |          |            |     loop\r
     10 |          |            |         null;\r
     11 |          |            |     end loop;\r
     12 |          |            | exception\r
     13 |          |            |     when no_data_found then\r
     14 |          |            |         raise notice '%', l_str;\r
     15 |          |            | end;\r
(15 rows)

if I don't use exception handling, it works fine

sv=# select lineno, avg_time, total_time, source from plpgsql_profiler_function_tb('demo_procedure()');
 lineno | avg_time | total_time |                               source
--------+----------+------------+--------------------------------------------------------------------
      1 |          |            | \r
      2 |          |            | declare\r
      3 |          |            |     l_cur refcursor;\r
      4 |          |            |     l_str varchar;\r
      5 |    0.004 |      0.004 | begin\r
      6 |    0.083 |      0.083 |     open l_cur for execute 'select $1 as p_x' using 'some text';\r
      7 |    0.006 |      0.006 |     FETCH l_cur INTO l_str;\r
      8 |  669.861 |    669.861 |     for i in 1..100000000\r
      9 |          |            |     loop\r
     10 |          |            |         null;\r
     11 |          |            |     end loop;\r
     12 |          |            | end;\r
(12 rows)
okbob commented

Interesting, I am able to reproduce it.

okbob commented

should be fixed now - it was my stupid mistake - I forgot some debug code there.

pestryakovda commented

It is fixed
Thank you for the fix!

okbob commented