sql - 跟踪追加到PostgreSQL中的表

Question

考虑下这个表：

create table entries (
  sequence_number integer
    default nextval('entries_sequence_number_seq')
    primary key,
  time timestamp default now()
);

此表用作仅追加的更改流。写入操作可能涉及其他表，但作为每个事务中的最后一条SQL语句，我们将向该表插入一行。换句话说，我们的事务可能很大而且耗时，但最终我们会编写这一行并立即提交。
现在，我们希望一个或多个使用者可以跟踪附加到此表中的更改：
每个使用者都需要定期循环，以大致按时间顺序获取下一批更改，也就是说，自上次使用者轮询以来附加到条目的新行增量。
消费者总是与时俱进，从不倒退。
每个消费者都得到所有的数据。不需要有选择性的分配。
消费顺序并不重要。但是，使用者最终必须看到所有提交的条目：如果一个正在运行的事务向表提交了一个新条目，则必须将其提取。
我们想尽量减少两次看到同一排的可能性，但如果发生这种情况，我们可以容忍。
概念上：

select * from entries where sequence_number > :high_watermark

…其中高_水印是消费者看到的最高数字。
但是，由于 nextval()是在提交时间之前计算的，因此您可能会遇到由于尚未提交的飞行事务而导致的间隔的情况。你可能有这样的种族状况：
假设世界从序号0开始。
Writer A txn:Inserts，获取序列号1。
Writer B txn:Inserts，获取序列号2。
写入程序B txn提交。
最新的序列号现在是2。
消费者在>0时进行选择，找到序号为2的条目，将其设置为高水印。
编写器A txn提交。
消费者选择了>2，因此永远看不到序号为1的条目。
在一般情况下，竞争条件可能非常小，但它仍然是一种可能性，并且它发生的概率随着系统负载的增加而增加。
到目前为止，想到的最好但肯定不是优雅的解决方案是始终随时间选择：

select * from entries
where sequence_number > :newest_sequence_number
or time >= :newest_timestamp

从理论上讲，这应该是——模闰秒和漂移时钟——以获取在最后一批中出现的行为代价，保证可以看到较旧的条目。消费者应该希望保留一小部分可以忽略的已经看到的条目。闰秒和漂移时钟可以通过在时间戳中填充一些不科学的秒数来解释。缺点是它会不断地读取一堆多余的行。只是感觉有点笨重，手有点波浪。
一个稍显迟钝但更具确定性的方法是维护一个未标记的未决事件表，并在我们从中读取时始终从中删除。这有两个缺点：一是性能，显然。另一种情况是，由于可能有任何数量的消费者，我们将不得不产生一个事件，每个消费者，这反过来意味着我们必须识别消费者的一些独特的ID在事件发射时间，当然，垃圾收集未使用的事件，当消费者不再存在。
让我吃惊的是，与未标记的表相比，更好的方法是使用条目ID作为有效负载的 LISTEN/ NOTIFY。这样做的好处是，首先避免了轮询，尽管这不是一个巨大的胜利，因为这个应用程序中的消费者的目标是时不时地醒来，减少对系统的工作。另一方面，我能看到的唯一一个主要缺点是，可以传输的消息数量有一个限制（尽管是一个很大的限制），如果通知不能发生，事务将开始失败。不过，这可能是一个合理的妥协。
同时，我脑海中的一些东西告诉我，必须有一种数学上更优雅的方法，用更少的工作来完成这件事。

Answer 1

您使用WHERE time >= :newest_timestamp改进的想法受到相同的竞争条件的约束，因为不能保证时间戳是按提交顺序排列的。进程偶尔会进入睡眠状态。
为每个初始化为boolean的消费者添加consumed_n字段FALSE。消费者n随后使用：

    UPDATE entries
       SET consumed_n = TRUE
       WHERE NOT consumed_n
       RETURNING sequence_number, time;

有助于部分索引 ON entries(1) WHERE NOT consumed_n。
如果这占用了太多的存储空间，请为每个消费者使用一个 bit(n)字段。
只要发出这些语句的事务保持打开状态，消费者就会相互锁定。所以保持简短以获得良好的并发性。