pg_retry

pig build pkg pg_retry         # build RPM / DEB packages

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

CREATE EXTENSION pg_retry;

retry.retry(
  sql TEXT,                          -- SQL statement to run (exactly one)
  max_tries INT DEFAULT 3,           -- total attempts (1 + retries), >= 1
  base_delay_ms INT DEFAULT 50,      -- initial backoff delay in ms
  max_delay_ms INT DEFAULT 1000,     -- cap for exponential backoff
  retry_sqlstates TEXT[] DEFAULT ARRAY['40001','40P01','55P03','57014']
) RETURNS INT                       -- number of rows processed

SELECT retry.retry('UPDATE accounts SET balance = balance - 100 WHERE id = 1');

SELECT retry.retry(
    'INSERT INTO audit_log (event) VALUES (''test'')',
    5,      -- max_tries
    100,    -- base_delay_ms
    5000    -- max_delay_ms
);

ALTER SYSTEM SET pg_retry.default_max_tries = 5;
ALTER SYSTEM SET pg_retry.default_base_delay_ms = 100;
ALTER SYSTEM SET pg_retry.default_max_delay_ms = 5000;
ALTER SYSTEM SET pg_retry.default_sqlstates = '40001,40P01,55P03,57014';
SELECT pg_reload_conf();