Timeout On Advisory Locks In Postgresql

I'm migrating from ORACLE. Currently I'm trying to port this call: lkstat := DBMS_LOCK.REQUEST(lkhndl, DBMS_LOCK.X_MODE, lktimeout, true); This function tries to acquire lock lkhn

Solution 1:

This is a prototype of a wrapper that poorly emulates DBMS_LOCK.REQUEST - constrained to only one type of lock (transaction-scope advisory lock).

To make function fully compatible with Oracle's, it would need several hundreds lines. But that's a start.

CREATEOR REPLACE FUNCTION
advisory_xact_lock_request(p_key bigint, p_timeout numeric)
RETURNSintegerLANGUAGE plpgsql AS $$
/*  Imitate DBMS_LOCK.REQUEST for PostgreSQL advisory lock. 
Return 0 on Success, 1 on Timeout, 3 on Parameter Error. */DECLARE
    t0 timestamptz := clock_timestamp();
BEGIN
    IF p_timeout NOTBETWEEN0AND86400THEN
        RAISE WARNING 'Invalid timeout parameter';
        RETURN3;
    END IF;
    LOOP
        IF pg_try_advisory_xact_lock(key) THENRETURN0;
        ELSIF clock_timestamp() > t0 + (p_timeout||' seconds')::intervalTHEN
            RAISE WARNING 'Could not acquire lock in % seconds', p_timeout;
            RETURN1;
        ELSE
            PERFORM pg_sleep(0.01); /* 10 ms */END IF;
    END LOOP;
END;
$$;

Test it using this code:

SELECTCASEWHEN advisory_xact_lock_request(1, 2.5) =0THEN pg_sleep(120)
END; -- and repeat this in parallel session /* Usage in Pl/PgSQL */

lkstat := advisory_xact_lock_request(lkhndl, lktimeout);