pg_dbms_lock
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_dbms_lock | 2.0 | SIM | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9250 | pg_dbms_lock | No | No | No | Yes | No | No | dbms_lock |
| Related | pg_background orafce session_variable pg_dbms_metadata pg_dbms_job oracle_fdw pgtt pg_statement_rollback mysql_fdw |
|---|
Requires pg_background >= 2.0; the PIGSTY DEB carries a downstream pg_background v2 API compatibility patch.
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 2.0 | 1817161514 | pg_dbms_lock | pg_background |
| RPM | PGDG | 2.0 | 1817161514 | pg_dbms_lock_$v | pg_background_$v |
| DEB | PIGSTY | 2.0 | 1817161514 | postgresql-$v-pg-dbms-lock | postgresql-$v-pg-background |
Build
You can build the DEB packages for pg_dbms_lock using pig build:
pig build pkg pg_dbms_lock # build DEB packages
Install
You can install pg_dbms_lock directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_dbms_lock; # Install for current active PG version
pig ext install -y pg_dbms_lock -v 18 # PG 18
pig ext install -y pg_dbms_lock -v 17 # PG 17
pig ext install -y pg_dbms_lock -v 16 # PG 16
pig ext install -y pg_dbms_lock -v 15 # PG 15
pig ext install -y pg_dbms_lock -v 14 # PG 14
dnf install -y pg_dbms_lock_18 # PG 18
dnf install -y pg_dbms_lock_17 # PG 17
dnf install -y pg_dbms_lock_16 # PG 16
dnf install -y pg_dbms_lock_15 # PG 15
dnf install -y pg_dbms_lock_14 # PG 14
apt install -y postgresql-18-pg-dbms-lock # PG 18
apt install -y postgresql-17-pg-dbms-lock # PG 17
apt install -y postgresql-16-pg-dbms-lock # PG 16
apt install -y postgresql-15-pg-dbms-lock # PG 15
apt install -y postgresql-14-pg-dbms-lock # PG 14
Create Extension:
CREATE EXTENSION pg_dbms_lock CASCADE; -- requires: pg_background
Usage
pg_dbms_lock: Extension to add Oracle DBMS_LOCK full compatibility to PostgreSQL
Uses PostgreSQL advisory locks to emulate Oracle DBMS_LOCK behavior.
Enabling
CREATE EXTENSION pg_dbms_lock;
ALLOCATE_UNIQUE
Allocate a unique lock identifier for a named lock:
DO $$
DECLARE
lockhandle varchar;
BEGIN
CALL dbms_lock.allocate_unique(
lockname => 'printer_lock',
lockhandle => lockhandle
);
RAISE NOTICE 'Handle: %', lockhandle;
END;
$$;
REQUEST
Request a lock with a specific mode (Exclusive=6, Shared=4):
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;
$$;
Return values: 0=Success, 1=Timeout, 3=Parameter error, 4=Already own lock, 5=Illegal handle.
RELEASE
Explicitly release a previously acquired lock:
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;
$$;
SLEEP
Suspend the session for a given duration:
CALL dbms_lock.sleep(0.70); -- sleep 0.7 seconds
Complete Example
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;
$$;
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.