pg_lease

pg_lease : SQL leases with TTL expiry and monotonically increasing fencing epochs

Overview

Packages

Source

pig build pkg pg_lease;		# build rpm

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

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

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

Create this extension with:

CREATE EXTENSION pg_lease;

Usage

Sources:

• PGXN 0.1.0
• Official README
• Extension SQL
• Control file

pg_lease provides expiring ownership of named resources with increasing fencing epochs. It is a pure SQL/PL/pgSQL extension with lazy expiry, no background worker, and no preload requirement. Version 0.1.0 is marked testing on PGXN; upstream requires PostgreSQL 14 or later and documents its compatibility tests on PostgreSQL 14 and 15.

Core Workflow

The extension is trusted: a role with CREATE privilege on the database can install it. It creates the fixed lease schema and is not relocatable.

CREATE EXTENSION pg_lease;

SELECT acquired, epoch, expires_at
FROM lease.acquire('job-42', 'worker-a', interval '30 seconds');

SELECT status, expires_at
FROM lease.renew('job-42', 'worker-a', 1, interval '30 seconds');

SELECT status FROM lease.release('job-42', 'worker-a', 1);
SELECT held, owner, epoch, expires_at FROM lease.inspect('job-42');

The example uses the first epoch, 1. Applications must pass the epoch actually returned by acquisition to later renewal, release, and external resource guards. A successful release advances the epoch; re-acquisition must use the newly returned value.

Operations

Renewal and release return ok, not_owner, lapsed, or epoch_mismatch. Invalid inputs raise SQLSTATE 22023; an exhausted blocking wait raises 57014. Re-acquiring an unexpired lease with the same owner is idempotent and does not extend its expiry.

Transactions and Boundaries

Use READ COMMITTED for blocking acquisition. State changes follow the caller’s transaction and are undone by rollback. Expiry follows the database server’s clock; session termination does not release a lease immediately. The functions use SECURITY DEFINER with a fixed search path; direct public access to lease.leases is revoked.

Owner strings are application-supplied identifiers, not authenticated database identities. Applications must control access to the lease API and enforce fencing epochs at the resource being protected. The extension provides no safety guarantee across primary promotion or independent databases. Dropping the extension destroys lease state and resets the epoch history on reinstallation; version 0.1.0 does not register its lease table for extension configuration dumps.