postgresql 选择后 postgres 更新

postgres update after select

提问by tbh1

I want to do the following in one go:

我想一次性完成以下操作：

SELECT * FROM jobs WHERE status='PENDING';
UPDATE jobs SET status='RUNNING' WHERE status='PENDING';

So get all pending jobs, then set them as 'RUNNING' immediately after.

因此，获取所有待处理的作业，然后立即将它们设置为“正在运行”。

The reason I don't want to do it one after the other in two statements is that jobs could be added to the jobs table as 'PENDING' after the SELECT but before the UPDATE so I'd end up setting jobs as RUNNING even though I haven't grabbed it while it was in it's PENDING state.

我不想在两个语句中一个接一个地执行的原因是，可以在 SELECT 之后但在 UPDATE 之前将作业作为“PENDING”添加到作业表中，因此我最终将作业设置为 RUNNING当它处于 PENDING 状态时，我还没有抓住它。

Is there anyway to do this in one? So I want the result from SELECT and the UPDATE to happen on the fly.

有没有办法做到这一点？所以我希望 SELECT 和 UPDATE 的结果即时发生。

Thanks.

谢谢。

采纳答案by dcp

In general, you should do it with one UPDATE statement. The UPDATE will normally not be affected by rows that could have changed while the UPDATE statement is running, however, it's good to read up on transaction isolation levels here.

通常，您应该使用一个 UPDATE 语句来完成。UPDATE 通常不会受到在 UPDATE 语句运行时可能已更改的行的影响，但是，最好在此处阅读事务隔离级别。

Assuming you're using default setting of Read Committed, here is what it says:

假设您使用的是 Read Committed 的默认设置，它是这样说的：

Read Committed is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began;

Read Committed 是 PostgreSQL 中的默认隔离级别。当事务在此隔离级别上运行时，SELECT 查询只会看到查询开始之前提交的数据；

And in regards to UPDATE:

关于更新：

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row may have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation, starting from the updated version of the row. (In the case of SELECT FOR UPDATE and SELECT FOR SHARE, that means it is the updated version of the row that is locked and returned to the client.)

UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行方面的行为与 SELECT 相同：它们只会找到在命令开始时提交的目标行。然而，这样的目标行在它被发现时可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，潜在的更新者将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新程序回滚，那么它的效果就无效了，第二个更新程序可以继续更新最初找到的行。如果第一个更新程序提交，第二个更新程序将在第一个更新程序删除该行时忽略该行，否则它将尝试将其操作应用于该行的更新版本。重新评估命令的搜索条件（WHERE 子句）以查看行的更新版本是否仍与搜索条件匹配。如果是，则第二个更新程序从该行的更新版本开始继续其操作。（在 SELECT FOR UPDATE 和 SELECT FOR SHARE 的情况下，这意味着它是被锁定并返回给客户端的行的更新版本。）

So in your scenario, one UPDATE should be fine.

所以在你的场景中，一个 UPDATE 应该没问题。

Keep in mind too, that there is what's known as a SELECT FOR UPDATEstatement, which will lock the rows you select. You can read about that here. A scenario where you would need to use this feature would be in a reservation system. Consider this example:

还要记住，有一个所谓的SELECT FOR UPDATE语句，它将锁定您选择的行。你可以在这里阅读。您需要使用此功能的场景是在预订系统中。考虑这个例子：

1. Execute SELECTto find out if room XYZ is available for a reservation on date X.
2. The room is available. Execute UPDATEquery to book the room.

1. 执行SELECT以查看房间 XYZ 是否可用于在日期 X 预订。
2. 房间可用。执行UPDATE查询以预订房间。

Do you see the potential problem here? If between steps 1 and 2 the room gets booked by another transaction, then when wereach step 2 we are operating on an assumption which is no longer valid, namely, that the room is available.

你看到这里的潜在问题了吗？如果在第 1 步和第 2 步之间房间被另一笔交易预订，那么当我们到达第 2 步时，我们基于一个不再有效的假设进行操作，即房间可用。

However, if in step 1 we use the SELECT FOR UPDATE statement instead, we ensure that no other transaction can lock that row, so when we go to UPDATE the row, we know it's safe to do so.

然而，如果在步骤 1 中我们使用 SELECT FOR UPDATE 语句，我们确保没有其他事务可以锁定该行，因此当我们去更新该行时，我们知道这样做是安全的。

But again, in your scenario, this SELECT FOR UPDATE isn't needed, because you are doing everything in one statement and aren't checking anything ahead of time.

但同样，在您的场景中，不需要此 SELECT FOR UPDATE，因为您在一个语句中完成所有操作并且没有提前检查任何内容。

回答by a_horse_with_no_name

Why not use the RETURNING clause and process both things in one single statement:

为什么不使用 RETURNING 子句并在一个语句中处理这两件事：

UPDATE jobs 
    SET status='RUNNING' 
WHERE status='PENDING'
RETURNING *

That way you will get all rows that were changed by the UPDATE with a single atomic operation.

这样，您将通过单个原子操作获取由 UPDATE 更改的所有行。

回答by Clodoaldo Neto

begin;
select * 
from jobs 
where status='pending'
for update
;
update jobs 
set status='running' 
where status='pending';
commit;