How can I select a row, then UPDATE it if it's column value =x, while also using the row with an INNER JOIN?

Question

In this scenario I have 2 tables: a task_pool table and an orders table.

task_pool

task_id (key)	order_id(int)	status(int)	expire_time(timestamp)
20	1	0	[timestamp]
25	2	1	[timestamp]

orders

order_id (key)	order_info(varchar)	order_datetime(timestamp)
0	"info"	[timestamp]
1	"info"	[timestamp]

Right now I retreive order_info and order_datetime from the orders table using the related order_id from task table, using this query.

WITH t AS (
        SELECT 
            task_id, 
            order_id,
            status
        FROM task_pool
        WHERE 
            task_id = $1 
            AND 
            (status = 0 OR status = 1)
    )
    SELECT  t.order_id, t.status,
            order_info, order_datetime
    FROM orders
    INNER JOIN t ON orders.order_id=t.order_id`,
[req.body.task_id]

This works (the most efficient way, I don't know). It will only retrieve 1 row since task_id is unique index key.

What I am trying to do, is check IF t.status=0, and if so UPDATE expire_time to expire_time + 10 minutes, and status to 1.

I know I COULD check the results.rows[0] back in Node.js, and if status==0, make another pg query to update that row's expire_time and status.

However, I would prefer to do it all in the original query, unless it unadvised.

Something like:

WITH t AS (
            SELECT 
                task_id, 
                order_id,
                status
            FROM task_pool
            WHERE 
                task_id = $1 
                AND 
                (status = 0 OR status = 1)
            )
**IF t.status = 0 THEN
    UPDATE 
        task_pool 
    SET 
        status = 1, expire_time = expire_time + (10 * interval '1 minute')
    WHERE
        task_id = $1
END IF;**

SELECT  t.order_id, t.status,
        order_info, order_datetime
FROM orders
INNER JOIN t ON orders.order_id=t.order_id`,
    [req.body.task_id]

Is this possible?

Answer 1

You can trade your select for an update..returning that targets the same row, but updates it based on your conditions, then returns the new values:

WITH t AS (
        UPDATE task_pool
        SET status=case when status=0 
                        then 1 
                        else status 
                   end, 
            expire_time=case when status=0 
                             then expire_time+'10 min'::interval 
                             else expire_time 
                        end
        WHERE 
            task_id = $1 
            AND 
            (status = 0 OR status = 1)
        RETURNING 
            task_id, 
            order_id,
            status
    )
    SELECT  t.order_id, t.status,
            order_info, order_datetime
    FROM orders
    INNER JOIN t ON orders.order_id=t.order_id`,
[req.body.task_id]

You might want to decrease the table fillfactor to let it fit the new values on the same pages - even when the update is unnecessary, the row still gets updated with the same values (the elses).

---

You could also split this into two CTEs, one select, another with update..returning, then union those. That way no update is performed if it isn't necessary.

WITH t1 AS (
        UPDATE task_pool
        SET status=1, 
            expire_time=expire_time+'10 min'::interval 
        WHERE 
            task_id = $1 
            AND 
            status = 0
        RETURNING 
            task_id, 
            order_id,
            status
    )
,t2 AS (
        SELECT 
            task_id, 
            order_id,
            status
        FROM task_pool
        WHERE 
            task_id = $1 
            AND 
            status = 1
    )
,t AS (table t1 union table t2)
    SELECT  t.order_id, t.status,
            order_info, order_datetime
    FROM orders
    INNER JOIN t ON orders.order_id=t.order_id`,
[req.body.task_id]

If your target row didn't meet the update criteria, that update is returning nothing, and the only values coming out of the union are those from the select which applied the opposite logic.