DEV Community

晖莫
晖莫

Posted on

The Unique Index That Makes Your Endpoint Retry-Safe

#idempotency #sql #testing #postgres

The payment form double-submitted on a flaky connection. Two POSTs hit /payments with the same idempotency key. One row landed in the table. The client got 200 OK both times. I asked why the second insert did not fail, and got three different answers from three engineers.

The real answer was in a migration from before any of us joined.

CREATE UNIQUE INDEX payments_idempotency_key_idx
    ON payments (idempotency_key);
Enter fullscreen mode Exit fullscreen mode

That index, and nothing in the Python, is what made the endpoint safe to retry.

The constraint is the retry policy

The handler looked like this.

def create_payment(req):
    try:
        cur.execute(
            """INSERT INTO payments (idempotency_key, account_id, amount_cents)
               VALUES (%s, %s, %s)
               ON CONFLICT DO NOTHING""",
            (req.key, req.account_id, req.amount_cents),
        )
        db.commit()
    except Exception:
        db.rollback()
    return {"status": "accepted"}
Enter fullscreen mode Exit fullscreen mode

Note the bare ON CONFLICT DO NOTHING. It names no target and no index. That clause compiles whether or not a unique constraint exists. When one exists, the duplicate is absorbed. When none exists, the same code inserts a second row and returns success. The endpoint's retry safety lives entirely in the schema, not in the code.

The except Exception makes it worse. It catches the genuine constraint violation just as quietly as it catches a dropped connection, so an absorbed duplicate and a rolled-back write produce the same response.

Why "it happens to work" is the problem

Nothing in that function announces an idempotency contract. The index looks like ordinary data hygiene, the kind someone adds during a cleanup ticket. So it gets treated as removable.

Two changes break it. Someone drops the index to cut write amplification on a hot table, and the bare ON CONFLICT DO NOTHING starts silently inserting duplicates. Or someone rewrites the branch to DO UPDATE so the response can carry the stored row, and a retry now overwrites an amount that already settled.

Neither change fails a test, because the test sends one request and asserts one row. That assertion passes with the index, without it, and with DO UPDATE. The suite was measuring the wrong thing the whole time.

Absorbed is not the same as correct

An absorbed duplicate means the database kept one row. It says nothing about what the client received. If the handler builds a fresh UUID per call, the retry gets a different resource id for the same charge, and any client that reconciles by id now holds two. If the response is just {"status": "accepted"}, the client learns nothing about which write won.

A correct replay returns the same answer twice: same status, same body, same id. That is the property clients actually depend on, and it is the property you should assert.

def test_retry_returns_same_body(client, db):
    body = {"idempotency_key": "k-1", "account_id": 7, "amount_cents": 500}
    first = client.post("/payments", json=body)
    second = client.post("/payments", json=body)
    assert first.status_code == second.status_code == 200
    assert first.json() == second.json()
    assert db.scalar(
        "SELECT count(*) FROM payments WHERE idempotency_key = 'k-1'"
    ) == 1
Enter fullscreen mode Exit fullscreen mode

Run that against the handler above and the row assertion passes while the body assertion fails if you generate the id per call. That failure is the point. It tells you the guarantee was only ever half there.

Finding the accidental ones

Grep the migrations for UNIQUE and CREATE UNIQUE INDEX. Grep the handlers for ON CONFLICT and for IntegrityError. Every place that catches a unique violation is declaring a retry contract it never wrote down.

Then ask one question per handler: does any test send the same request twice? If not, the guarantee is unverified and undefended. Write the two-request test first. If it passes, you have pinned the behaviour to the schema and to a test at the same time. If it fails, you just found a latent double-write before a customer did.

Name the constraint in a comment next to the conflict clause. Not for documentation's sake, but so the next person weighing write throughput against dropping that index can see what it is holding up. A unique index that is load-bearing should read like one.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Top comments (0)