Postgres SELECT FOR UPDATE, actually update row when transaction is running

Question

I'm building an application which reads tasks from Posgtres table to perform calculation. My goal is reaching concurrency in performing such tasks.

I have a status model: 0 - initial 1 - in progress 2 - finished When task calculation transaction is started following transition is made: 0 -> 1. For that currently I'm using following query:

with next_task as (select id
                       from tasks
                       where status = 0
                       limit 1 for update skip locked)
            update tasks t
            set status = 1
            from next_task nt
            where t.id = nt.id
            returning t.id, t.type, t.bucket, t.params, t.message, t.attempts, t.status, t.created_at, t.updated_at

When querying for status while running related transaction it won't return the status field. In my case it is needed for monitoring task status reasons (I need to know which task is running currently).

Current behaviour when running query within transaction:

...|status|...
...|  0   |...

Desired behaviour:

...|status|...
...|  1   |...

What can I do to make it change status? My thought is to make two transactions - one for obtaining task and the other for performing calculation. But won't it go against transaction principle?

Answer 1

But won't it go against transaction principle?

Being able to monitor a transaction from outside that transaction also goes against transaction principle. If you need to do that, then you should do it in two separate PostgreSQL transactions. One to claim the tuple, one to finish and disclaim it.