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!
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. |