On 2016-06-23 12:34, Terje Elde wrote:
> Typically the flow would be something like:
>
> BEGIN;
> SELECT id FROM targets WHERE status=‘scheduled’ FOR UPDATE SKIP LOCKED LIMIT 1;
> UPDATE targets SET status=‘in-flight’ WHERE id =%(id);
> COMMIT;
> — Do the work.
> BEGIN;
> UPDATE targets SET status=‘completed’ WHERE id = %(id); — or status=‘failed-foo’, if it fails for reason foo
> COMMIT;
>
>
> What I’m suggesting would be something along the lines of;
>
> BEGIN;
> SELECT id FROM targets WHERE status=‘scheduled’ FOR UPDATE SKIP LOCKED LIMIT 1;
> UPDATE targets SET status=‘failed-unknown’ WHERE id =%(id);
> SAVEPOINT deadletter ON FAILURE COMMIT;
> — Do the work.
> UPDATE targets SET status=‘completed’ WHERE id = %(id); — or status=‘failed-foo'
> COMMIT;
Comparing these two; how is the latter any better? It's the same number
of commands, except it's holding a transaction open for longer, it's
using a non-standard concept and it's arguably more complex.
.m