[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    How can I use Postgres "ROLLBACK TO SAVEPOINT" with "FOR UPDATE SKIP LOCKED"?
From:       Andrew Stuart <dukedougal () gmail ! com>
Date:       2018-08-26 0:03:07
Message-ID: CACXF7-gyqjQp7R8=58oP-t8fe_zt8QOfup52J09Qfu4+arRxPA () mail ! gmail ! com
[Download RAW message or body]

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!

[Attachment #3 (text/html)]

<div dir="ltr"><div>Postgres 10</div><div><br></div><div>I am trying to use &quot;FOR \
UPDATE SKIP LOCKED&quot; to make a work queue in \
Postgres.</div><div><br></div><div>My goal is to be able to set status to \
&#39;complete&#39; or &#39;failed&#39; as the outcome by using &quot;ROLLBACK TO \
SAVEPOINT;&quot; in the event that processing fails.</div><div><br></div><div>I \
expected the code below   to result in a final status of &quot;failed&quot;, but it \
appears the final status is &quot;waiting&quot;.</div><div><br></div><div>      \
BEGIN;</div><div>               DROP TABLE IF EXISTS foo;</div><div>               \
SELECT id</div><div>               INTO foo</div><div>               FROM \
jobs</div><div>               WHERE status = &#39;waiting&#39;</div><div>             \
AND status != &#39;failed&#39;</div><div>               ORDER BY created \
ASC</div><div>               FOR UPDATE SKIP LOCKED</div><div>               LIMIT \
1;</div><div>       </div><div>            UPDATE jobs SET status = &#39;failed&#39; \
WHERE id = (SELECT id from foo) RETURNING *;</div><div>            SAVEPOINT \
blah;</div><div>            UPDATE jobs SET status = &#39;complete&#39; WHERE id = \
(SELECT id from foo) RETURNING *;</div><div>       </div><div>       </div><div>      \
ROLLBACK TO SAVEPOINT blah;</div><div><br></div><div><br></div><div>Can anyone please \
suggest what I can do to use either COMMIT to for status to be &#39;complete&#39; or \
ROLLBACK TO SAVEPOINT blah for statgus to be \
&#39;failed&#39;?</div><div><br></div><div>thanks!</div></div>



[prev in list] [next in list] [prev in thread] [next in thread]