SKIP LOCKED job queue and a worker that dies mid-transaction

Depends on how it died. Two cases, and from the queue they look nothing alike.

Killed, connection closed. The server process was waiting for the next command, sees the connection go, and rolls back the open transaction. The row lock goes with it. status is still 'queued', and the next poll from any worker takes the row. One job, two runs.

Hung, connection still up. The session sits idle in transaction, the row stays locked, and every other worker's SKIP LOCKED walks around it on every poll for as long as the session lives. That's one job parked and invisible, and with N hung workers it's N jobs. idle_in_transaction_session_timeout exists for this and is off by default.

So count on a job running more than once. Its external effect has to survive the repeat.

Right about kill -9. Connection drops, rollback, the Go worker takes the row on its next poll, job runs twice.

kill -STOP is the ugly one. Row stays locked, session sits in pg_stat_activity as idle in transaction. With idle_in_transaction_session_timeout set, the server ends the session and the Go worker takes the row and runs the job. Then kill -CONT. The stopped worker wakes up, finishes its work, sends its UPDATE, and only then gets the error that the connection is gone. By then the external effect has happened twice.

Timeout stays, since it frees the row. It doesn't stop the old worker though, so each job gets an idempotency key next.

The poll query will slow down as finished jobs pile up in the table, and its text won't tell you why. With an index on id only, the typical plan walks it for ORDER BY id LIMIT 1 and throws away rows with status = 'done' one by one, and the longer the history, the longer that walk. A partial index WHERE status = 'queued' shortens it, as long as the query uses the same literal.

That leaves a question I don't have a good answer to. Do you delete finished rows in the same transaction, move them to an archive table, or clean them up in batches later?

On the Go side, give the worker's transaction a deadline. When the BeginTx context expires, database/sql rolls the transaction back by itself, even while run is still stuck.

ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
defer cancel()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback()
// poll, run(ctx, payload), mark done
return tx.Commit()

Doesn't help with kill -STOP. A stopped process runs nothing, deadlines included.

That's the case that worries me most. The old worker can't know its session is gone until it talks to the database again, and by then the work is done. Let the external write carry the job id, and let the receiving side refuse a second one.

Delete it. Dequeue by deleting the row and the history problem never starts.

DELETE FROM jobs
WHERE id = (
  SELECT id FROM jobs
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

Run the work inside the same transaction and commit at the end. The table only ever holds pending jobs, so no partial index and no archive job. Yes, you lose the record of what ran. Write that to a log table if anyone asks for it, and keep it out of the poll path.