sql - 在交易中设置时间戳

Question

我们的数据库 (PostgreSQL) 上有以下两个重复且独立运行的任务:

session 1 在事务中进行一些更新并设置更新数据集的时间戳:

BEGIN;
...
UPDATE table SET ..., timestamp = current_timestamp WHERE ...;
... // (A)
COMMIT;

session 2 选择自上次运行以来更新的所有数据集:

SELECT * FROM table WHERE timestamp BETWEEN last_run AND current_timestamp;
last_run = current_timestamp;
...

如果 session 2 在 session 1 处于 (A) 时开始，它将看不到更改，因为时间戳在提交之前不会被设置，而是设置为更早的值。此外，后续 session 2 不会选择更改，因为 last_run 已经大于时间戳。所以问题是 session 1 在错误的时间分别将时间戳设置为错误的值，因此可能会“忘记”更改。

一个可能的解决方法是在 session 1 中将更新的数据集 ID 存储在另一个表中，并在 session 2 中从该表中选择并删除它们。但也许有人有更好的主意......

Answer 1

This sort of question不时出现——据我所知，唯一完全可靠的方法是执行您所描述的操作，在第一个过程中将更新的 ID 存储在某个表中，并在第二个过程中将它们标记为已处理。基本上这是在数据库中重新发明一个消息队列。您已经很好地描述了天真的解决方案将如何错过更新。

让导入过程标记更新的行可以很容易地完成，甚至可以使用数据表上的触发器来实现。如果您只有一个消费者进程，那么它所要做的就是 delete from updated_item returning item_id 以获得更新列表。听起来好像要复杂得多，但恕我直言，事实并非如此。诸如能够免费监控积压量的功能也出现了。