有序序列生成 [英] In-order sequence generation

问题描述

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

Is there a way to generate some kind of in-order identifier for a table records?

假设我们有两个线程在做查询:

Suppose that we have two threads doing queries:

线程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')之前.

It's entirely possible (depending on timing) that an external observer would see the (2, 'world') record appear before the (1, 'hello').

那很好，但是我想要一种方法来获取自上次外部观察者检查以来在"table1"中出现的所有记录.

That's fine, but I want a way to get all the records in the 'table1' that appeared since the last time the external observer checked it.

那么，有什么方法可以按插入顺序获取记录吗?也许OID可以帮助您?

So, is there any way to get the records in the order they were inserted? Maybe OIDs can help?

推荐答案

否.由于数据库表中的行没有自然顺序，因此您只需使用表中的值即可.

No. Since there is no natural order of rows in a database table, all you have to work with is the values in your table.

嗯，有 Postgres特定的系统列和 ctid 您可以在某种程度上滥用.

Well, there are the Postgres specific system columns cmin and ctid you could abuse to some degree.

元组ID(ctid)包含文件块号和该行在块中的位置.因此，这代表了磁盘上当前的物理顺序.以后添加的内容将具有更大的ctid，通常是 .您的SELECT语句可能看起来像这样

The tuple ID (ctid) contains the file block number and position in the block for the row. So this represents the current physical ordering on disk. Later additions will have a bigger ctid, normally. Your SELECT statement could look like this

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

ctid的数据类型为tid.示例:'(0,9)'::tid

ctid has the data type tid. Example: '(0,9)'::tid

但是，作为长期标识符，它不稳定，因为VACUUM或任何并发的UPDATE或某些其他操作可以随时更改元组的物理位置.在整个交易期间，它是稳定的.而且，如果您只是插入而什么也没，则它应该可以在本地工作.

However it is not stable as long-term identifier, since VACUUM or any concurrent UPDATE or some other operations can change the physical location of a tuple at any time. For the duration of a transaction it is stable, though. And if you are just inserting and nothing else, it should work locally for your purpose.

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

I would add a timestamp column with default now() in addition to the serial column ...

我还将让列默认值填充您的id列(

I would also let a column default populate your id column (a serial or IDENTITY column). That retrieves the number from the sequence at a later stage than explicitly fetching and then inserting it, thereby minimizing (but not eliminating) the window for a race condition - the chance that a lower id would be inserted at a later time. Detailed instructions:

• 自动增量表列

这篇关于有序序列生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！