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

Поиск
Список
Период
Сортировка
От Andrew Stuart
Тема How can I use Postgres "ROLLBACK TO SAVEPOINT" with "FOR UPDATE SKIP LOCKED"?
Дата
Msg-id CACXF7-gyqjQp7R8=58oP-t8fe_zt8QOfup52J09Qfu4+arRxPA@mail.gmail.com
обсуждение исходный текст
Список 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!

В списке pgsql-general по дате отправления:

Предыдущее
От: TalGloz
Дата:
Сообщение: Re: Linker errors while creating a PostgreSQL C extension function.
Следующее
От: Raghavendra Rao J S V
Дата:
Сообщение: Size of the table is growing abnormally in my database.