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

From: Andrew Stuart <dukedougal(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How can I use Postgres "ROLLBACK TO SAVEPOINT" with "FOR UPDATE SKIP LOCKED"?
Date: 2018-08-26 00:03:07
Message-ID: CACXF7-gyqjQp7R8=58oP-t8fe_zt8QOfup52J09Qfu4+arRxPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Browse pgsql-general by date

  From Date Subject
Next Message Raghavendra Rao J S V 2018-08-26 03:36:06 Size of the table is growing abnormally in my database.
Previous Message TalGloz 2018-08-25 20:23:39 Re: Linker errors while creating a PostgreSQL C extension function.