Contents
This is sample teaching content. Publication dates and transaction records are illustrative.
A timeout does not tell us whether a write committed. Retrying an append can therefore duplicate data. An idempotent operation produces the same destination state when the same input is applied again. This sample uses PostgreSQL 18 to make that property concrete.
Define identity and revision
The source supplies one current amount per transaction and an increasing source_revision. A larger revision replaces a smaller one. Equal revisions must carry the same payload; conflicting payloads at the same revision are a source error to quarantine. Amounts use one currency.
CREATE TABLE transaction_state (
transaction_id text PRIMARY KEY,
source_revision bigint NOT NULL,
amount numeric(12, 2) NOT NULL
);
INSERT INTO transaction_state AS current_state
(transaction_id, source_revision, amount)
VALUES ('T-10', 2, 130.00)
ON CONFLICT (transaction_id) DO UPDATE
SET source_revision = EXCLUDED.source_revision,
amount = EXCLUDED.amount
WHERE EXCLUDED.source_revision > current_state.source_revision;
The primary key establishes identity. ON CONFLICT uses that key to select an update instead of a duplicate insert. The condition prevents an older revision replacing a newer value. Replaying revision 2 leaves the row unchanged. This condition also ignores equal-revision conflicts, so validate that contract separately before writing.
Test failures at the boundary
| Input after revision 2 | Expected destination |
|---|---|
| Same revision 2, amount 130.00 | One row, amount 130.00 |
| Older revision 1, amount 125.00 | One row, amount 130.00 |
| New revision 3, amount 135.00 | One row, amount 135.00 |
| Revision 2 with conflicting payload | Quarantine before write |
Send each scenario twice. Compare the complete row and row count. If a batch contains several versions of one key, select a deterministic latest revision before one upsert statement. PostgreSQL does not allow one statement to update the same destination row twice through ON CONFLICT.
Separate state from side effects
A safe database upsert does not make an email or external API call idempotent. Use a destination-supported idempotency key or a transactional outbox with a deduplicating receiver. Record the input batch and completion checkpoint durably. Advance the checkpoint only after the intended effects are safe.
Add retries with bounded delays for transient failures, route invalid records to investigation, and reconcile accepted plus rejected input counts. Test a crash immediately after commit but before acknowledgement. If recovery duplicates effects or loses a correction, the retry contract is incomplete.