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$
Leave an answer