Implement advisory lock inside stored procedure-CodePudding

I have a stored procedure shown below which is running every 15 minutes trigged from postgres cron. Usually it happens that this procedure needs more time to finish. In such situation when next schedule is achieved and procedure is called again I would like procedure to be still called but wait till previous is finished. How can I achieve that without losing any new procedure call? I've heard that this can be achieved by using advisory lock nevertheless i am not sure how should I prepare my procedure to implement such feature. Can anyone show how exactly could i prepare my procedure?

My stored procedure:

CREATE OR REPLACE PROCEDURE public.test_procedure(p1 integer,
 INOUT p2 character varying)

 LANGUAGE plpgsql

AS $procedure$

DECLARE

  loop_var int;

BEGIN


  IF p1 is null OR p2 is null THEN

    RAISE EXCEPTION 'input cannot be null';

  END IF;

  DROP TABLE if exists test_table;

  CREATE TEMP TABLE test_table(a int, b varchar);

    FOR loop_var IN 1..p1 LOOP

        insert into test_table values (loop_var, p2);

        p2 := p2 || ' ' || p2;

    END LOOP;


END;

$procedure$

Procedure after modification:

CREATE OR REPLACE PROCEDURE public.test_procedure(p1 integer,
 INOUT p2 character varying)

 LANGUAGE plpgsql

AS $procedure$

DECLARE

  loop_var int;

BEGIN
  SELECT pg_advisory_lock(4711);
  IF p1 is null OR p2 is null THEN

    RAISE EXCEPTION 'input cannot be null';

  END IF;

  DROP TABLE if exists test_table;

  CREATE TEMP TABLE test_table(a int, b varchar);

    FOR loop_var IN 1..p1 LOOP

        insert into test_table values (loop_var, p2);

        p2 := p2 || ' ' || p2;

    END LOOP;
SELECT pg_advisory_unlock(4711);
END;

$procedure$

CodePudding user response：

Per the documentation:

At the beginning of the procedure, take an exclusive advisory lock:

SELECT pg_advisory_lock(4711);

Immediately before the end, release the lock:

SELECT pg_advisory_unlock(4711);

The number 4711 is a random number, all that counts is that you don't use the same advisory lock number anywhere else.

Then the second concurrent execution of the function will block when it tries to take the advisory lock and will only be allowed to continue when the lock is released.