Postgresql 中文操作指南
9.17. Sequence Manipulation Functions #
本节介绍了用于操作 sequence objects 的函数，也称为序列发生器或简称序列。序列对象是使用 CREATE SEQUENCE 创建的特殊单行表。序列对象通常用于生成表的行的唯一标识符。 Table 9.52 中列出的序列函数提供了简单且对多用户安全的方法，用于从序列对象获取连续的序列值。
This section describes functions for operating on sequence objects, also called sequence generators or just sequences. Sequence objects are special single-row tables created with CREATE SEQUENCE. Sequence objects are commonly used to generate unique identifiers for rows of a table. The sequence functions, listed in Table 9.52, provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.
Table 9.52. Sequence Functions
Function Description |
nextval ( regclass ) → bigint Advances the sequence object to its next value and returns that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. If the sequence object has been created with default parameters, successive nextval calls will return successive values beginning with 1. Other behaviors can be obtained by using appropriate parameters in the CREATE SEQUENCE command. This function requires USAGE or UPDATE privilege on the sequence. |
setval ( regclass, bigint [, boolean ] ) → bigint Sets the sequence object’s current value, and optionally its is_called flag. The two-parameter form sets the sequence’s last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. The value that will be reported by currval is also set to the specified value. In the three-parameter form, is_called can be set to either true or false. true has the same effect as the two-parameter form. If it is set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. Furthermore, the value reported by currval is not changed in this case. For example, SELECT setval('myseq', 42); Next _nextval will return 43_ SELECT setval('myseq', 42, true); Same as above SELECT setval('myseq', 42, false); Next _nextval will return 42_ The result returned by setval is just the value of its second argument. This function requires UPDATE privilege on the sequence. |
currval ( regclass ) → bigint Returns the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did. This function requires USAGE or SELECT privilege on the sequence. |
lastval () → bigint Returns the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session. This function requires USAGE or SELECT privilege on the last used sequence. |
Caution
为避免阻塞从相同序列中获取数字的并发事务，如果调用事务后来中止， nextval 获得的值不会被回收以供再次使用。这意味着事务中止或数据库崩溃会导致分配值的序列中出现间隙。这种情况也会在没有事务中止时发生。例如带有 ON CONFLICT 子句的 INSERT 会计算要插入的元组，包括执行任何必需的 nextval 调用，在检测到导致按照 ON CONFLICT 规则执行的任何冲突之前。因此，PostgreSQL 序列对象 cannot be used to obtain “gapless” sequences 。
To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values. That can happen without a transaction abort, too. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences.
同样，由 setval 进行的序列状态更改对其他事务立即可见，并且如果调用事务回滚，不会撤销这些更改。
Likewise, sequence state changes made by setval are immediately visible to other transactions, and are not undone if the calling transaction rolls back.
如果在提交包含 nextval 或 setval 调用的事务之前数据库集群崩溃，序列状态更改可能未写入持久性存储，因此在集群重新启动之后，不确定序列的状态是原始状态还是更新状态。这对于数据库中的序列使用来说是无害的，因为未提交事务的其他影响也不会可见。但是，如果你希望将序列值用于数据库外的持久性目的，请确保在执行之前已提交 nextval 调用。
If the database cluster crashes before committing a transaction containing a nextval or setval call, the sequence state change might not have made its way to persistent storage, so that it is uncertain whether the sequence will have its original or updated state after the cluster restarts. This is harmless for usage of the sequence within the database, since other effects of uncommitted transactions will not be visible either. However, if you wish to use a sequence value for persistent outside-the-database purposes, make sure that the nextval call has been committed before doing so.
序列函数要操作的序列由 _regclass_参数指定，它只是 _pg_class_系统目录中序列的 OID。 但是，您不必手动查找 OID，因为 _regclass_数据类型的输入转换器将为您完成此工作。 有关详细信息，请参见 Section 8.19。
The sequence to be operated on by a sequence function is specified by a regclass argument, which is simply the OID of the sequence in the pg_class system catalog. You do not have to look up the OID by hand, however, since the regclass data type’s input converter will do the work for you. See Section 8.19 for details.