Timeout on advisory locks in postgresql

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.

CREATE OR REPLACE FUNCTIONadvisory_xact_lock_request(p_key bigint, p_timeout numeric)RETURNS integerLANGUAGE 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 NOT BETWEEN 0 AND 86400 THEN        RAISE WARNING 'Invalid timeout parameter';        RETURN 3;    END IF;    LOOP        IF pg_try_advisory_xact_lock(key) THEN            RETURN 0;        ELSIF clock_timestamp() > t0 + (p_timeout||' seconds')::interval THEN            RAISE WARNING 'Could not acquire lock in % seconds', p_timeout;            RETURN 1;        ELSE            PERFORM pg_sleep(0.01); /* 10 ms */        END IF;    END LOOP;END;$$;

Test it using this code:

SELECT CASE     WHEN advisory_xact_lock_request(1, 2.5) = 0    THEN pg_sleep(120)END; -- and repeat this in parallel session /* Usage in Pl/PgSQL */lkstat := advisory_xact_lock_request(lkhndl, lktimeout);