Postgres: timing the runtime of a function

Question

I want to update an audit table that stores the duration of a function/stored proc,

so far I have

drop table if exists tmp_interval_test;
create table tmp_interval_test (
    id serial primary key,
    duration interval
);

drop function if exists tmp_interval;
create or replace function tmp_interval()
    returns void as 

    $body$
    declare
        sleep int;
        start_time timestamp;
        end_time timestamp;
        diff interval;

    begin
        start_time := now();
        sleep := floor(random() * 10 + 1)::int;

        -- actual code goes here
        perform pg_sleep(sleep);
        end_time := now();

        diff := age(end_time, start_time);

        insert into tmp_interval_test (duration) values (diff);
    end;

    $body$
language 'plpgsql' volatile;

However, when I test this function, the duration shows

id|duration|
--|--------|
 1|00:00:00|

How do I correctly insert the duration into my table?

score 2 · Answer 1 · answered Nov 15 '19 at 03:36

The now() functions returns transaction time - it is same inside one transaction. So 0 is correct result. You should to use different functions, that returns real time - Use clock_timestamp() function instead.

On second hand, if you want to collect times of functions, you can use a buildin functionality in Postgres (if has superuser rights). Activate tracking functions. Then you can see what you need in system table pg_stat_user_function.

spitfiredd · Answer 2 · 2019-11-15T15:35:20.897

0

See SO regarding now()

Updated function and used clock_timestamp() instead of now(), e.g.,

start_time := clock_timestamp();

edited Nov 15 '19 at 15:35

answered Nov 15 '19 at 03:34

spitfiredd

2,897
5
32
75

1

current_timestamp cannot to help, it returns transaction time again - and there is syntax error - it is pseudoconstant. Postgres functions are running under transaction every time. – Pavel Stehule Nov 15 '19 at 03:38
1

You want `clock_timestamp()` – Nov 15 '19 at 06:35
yes I do sorry I meant to put `clock_timestamp()` sorry will fix this – spitfiredd Nov 15 '19 at 15:34

Postgres: timing the runtime of a function

2 Answers2