PostgreSQL Update and return

Let’s say I have a table called t in Postgres:

   id   | group_name | state
-----------------------------
   1    |   group1   |   0
   2    |   group1   |   0
   3    |   group1   |   0

I need to update the state of a row by ID, while also returning some things:

• The old state
• The remaining number of rows in the same group that have state = 0

I’ve got a query to do this as follows:

UPDATE t AS updated SET state = 1
FROM t as original
WHERE 
    updated.id = original.id AND
    updated.id = :some_id
RETURNING
    updated.state AS new_state,
    original.state AS old_state,
    (
        SELECT COUNT(*) FROM t 
        WHERE 
            group_name = updated.group_name AND
            state = 0
    ) as remaining_count;

However, it seems like the subquery within RETURNING is executed before the update has completed, leaving me with a remaining_count that is off by 1.

Additionally, I’m not sure how this behaves when concurrent queries are run. If we update two of these rows at the same time, is it possible that they would both return the same remaining_count?

Is there a more elegant solution to this? Perhaps some sort of window/aggregate function?

Answer

The subquery is indeed run without seeing the change from the UPDATE, because it’s running before the UPDATE has committed, and therefore it’s not visible. Nevertheless, it’s an easy fix; just add a where clause to filter out the ID you just updated in the subquery, making your query something like this:

UPDATE t AS updated SET state = 1
FROM t as original
WHERE 
    updated.id = original.id AND
    updated.id = :some_id
RETURNING
    updated.state AS new_state,
    original.state AS old_state,
    (
        SELECT COUNT(*) FROM t 
        WHERE 
            group_name = updated.group_name AND
            state = 0 AND
            t.id <> :some_id /* this is what I changed */
    ) as remaining_count;

Concurrency-wise, I’m not sure what the behavior would be, TBH; best I can do is point you at the relevant docs.