pg_once

pg_once is a lightweight PostgreSQL extension that adds run‑once / idempotency‑key semantics with a default 24‑hour TTL and optional JSON response caching.

Quick install (dbdev client)

select dbdev.install('pg_once');
create extension pg_once version '0.1.0';

API

Function	Purpose
pg_once_start(key text, resource text, ttl interval default '24 hours') → jsonb	Reserve or check an idempotency key.
pg_once_commit(key text, response jsonb)	Mark the key as completed and cache a response.
pg_once_fail(key text)	Mark the key as failed.
pg_once_cleanup() → integer	Purge expired keys (TTL).

Example

-- Reserve key
select pg_once_start('order#42', 'orders') \gset

\if :pg_once_start_status = 'accepted'
  -- do work here ...
  select pg_once_commit('order#42', '{"ok":true}'::jsonb);
\endif

License

MIT