Example for advisory locks in postgresql

advisory_locks_example.sql

do $$
DECLARE
	ADV_LOCK BOOLEAN DEFAULT FALSE;
	LOCK_ID INTEGER DEFAULT 50;
BEGIN
	-- EARLY LOCK
	-- LOCK_ID := 'tablename'::regclass::integer;
	SELECT pg_try_advisory_lock(LOCK_ID) into ADV_LOCK;
	RAISE NOTICE 'LOCK_ID: %', LOCK_ID;
	IF ADV_LOCK THEN
		RAISE INFO 'Start function';
	ELSE
		RAISE INFO 'Another function running, exiting';
		RETURN;
	END IF;


	RAISE INFO 'BEGIN CODE BLOCK, start waiting';
	-- Don't forget to call `PERFORM pg_advisory_unlock(LOCK_ID);` before returning.
	PERFORM pg_sleep(5);
	RAISE INFO 'END CODE BLOCK, stop waiting';


	-- UNLOCK
	PERFORM pg_advisory_unlock(LOCK_ID);
	exception when QUERY_CANCELED then
		-- Cleanup lock
		PERFORM pg_advisory_unlock(LOCK_ID);
END$$;