pg_dbms_lock

pig build pkg pg_dbms_lock;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_dbms_lock;		# install via package name, for the active PG version

pig install pg_dbms_lock -v 18;   # install for PG 18
pig install pg_dbms_lock -v 17;   # install for PG 17
pig install pg_dbms_lock -v 16;   # install for PG 16
pig install pg_dbms_lock -v 15;   # install for PG 15
pig install pg_dbms_lock -v 14;   # install for PG 14

CREATE EXTENSION pg_dbms_lock CASCADE; -- requires pg_background

CREATE EXTENSION pg_dbms_lock;

DO $$
DECLARE
    lockhandle varchar;
BEGIN
    CALL dbms_lock.allocate_unique(
        lockname => 'printer_lock',
        lockhandle => lockhandle
    );
    RAISE NOTICE 'Handle: %', lockhandle;
END;
$$;

DO $$
DECLARE
    lock_res int;
BEGIN
    lock_res := dbms_lock.request(
        id => 123,
        lockmode => 6,           -- Exclusive
        timeout => 300,
        release_on_commit => false
    );
    IF lock_res <> 0 THEN
        RAISE EXCEPTION 'Lock request failed: %', lock_res;
    END IF;
END;
$$;

DO $$
DECLARE
    lock_res int;
BEGIN
    lock_res := dbms_lock.release(id => 123);
    IF lock_res <> 0 THEN
        RAISE EXCEPTION 'Release failed: %', lock_res;
    END IF;
END;
$$;

CALL dbms_lock.sleep(0.70);  -- sleep 0.7 seconds

DO $$
DECLARE
    lock_res int;
    printer_lockhandle varchar;
BEGIN
    CALL dbms_lock.allocate_unique('printer_lock', printer_lockhandle);
    lock_res := dbms_lock.request(lockhandle => printer_lockhandle, lockmode => 6, timeout => 5);
    IF lock_res <> 0 THEN
        RAISE EXCEPTION 'REQUEST failed: %', lock_res;
    END IF;
    -- do exclusive work here
    lock_res := dbms_lock.release(lockhandle => printer_lockhandle);
END;
$$;