Upsert in PostgreSQL 9.5

$row = $db->fetchOne('SELECT id FROM counters WHERE name = ?', array($name));
if ($row) {
    $db->execute('UPDATE counters SET value = value + 1 WHERE name = ?', array($name));
} else {
    $db->execute('INSERT INTO counters (name, value) VALUES (?, 1)', array($name));
}

Works on the laptop. In production two requests arrive in the same millisecond. Both SELECT, both see nothing, both INSERT. One dies with a duplicate key error. Or worse, there is no unique constraint, and now you have two rows and a bug report you cannot reproduce. The window between SELECT and INSERT is tiny, so it fires once a week, always for someone else.

PostgreSQL 9.5 is out, and the feature I waited for is INSERT ... ON CONFLICT. Real upsert, at last. One statement:

INSERT INTO counters (name, value)
VALUES ('emails_sent', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

The database resolves the race. That is its job, it has locks and it knows how to use them. There is also DO NOTHING, perfect for idempotent writes: insert the event, and if it is already there, fine, move on.

One detail I like. ON CONFLICT requires a unique index to conflict on. So the feature pushes you to declare the constraint. “Counter names are unique” is a business rule, and a unique constraint is the only place where that rule cannot be bypassed by a careless script or a second code path.

Before 9.5 we had advisory locks, retry loops around the duplicate error, and a wiki page explaining why. That code can go now. Deleting it is the best part of the upgrade. I wrote most of it. I am still going to enjoy the delete.