Thread: How can I use Postgres "ROLLBACK TO SAVEPOINT" with "FOR UPDATE SKIP LOCKED"?

Postgres 10

I am trying to use "FOR UPDATE SKIP LOCKED" to make a work queue in Postgres.

My goal is to be able to set status to 'complete' or 'failed' as the outcome by using "ROLLBACK TO SAVEPOINT;" in the event that processing fails.

I expected the code below  to result in a final status of "failed", but it appears the final status is "waiting".

    BEGIN;

          DROP TABLE IF EXISTS foo;

          SELECT id

          INTO foo

          FROM jobs

          WHERE status = 'waiting'

          AND status != 'failed'

          ORDER BY created ASC

          FOR UPDATE SKIP LOCKED

          LIMIT 1;

    

        UPDATE jobs SET status = 'failed' WHERE id = (SELECT id from foo) RETURNING *;

        SAVEPOINT blah;

        UPDATE jobs SET status = 'complete' WHERE id = (SELECT id from foo) RETURNING *;

    

    

    ROLLBACK TO SAVEPOINT blah;

Can anyone please suggest what I can do to use either COMMIT to for status to be 'complete' or ROLLBACK TO SAVEPOINT blah for statgus to be 'failed'?

thanks!