The Postgres job-claim query that silently claims the same job twice

A common way to claim a background job in PostgreSQL is a single UPDATE with a subquery that picks the oldest pending row. It is one statement, so it looks atomic. It is not. The most common version o