按顺序生成序列

Question

有没有一种方法可以为表记录生成某种顺序标识符？

假设我们有两个线程进行查询：

线程1：

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'hello');
commit;

线程2：

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'world');
commit;

根据时间顺序，外部观察者可能会看到记录 (2, 'world') 出现在记录 (1, 'hello') 之前。

虽然如此，我想要一种方法来获取自上次外部观察者检查它以来出现在“table1”中的所有记录。

因此，有没有办法按照插入顺序获取记录？也许可以用 OIDs 来帮助？

- Cyberax

---

你可能想查看这个答案：https://dev59.com/F6Tja4cB1Zd3GeqPEp4M#70360173 - john16384

Answer 1

没有。由于数据库表中没有自然顺序，你所能使用的只有表中的值。

好吧，还有Postgres特定系统列和ctid，你可以在一定程度上滥用它们。

元组ID（ctid）包含行的文件块编号和块中的位置。因此，这表示磁盘上的当前物理排序。后续添加将具有更大的ctid，通常情况下是这样的。你的SELECT语句可能如下所示。

SELECT *, ctid   -- save ctid from last row in last_ctid
FROM   tbl
WHERE  ctid > last_ctid
ORDER  BY ctid

ctid的数据类型为tid。例如：'(0,9)'::tid

然而，它作为长期标识符并不是稳定的，因为VACUUM或任何并发的UPDATE或其他操作都可以随时更改元组的物理位置。在事务的持续时间内，它是稳定的。如果您只是插入而没有其他操作，则应该在本地工作。

我建议除了serial列之外，还添加一个默认值为now()的时间戳列...

我还建议让列默认值填充您的id列（使用serial或IDENTITY列）。这将在比显式提取并插入数字更晚的阶段从序列中检索数字，从而最小化（但不是消除）竞争条件的窗口 - 即较低的id稍后可能被插入的机会。详细说明：

• 自动递增表列

- Erwin Brandstetter

---

我认为在这里依赖于物理行位置不是一个有效的策略。中止事务、丢失数据库连接等都可能导致后面的行被放置在错误的顺序中。此外，使用序列列也不足够，因为在插入行并提交线程1事务之间可能会有任何延迟，这可能会使线程2事务在其之前被提交和查看。 - Tometzky

@Tometzky：我同意这不是可靠的。原因比我之前提到的还要多。它只排除了一些导致元组无序的原因（例如从序列中单独获取id），但并没有排除其他原因。 - Erwin Brandstetter

我试图滥用CTID，但它没有起作用，因为我有时也想删除一些行。自动单调递增计数器会很好，但我想我必须进行显式锁定。 - Cyberax

如果表是只追加而没有任何UPDATE或DELETE，并且AUTO_VACUUM已禁用，则每次插入时ctid是否单调递增？ - Majid Azimi

@MajidAzimi：插入的行被追加到表的物理末尾是默认行为。但这是一种实现细节，没有保证。一旦你有并发事务，即使在那时，行也可能出现无序。 - Erwin Brandstetter

Answer 2

你想要的是强制事务按照插入顺序提交（使其插入可见）。就其他客户端而言，直到这些插入被提交，它们才“发生”，因为它们可能会回滚并消失。

即使您没有在显式的begin/commit中包装插入，事务提交（即使是隐式完成）仍然不一定按照行自身插入的顺序运行。它受操作系统CPU调度器排序决策等影响。

即使PostgreSQL支持脏读取，这也是正确的。只是因为你以给定的顺序开始三个插入，并不意味着它们会以那种顺序完成。

没有简单或可靠的方法可以做到您想要的同时保持并发性。您需要在单个工作程序上按顺序执行插入 - 或者像Tometzky建议的那样使用表锁定，其效果基本相同，因为在任何给定时间只有一个插入线程可以执行任何操作。

您可以使用咨询锁定，但效果相同。

使用时间戳不会有帮助，因为您不知道是否对于任何两个时间戳之间存在一个具有尚未提交的时间戳的行。

您不能依赖标识列，其中您仅读取到第一个“间隙”之前的行，因为由于回滚，系统生成的列中出现间隙是正常的。

我认为您应该退后一步，并考虑一下您为什么有这个要求，以及在此要求下，为什么要使用单独的并发插入。

也许您最好从单个会话中进行小块批量插入？

- Craig Ringer

---

我面临着类似的挑战，我的目的是公开事件源。每一行代表一个事件。 该源的契约应为页面不可变。 客户端将请求页面，如 page?after=<serial>&limit=XX。 然而，无序插入可能会导致 page?after=3 第一次返回 [5]，在提交“4”后返回 [4,5]。 这是一个仅插入表，因此它似乎是源的良好基础。 但是，由于序列号出现无序，我正在考虑是否有更好的方法。我们目前正在回顾若干页以检查新条目。 - acorello

这正是逻辑解码的用途。可以查看wal2json、pglogical等工具。 - Craig Ringer

Answer 3

我们在最近的PostgreSQL服务器中找到了另一种解决方案，类似于@erwin的答案，但使用txid。

在插入行时，不要使用序列，而是将txid_current()作为行ID插入。这个ID在每个新事务中单调递增。

然后，在从表中选择行时，将 id < txid_snapshot_xmin(txid_current_snapshot()) 添加到WHERE子句中。 txid_snapshot_xmin(txid_current_snapshot())对应于最旧的仍然打开的事务的事务索引。因此，如果第20行在第19行之前提交，它将被过滤掉，因为事务19仍然是打开的。当事务19提交时，第19行和第20行都将变为可见。

当没有打开的事务时，快照xmin将是当前运行的SELECT语句的事务ID。

返回的事务ID是64位的，高32位是一个纪元，低32位是实际ID。

这里是这些函数的文档：https://www.postgresql.org/docs/9.6/static/functions-info.html#FUNCTIONS-TXID-SNAPSHOT 感谢tux3提出的想法。

- Philippe

---

有趣。这样你就可以按事务开始时间的顺序获取记录，而不会错过任何东西。有时我需要在一个事务中插入多行，但我想我可以只使用txid_current()*1000，+1，+2，+3等。你在生产中使用过吗？有什么问题吗？ - bodrin

与其使用“*1000 +1, +2…”的技巧，也许你可以添加一个非唯一列，并在其中插入txid_current()，而不是使用表的id列。你将有多行具有相同的txid，但WHERE子句应该同样有效。 我们最终根本没有使用这个解决方案，并通过完全改变我们从API中插入和请求记录的方式来避免了问题，因此这从未达到生产。 - Philippe

@Philippe 你有关于你的解决方案的博客文章吗？ :) - Bruno Medeiros

好主意。危险在于有人可能意外地保留了一个写事务，这将导致查询永远不会“看到”任何新数据，直到事务提交为止。 - richard

Answer 4

如果您的意思是每个查询都必须看到world行，也必须看到hello行，那么您需要执行以下操作：

begin;
lock table table1 in share update exclusive mode;
insert into table1(id, value) values (nextval('table1_seq'), 'hello');
commit;

这个 share update exclusive mode 是最弱的锁模式，它是自我排他的——每次只能有一个会话持有它。

请注意，这不会使这个序列无缝——这是另一个问题。

- Tometzky