Postgres concurrent updates on a cumulative sum query

user_151409 0
Viewed 244

The answer to this previous question demonstrates a method to select rows until a cumulative threshold is reached:

select t.*
from (
  select t.*, sum(amount) over (order by date desc) as running_amount from t
) t
where running_amount - amount < 12
order by date desc;

Now assume that what I want to do is perform an update on the result from that query. Maybe it looks something like this:

update t
set owner=$OWNER
from (
  select t.*, sum(amount) over (order by date desc) as running_amount
  from t
  where owner is null
) t
where t.running_amount - t.amount < 12
order by date desc;

Now assume that many such queries are run concurrently. I expect we may have a race condition because if the subquery runs at the same time it will return the same set of results in the same order to two or more outer queries, meaning the outer queries either tries to change the same rows, or the running amount is incorrect so it ends up selecting the wrong rows.

I'm not sure FOR UPDATE SKIP LOCKED helps here. If I use it in the above query I'll get a "FOR UPDATE is not allowed with window functions" error. But even if I could use it, using it in the subquery would end up locking every single row which is not what I want. Using it in the outer query I think is applying the lock in the wrong place.

So I thought I'd turn to greater brains than mine to see if you can help point me in the right direction?

  • Is my intuition correct in that the above query will result in race conditions when run concurrently?
  • Is there a better and safer approach to this problem?
concurrency postgresql sql
4 Answers

About your first question, you're right and it's really intense! and the answer to your second question is using "with" statement as described here.

Here is the new query:

(I change your table name from 't' to 'table1' and assume it has an 'ID' or any other unique column)

WITH dat AS (
    SELECT * 
    FROM
        ( SELECT *, SUM ( amount ) OVER ( ORDER BY DATE DESC ) AS running_amount 
          FROM table1 WHERE OWNER IS NULL ) A 
    WHERE
        A.running_amount - A.amount < 12 
    ) 
UPDATE table1 
    SET OWNER = $OWNER 
WHERE
    ID IN ( SELECT ID FROM dat )
user_39362090

This is not an answer to your question but a question on your question, and which is too long for a simple comment :

I don't really understand the objective and logic of your UPDATE statement :

Once you have udpated the owner column of some rows which conform the condition owner is null and the condition sum(amount) over (order by date desc) - amount < 12 then these rows don't conform the first condition owner is null anymore, so that if you look at the rest of the rows whose owner is still null, then you can see that some of these rows do now conform the condition sum(amount) over (order by date desc) - amount < 12 so that they will be updated if you execute the same UPDATE statement again. And so on ... If you execute n times your UPDATE statement until no more row conform both conditions, then you just see that all the rows in the tables have been updated.

So why don't you simply update all the rows at once ?

See the test result for an incremental UPDATE in dbfiddle_1

and for a full iterative UPDATE in dbfiddle_2.

user_80600170

To me - I would start some steps before place where we are right now. Looks like you are looking for particular solution, but I'm not sure if the problem is well defined.

If load is a problem. Maybe you can have something like a trigger to prepare data for that updates? If date is there, so maybe things can be prepared? Or maybe you know when updates for particular date happens? Maybe anything here can limit load...

Getting to your question. I would change order by date desc to order by id - I can't see reason why you need that final order by date... With order by id you can deal with dead locks - since locking order will be done in the same order among all concurrent queries. When order by date is used it can happen that rows are in different order, PG locks rows one by one - so dead lock is just a matter of time. If you run many, many that queries concurrently - maybe it is possible to update only some rows?

user_61030010

Updating and selecting data are different queries, don't mix these in one SQL statement. First do the update query, then the select query.

The order is not important in the update query. Also t. is not needed, since "update t" already implies you use that table.

Result should be this query:

update t
set owner=$OWNER
where owner is null
and running_amount - amount < 12;

After the update query run the select query select * from t ...

user_14305520
Related