How to identify whether a function is executing or not in postgresql

Question

Suppose

CREATE OR REPLACE FUNCTION report.dummy_func1() RETURNS integer AS $$
DECLARE
BEGIN
    PERFORM pg_sleep(10);
    RETURN 1;
END;
$$ LANGUAGE plpgsql;   

CREATE OR REPLACE FUNCTION report.dummy_func() RETURNS integer AS $$
DECLARE
BEGIN
    PERFORM pg_sleep(5);
    PERFORM report.dummy_func1();
    RETURN 1;
END;
$$ LANGUAGE plpgsql;   

Select report.dummy_func();

With the above setup, is there a way to identify which function is currently being executed?

One way to identify is using pg_stat_activity, but it doesn't show which function is being executed.

Is there a reliable way to find whether a function is executing or not in postgresql.

My original requirement is to invoke a function, only if it is not already running. Is there a better way to achieve this in postgresql?

Craig Ringer · Accepted Answer · 2013-08-20 12:04:21Z

2

My original requirement is to invoke a function, only if it is not already running. Is there a better way to achieve this in postgresql?

It sounds like you should be using an advisory lock:

http://www.postgresql.org/docs/current/static/explicit-locking.html

Basically, something like:

if not pg_try_advisory_lock(_key) then return -1; end if;
-- do stuff...
perform pg_advisory_unlock(_key);

edited Aug 20, 2013 at 12:04

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

answered Aug 20, 2013 at 9:57

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to identify whether a function is executing or not in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related