sql - 使用 txid 获取最新的未处理、更新的行

Question

我的 PostgreSQL 中有一个表(实际上是它的多个表，但为了简单起见，我们假设它只有一个)和多个客户端，它们定期需要查询该表以查找更改的项目。这些是更新或插入的项目(已删除的项目通过首先将它们标记为删除，然后在宽限期后实际删除它们来处理)。
现在显而易见的解决方案是为每一行保留一个“修改过的”时间戳列，为每个客户端记住它，然后简单地获取更改的那些

SELECT * FROM the_table WHERE modified > saved_modified_timestamp;

然后将使用触发器使修改后的列保持最新，例如:

CREATE FUNCTION update_timestamp()
    RETURNS trigger
    LANGUAGE ‘plpgsql’
AS $$
    BEGIN
        NEW.modified = NOW();
        RETURN NEW;
    END;
$$;

CREATE TRIGGER update_timestamp_update
    BEFORE UPDATE ON the_table
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

CREATE TRIGGER update_timestamp_insert
    BEFORE INSERT ON the_table
    FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

这里明显的问题是 NOW()是转换开始的时间。因此，在获取更新的行时可能会发生事务尚未提交的情况，并且在提交时，时间戳低于saved_modified_timestamp，因此永远不会注册更新。
我想我找到了一个可行的解决方案，我想看看您是否可以找到这种方法的任何缺陷。
基本思想是使用 xmin (或者更确切地说 txid_current() )而不是时间戳，然后在获取更改时，将它们包装在显式事务中 REPEATABLE READ并阅读 txid_snapshot() (或者更确切地说，它包含的三个值 txid_snapshot_xmin() 、 txid_snapshot_xmax() 、 txid_snapshot_xip() )来自交易。
如果我正确阅读了 postgres 文档，那么所有更改都会进行 < txid_snapshot_xmax() 的交易。而不是在 txid_snapshot_xip()应该在那个 fetch 事务中返回。然后，此信息应该是再次获取时获取所有更新行所需的全部信息。然后选择看起来像这样，带有 xmin_version更换 modified柱子:

SELECT * FROM the_table WHERE
   xmin_version >= last_fetch_txid_snapshot_xmax OR xmin_version IN last_fetch_txid_snapshot_xip;

触发器将是这样的:

CREATE FUNCTION update_xmin_version()
    RETURNS trigger
    LANGUAGE ‘plpgsql’
AS $$
    BEGIN
        NEW.xmin_version = txid_current();
        RETURN NEW;
    END;
$$;

CREATE TRIGGER update_timestamp_update
    BEFORE UPDATE ON the_table
    FOR EACH ROW EXECUTE PROCEDURE update_xmin_version();

CREATE TRIGGER update_timestamp_update_insert
    BEFORE INSERT ON the_table
    FOR EACH ROW EXECUTE PROCEDURE update_xmin_version();

这行得通吗？或者我错过了什么？

Answer 1

感谢您对来自 txid_current() 的 64 位返回的澄清以及时代如何翻滚。对不起，我把那个纪元计数器和时间纪元混淆了。
我看不出您的方法有任何缺陷，但会通过实验验证在可重复读取事务中同时具有多个客户端 session ，使用 txid_snapshot_xip()快照不会引起任何问题。
我不会在实践中使用这种方法，因为我假设客户端代码需要解决处理相同更改(插入/更新/删除)的重复读取以及数据库内容和客户端工作集之间的定期协调来处理由于通信故障或客户端崩溃而导致的漂移。一旦编写了该代码，然后使用 now()在客户跟踪表中，clock_timestamp()在触发器中，当客户端拉取变更集时，宽限间隔重叠将适用于我遇到的用例。
如果需求要求比这更强的实时完整性，那么我会推荐分布式提交策略。