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 lkhndl
and returns 1 if it fails to get it after timeout
seconds.
In postgresql I use
pg_advisory_xact_lock(lkhndl);
However, it seems that it waits for lock forever. pg_try_advisory_xact_lock
returns immediately if fails. Is there a way to implement timeout version of lock acquiring?
There is lock_timeout
setting, but I'm not sure is it applicable to advisory locks and how pg_advisory_xact_lock
would behave after timeout.
lock_timeout is a configuration parameter determining the period of time which should elapse before an attempt to acquire a lock is abandoned. lock_timeout was added in PostgreSQL 9.3.
The default connection timeout limit to PostgreSQL data sources is 30 seconds.
PostgreSQL advisory locks are application-level, cooperative locks explicitly locked and unlocked by the user's application code. An application can use PostgreSQL advisory locks to coordinate activity across multiple sessions.
Do this instead: -- select, update, insert, and delete block until the catalog is update (milliseconds) ALTER TABLE items ADD COLUMN last_update timestamptz; -- select and insert go through, some updates and deletes block while the table is rewritten UPDATE items SET last_update = now();
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 FUNCTION
advisory_xact_lock_request(p_key bigint, p_timeout numeric)
RETURNS integer
LANGUAGE 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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With