Idempotent writes & retries

Pattern 1 — the key is the PK, plain INSERT

The simplest handler: whoever gives you the event also gives you its id. Store it as the primary key and let the engine refuse the second delivery.


CREATE TABLE payments_received (
  event_id     VARCHAR       PRIMARY KEY,
  customer_id  VARCHAR       NOT NULL,
  amount       NUMERIC(12,2) NOT NULL,
  received_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

psql


INSERT INTO payments_received (event_id, customer_id, amount)
VALUES ('evt_9f1c', 'cust_42', 19.99);
-- INSERT 0 1
 
INSERT INTO payments_received (event_id, customer_id, amount)
VALUES ('evt_9f1c', 'cust_42', 19.99);
-- ERROR:  duplicate key value violates unique constraint   (SQLSTATE 23505)

Concurrent deliveries of the same event are safe too: when several connections INSERT the same key at once, exactly one succeeds and the rest get 23505. since 0.1.41

Pattern 2 — `ON CONFLICT DO NOTHING`

When you would rather not handle an exception, ask the engine to skip. The row count tells you whether this call was the one that wrote.


INSERT INTO payments_received (event_id, customer_id, amount)
VALUES ('evt_9f1c', 'cust_42', 19.99)
ON CONFLICT (event_id) DO NOTHING;
-- first caller:  INSERT 0 1
-- every other:   INSERT 0 0

Under a race the semantics match Postgres: the first inserter’s row survives, the first inserter reports 1, everyone else reports 0. since 0.1.43


cur = conn.execute(
    "INSERT INTO payments_received (event_id, customer_id, amount) "
    "VALUES (%s, %s, %s) ON CONFLICT (event_id) DO NOTHING",
    (event_id, customer_id, amount),
)
if cur.rowcount == 1:
    enqueue_downstream_work(event_id)   # only the winner does side effects

Still openInside an explicit BEGIN … COMMIT block the row count of a skipped ON CONFLICT DO NOTHING may still read 1. Use RETURNING event_id (which returns no row on a skip) when you need the signal inside a transaction. See Limits & differences.

Pattern 3 — `ON CONFLICT DO UPDATE` and `UPSERT INTO`

When a re-delivery may carry newer data, replace or merge instead of skipping. Assignments take arbitrary expressions.


-- Last write wins on the whole row
INSERT INTO customer_profile (customer_id, email, tier)
VALUES ('cust_42', '[email protected]', 'pro')
ON CONFLICT (customer_id) DO UPDATE
  SET email = EXCLUDED.email, tier = EXCLUDED.tier;
 
-- Counter: idempotent per (day, key) if the caller dedups, additive otherwise
INSERT INTO daily_counts (day, key, n)
VALUES (CURRENT_DATE, 'signup', 1)
ON CONFLICT (day, key) DO UPDATE SET n = daily_counts.n + 1;
 
-- Shorthand for replace-by-primary-key
UPSERT INTO customer_profile (customer_id, email, tier)
VALUES ('cust_42', '[email protected]', 'pro');
 
-- MySQL spelling is accepted with the same meaning
INSERT INTO customer_profile (customer_id, email, tier)
VALUES ('cust_42', '[email protected]', 'pro')
ON DUPLICATE KEY UPDATE email = VALUES(email), tier = VALUES(tier);

The conflict target must be the primary key or a CREATE UNIQUE INDEX column set. ON CONFLICT ON CONSTRAINT <name> is refused; name the columns.

SET n = daily_counts.n + 1 is not idempotent by itself. If the same event can be delivered twice, record the event id (pattern 1 or 2) in the same transaction and only bump the counter when the record INSERT wrote a row.

Pattern 4 — read generated values with `RETURNING`

SERIAL, BIGSERIAL and GENERATED … AS IDENTITY columns get a 64-bit generated id. There are no sequences, so lastval() and currval() do not exist. RETURNING is how you read the value, and it works on INSERT, UPDATE and DELETE, including inside an open transaction.


CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  VARCHAR NOT NULL,
  placed_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO orders (customer_id) VALUES ('cust_42')
RETURNING id, placed_at;


order_id, placed_at = conn.execute(
    "INSERT INTO orders (customer_id) VALUES (%s) RETURNING id, placed_at",
    ("cust_42",),
).fetchone()

A generated id is not an idempotency key: a retried handler would mint a new one. Carry a caller-supplied key in a UNIQUE column and use pattern 2 on it.

Pattern 5 — the retry loop

Two classes of failure are worth retrying, and they need different handling.

SQLSTATE	Meaning	Retry how
`40001`	Another transaction committed a write to a key this one read or wrote. Nothing was written.	Re-run the whole transaction, with a small backoff.
`08006`	Connection failure or a transient storage fault.	Reconnect and re-run. If the statement may have committed, make it idempotent first (patterns 1–3).
`57014`	`statement_timeout` expired. The statement was cancelled.	Caller’s call: retry, or raise the timeout.
`XX000`	Internal error.	Retry once; report if it persists.


import time
from psycopg import errors, OperationalError
 
RETRYABLE = (errors.SerializationFailure, OperationalError)
 
def run_with_retry(fn, attempts=5):
    for attempt in range(attempts):
        try:
            return fn()
        except RETRYABLE:
            if attempt == attempts - 1:
                raise
            time.sleep(0.05 * (2 ** attempt))

Wrap the transaction, not the individual statement: a 40001 is raised at COMMIT, and the fix is to redo the reads that led to the writes.

What not to retry

SQLSTATE	Why retrying is wrong
`23505`	The row already exists. That is the idempotency signal, not a failure.
`23502` · `23514` · `23P90`	Your data violates a constraint. It will violate it again.
`22001` · `22003` · `22P02` · `22012`	A value does not fit its type, or you divided by zero. Fix the value.
`42xxx`	The SQL is wrong: unknown column, unknown table, syntax. Fix the statement.
`0A000`	The feature is not supported. The error names the alternative.

The full code list, with example messages, is on Errors & SQLSTATEs.

Putting it together — a webhook handler


def handle_webhook(conn, evt):
    def txn():
        with conn.transaction():
            cur = conn.execute(
                "INSERT INTO payments_received (event_id, customer_id, amount) "
                "VALUES (%s, %s, %s) ON CONFLICT (event_id) DO NOTHING RETURNING event_id",
                (evt["id"], evt["customer"], evt["amount"]),
            )
            if cur.fetchone() is None:
                return "duplicate"           # someone already processed it
            conn.execute(
                "UPDATE customer_balance SET owed = owed - %s WHERE customer_id = %s",
                (evt["amount"], evt["customer"]),
            )
            return "applied"
    return run_with_retry(txn)

Everything inside conn.transaction() commits together or not at all. The RETURNING on the DO NOTHING insert is the duplicate signal that works inside a transaction, and the UPDATE is guarded on the balance row, so two concurrent webhooks for different events on the same customer serialize through 40001 and the retry rather than losing an update.

Idempotent writes & retries

Pattern 1 — the key is the PK, plain INSERT

psql

Python (psycopg)

Pattern 2 — ON CONFLICT DO NOTHING

Pattern 3 — ON CONFLICT DO UPDATE and UPSERT INTO

Pattern 4 — read generated values with RETURNING