Easy task queues using PostgreSQL

taskqueues.sql

-- Let's say you have a table full of work:

CREATE TABLE tasks (
       id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
       status TEXT NOT NULL DEFAULT 'pending',
       payload JSON NOT NULL, -- or just have meaningful columns!
       created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE tasks ADD CHECK (status IN ('pending', 'done', 'error'));

INSERT INTO tasks (payload) VALUES ('{ "action": "feed", "animal": "panda", "food": "kaiserschmarrn" }');

-- To find pending work, by polling, you query the table like this, in
-- a transaction:

BEGIN;
SELECT * FROM tasks WHERE status = 'pending' ORDER BY created_at ASC LIMIT 1 FOR UPDATE SKIP LOCKED;

-- ^ This query requires PostgreSQL 9.5+

-- Now you have the info in that row, and nobody else will see that
-- row until the transaction completes, so you do the work, and then:

UPDATE tasks SET status = 'done' WHERE id = the_task_id;
COMMIT;

-- The important thing is to decide the error handling strategy.

-- If an error happens, the transaction will generally be rolled back,
-- which means somebody else (you again?) will pick up the same
-- job. That can be problematic, so it's often best to do the work
-- using a separate connection and transaction. Then if that work
-- explodes, you can catch the error and instead execute:

UPDATE tasks SET status = 'error'  WHERE id = the_task_id;

-- (and optionally save the failure info in another table or an evil
-- nullable column)

-- Now, I mentioned this was for polling. You can reduce/eliminate
-- polling by using a long-running connection and using LISTEN/NOTIFY
-- (https://www.postgresql.org/docs/9.0/sql-notify.html) on a
-- channel. The code responsible for creating tasks would call
-- pg_notify after inserting a new task. The worker code would use
-- LISTEN, and then it would perform the above query to obtain and
-- lock a task.

An example on how to handle errors using SAVEPOINTS, so that we can use the same transaction that picked the task. Although it seems a good strategy (better than opening a new transaction) I'm not sure about its limitations and tradeoffs.

SAVEPOINT = sub-transaction and nested transaction

Postgres and issues with sub/nested-transactions (aka SAVEPOINTs)