postgresql - 仅向客户端发送更新的行

Question

我想创建一个 Web 服务，允许客户端获取表中的所有行，然后允许客户端仅获取新的或更新的行。

最简单的实现似乎是将当前时间戳发送给客户端，然后让客户端在后续请求中请求比时间戳更新的行。

这似乎是可行的，方法是在更新和插入触发器中保留时间戳设​​置为 NOW() 的“updated_at”列，然后查询较新的行，并向下传递 NOW() 的值。

问题是，如果有未提交的事务，这些事务会将 updated_at 设置为事务的开始时间，而不是提交时间。

因此，这个简单的实现不起作用，因为行可能会丢失，因为它们可能带有过去的时间戳。

尽管这似乎是一个非常普遍的需求，但我一直无法找到任何简单的解决方案:有什么想法吗？

可能的解决方案:

1. 在表中保留一个单调时间戳，在每个事务开始时将其更新为 MAX(NOW(), last_timestamp + 1) 并将其用作行时间戳。问题:这实际上意味着所有写事务都被完全序列化并锁定整个数据库，因为它们在更新时间表上发生冲突。

2. 在事务结束时，像上述解决方案一样，在更新表中添加一个从 NOW() 到时间的映射。这似乎需要采用显式锁定并使用序列生成非时间“时间戳”，因为仅在单行上使用 UPDATE 会导致在 SERIALIZABLE 模式下回滚。

3. PostgreSQL 以某种方式在提交时迭代所有更新的行并将 updated_at 设置为单调时间戳

4. PostgreSQL 本身以某种方式维护了一个事务提交时间表，目前它似乎没有这样做

使用内置的 xmin 列似乎也是不可能的，因为 VACUUM 可以将其丢弃。

如果能够在数据库中执行此操作而无需修改应用程序中的所有更新，那就太好了。

通常的做法是什么？

天真的解决方案的问题

如果不是很明显，这是使用 NOW() 或 CLOCK_TIMESTAMP() 的问题:

1. 在时间 1，我们在事务中运行 NOW() 或 CLOCK_TIMESTAMP()，它给出 1 并且我们更新一行设置时间 1 作为更新时间
2. 在时间 2，客户端获取所有行，我们告诉他在时间 2 之前我们已经提供了所有行
3. 在时间 3，事务在 updated_at 字段中以“时间 1”提交
4. 客户端从时间 2 开始请求更新的行(他从之前的完整获取请求中获得的时间)，我们查询 updated_at >= 2 并且我们什么都不返回，而不是返回刚刚添加的行
5. 那一行丢失了，客户永远也看不到

Answer 1

您的整个提议与 PostgreSQL 等符合 ACID 的 RDBMS 的一些基本原理背道而驰。事务开始时间(例如 current_timestamp())和其他基于时间的指标作为衡量特定客户端已收到或未收到的内容毫无意义。放弃整个想法。

假设您的客户端通过持久 session 连接到数据库，您可以遵循以下过程:

• session 开始时，为 session 用户CREATE TEMP UNLOGGED TABLE。该表仅包含您要从中获取数据的表的 PK 和上次更新时间。
• 客户端轮询新数据并仅接收那些具有尚未在临时表中的 PK 或现有 PK 但上次更新时间较新的记录。当前未提交的事务是不可见的，但将在下一次轮询新记录或更新记录时检索。更新时间是必需的，因为无法从所有并发客户端的临时表中删除记录。
• 检索记录的 PK 和最后更新时间存储在临时表中。
• 当用户关闭 session 时，临时表将被删除。

如果您想在每个客户端的多个 session 中保留检索到的记录，或者客户端在每次查询后断开连接，那么您需要一个常规表，但我建议还添加用户的 oid，以便所有用户都可以使用用于跟踪检索到的记录的单个表。在后一种情况下，您可以使用您的数据在表上创建一个 AFTER UPDATE 触发器，它会在一次扫描中为所有用户从具有获取记录的表中删除 PK。在他们的下一次投票中，客户将获得更新的记录。