在Postgres中使用行版本实现增量客户端更新

问题描述

我是Postgres的新来者，到目前为止一直很喜欢。我已经考虑了很多问题，RTFM已尽我所能，但是已经走到了尽头，所以我需要向正确的方向轻推。

I am a newcomer to Postgres and love it so far. I've given this problem a lot of thought already, RTFM to the best of my ability, but hit a dead end, so I need a nudge in the right direction.

我正在设计一个数据库，其中每个感兴趣的实体都有一个 rowversion 列，该列从全局序列中分配一个值。因此，在最简单的情况下，在具有两行的表 emps 中： emp1 具有 rowversion @ 3 和 emp2 和 rowversion @ 5 ，我知道 emp2 在 emp1 之后进行了修改（即在以后的事务中-不必介意同一事务中的行是否具有相同的 rowversion ）。

I'm designing a database where each entity of interest has a rowversion column that gets assigned a value from a global sequence. So, in the simplest scenario, in a table emps with two rows: emp1 with rowversion@3 and emp2 with rowversion@5, I know emp2 was modified after emp1 (ie in a later transaction - don't really mind if rows within the same transaction have the same rowversion).

这构成了数据同步逻辑的基础，在那里，知道自己拥有@ 3之前一切的客户端可以获取使用诸如 SELECT * FROM emps WHERE rowversion> 3和rowversion 之类的查询进行最新更新。

This is to form the foundation of a data sync logic, where a client that knows they have everything up until @3, can get the latest updates using a query such as SELECT * FROM emps WHERE rowversion>3 and rowversion<=new_anchor.

以下是已经更新@ 3的客户端的示例场景-假设以下交易记录如下：

Here is an example scenario for a client already updated @3 - assume the following transactions since:

@3 - committed
@4 - committed
@5 - committed
@6 - in progress - not committed yet
@7 - committed
@8 - in progress - not committed yet
@9 - committed

客户更新分三个阶段进行：

Client update is performed in three stages:

1. 向数据库询问适当的 new_anchor 。


2. 执行 SELECT * FROM emps rowversion> 3和rowversion< = new_anchor 。


3. 将 new_anchor 值以及结果数据传递回客户端。

1. Ask the database for an appropriate new_anchor.
2. Perform SELECT * FROM emps WHERE rowversion>3 and rowversion<=new_anchor.
3. Pass the new_anchor value back to the client, along with the result data.

由于具有 rowversion @ 6和@ 8的行仍在进行中，因此 new_anchor 的行必须为@ 5，，以便我们的范围查询不会错过任何未提交的更新。现在，客户可以放心了，直到@ 5为止，一切都可以放心。

Since the rows with rowversion @6 and @8 are still in progress, new_anchor has to be @5, so that our range query doesn't miss any uncommitted updates. Now the client can be confident it has everything up until @5.

因此，实际问题得以提炼：这如何能够是否可以安全地确定code>而不必强制 SERIALIZABLE 或以其他方式严重影响性能？

So the actual problem distilled: how can this new_anchor be safely determined without forcing SERIALIZABLE or otherwise seriously hurting performance?

您可能会说我是从SQL Server借用的，通过 min_active_rowversion（）函数可以轻松解决此问题。在上述情况下，此函数将返回@ 6，因此您的 new_anchor 可以放心地为 min_active_rowversion（）-1 。我有点了解如何使用 active_rowversions 表，触发器和 SELECT min（id）从active_rowversions ，但这需要 READ UNCOMMITTED 隔离，在Postgres中不可用。

As you can probably tell I've borrowed this idea from SQL Server, where this problem is trivially solved by the min_active_rowversion() function. This function would return @6 in the above scenario, so your new_anchor can safely be min_active_rowversion() - 1. I sort of had an idea how this could be implemented in Postgres using an active_rowversions table, triggers, and SELECT min(id) FROM active_rowversions, but that would require READ UNCOMMITTED isolation, which is not available in Postgres.

真的很感谢任何帮助或想法。

I would really appreciate any help or ideas.

推荐答案

由于Postgres的。

Turns out the solution is much simpler than initially thought, thanks to Postgres' System Information Functions.

• txid_current（）可以在触发器中用于分配记录的 rowversion


• txid_snapshot_min（txid_current_snapshot（））可用于获取最小活动事务，其方式与SQL Server用户可能使用的方式相同。 min_active_rowversion（）。

• txid_current() can be used in a trigger to assign a record's rowversion.
• txid_snapshot_min(txid_current_snapshot()) can be used to get the minimum active transaction in the same way an SQL Server user might use min_active_rowversion().

最好的部分是这些都是64位的，永久的

The best part is these are 64-bit, permanent, non-subject to vacuuming:

Postgres确实很棒。

Postgres is truly amazing.

这篇关于在Postgres中使用行版本实现增量客户端更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！