为什么即使对象创建失败 Postgres 序列项也会上升? [英] Why does Postgres sequence item go up even if object creation fails?

问题描述

我有一个 Postgres 项目，其中我的一个模型是 Client ，只是由其主键索引.我在创建客户端时遇到了问题，因为在某处有人创建了一个客户端，同时显式设置了我读过的主键不会影响 Postgres 的客户端序列表，它负责在任何时候自动递增主键 1创建了一个 Client 对象.

I have a Postgres item where one of my models is Client simply indexed by its primary key. I was having an issue creating clients because somewhere along the lines someone created a client while explicitly setting its primary key which I have read doesn't affect Postgres' sequence table for the Clients, which is responsible for auto-incrementing the primary key 1 whenever a Client object is created.

我运行了一些 SQL 查询来处理它，发现当前的序列值实际上比数据库 263 中客户端的最高 ID 低 1，即 262，因此它说 ID 为 263 的客户端已经存在.我尝试在我们的前端应用程序中创建一个客户端，再次出现错误，并决定重新运行查询.我看到数据库中没有创建新的客户端，正如预期的那样，但我也注意到序列值确实上升到了 263，所以当我再次尝试创建一个客户端时，它成功了！

I ran some SQL queries to play around with it, and found that the current sequence value was in fact 1 lower, 262, than the highest id for Clients in the database 263 so it was saying that a Client with the ID 263 already existed. I tried creating a Client in our front end application, got the error again, and decided to re run the queries. I saw that there was no new client created in the database, as expected, but I also noticed that the sequence value did go up to 263, so when I tried creating a client again it worked!

即使相关模型的创建失败，PostgreSQL 序列表也会增加这种正常行为吗?如果是这样，这似乎会导致一些严重的问题.

Is this normal behavior for a PostgreSQL sequence table to increment up even if creation of its related model fails ? If so it seems like that could cause some serious issues.

推荐答案

是的，这是预期的行为.查看文档:

Yes, this the expected behaviour. See docs:

nextval

将序列对象推进到其下一个值并返回该值.这是以原子方式完成的:即使多个会话同时执行 nextval，每个会话也会安全地接收不同的序列值.

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

如果使用默认参数创建了序列对象，则连续的 nextval 调用将返回从 1 开始的连续值.其他行为可以通过使用 CREATE SEQUENCE 命令中的特殊参数获得；有关详细信息，请参阅其命令参考页面.

If a 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 special parameters in the CREATE SEQUENCE command; see its command reference page for more information.

重要提示:为了避免阻塞从同一序列中获取数字的并发事务，nextval 操作永远不会回滚；也就是说，一旦获取了一个值，它就被认为是已使用的，即使执行 nextval 的事务后来中止.这意味着中止的事务可能会在分配的值序列中留下未使用的漏洞".

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

请注意，nextval 通常设置为自动增量/串行列的默认值.

Note that nextval is normally set as a default value for a autoincrement/serial column.

还试着想象一下，如果 nextval 回滚会是多么困难和低效.本质上，您必须在 nextval 上锁定每个客户端，直到处理整个事务(获得锁定的事务).在这种情况下，忘记并发插入.

Also try to imagine how hard and inefficient it would be if nextval were to rollback. Essentially you would have to lock every client on nextval until whole transaction (the one that acquired the lock) is processed. In that case forget about concurrent inserts.

如果是这样，那似乎会导致一些严重的问题.

If so it seems like that could cause some serious issues.

喜欢什么?您的情况的问题是有人手动为自动增量列指定了一个值.除非您是武士，否则您永远不应该这样做.:)

Like what? The issue in your case was that someone manually specified a value for an autoincrement column. You should never do that unless you are a samurai. :)

这篇关于为什么即使对象创建失败 Postgres 序列项也会上升?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！