I am using Spring Boot with JPA and PostgreSQL for my application. I have noticed that every time I restart the server, it seems to "skip" 50 IDs for records in a table. For example, if the last record in the table has an ID of 50, when I insert a new record via the API after a server restart, the new record is assigned an ID of 100. However, if I insert records without restarting, it goes to 101, 102, and so on as expected.

我在我的应用程序中使用了带有JPA和PostgreSQL的Spring Boot。我注意到，每次重新启动服务器时，似乎都会为表中的记录“跳过”50个ID。例如，如果表中的最后一条记录的ID为50，则当我在服务器重新启动后通过API插入新记录时，新记录的ID将被分配为100。但是，如果我在不重新启动的情况下插入记录，它会像预期的那样转到101、102等。

I have a base class BaseEntity for my model classes that generates the ID with @GeneratedValue(strategy = GenerationType.AUTO). Hibernate DDL is configured to validate only.

我的模型类有一个基类BaseEntity，它用@GeneratedValue(Strategy=GenerationType.AUTO)生成ID。Hibernate DDL配置为仅进行验证。

Is there a way to ensure that the auto-increment behavior starts from the last known ID after a server restart, rather than skipping values? Is this related to PostgreSQL's sequence behavior?

有没有办法确保自动递增行为在服务器重新启动后从最后一个已知ID开始，而不是跳过值？这与PostgreSQL的序列行为有关吗？

Thank you for your assistance in advance.

提前感谢您的帮助。

Here's my BaseEntity:

下面是我的BaseEntity：

@Getter
@Setter
@MappedSuperclass
public abstract class BaseEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof BaseEntity that)) return false;
        return id.equals(that.id);
    }
    @Override
    public int hashCode() {
        return Objects.hash(id);
    }
    @Override
    public String toString() {
        return "BaseEntity {" +
                "id = " + id +
                "}";
    }
}

I have a second BaseEntity that inserts created_at columns etc... Don't know if it helps...

我还有第二个BaseEntity，它插入Created_At列等...不知道这有没有帮助。

@Getter
@Setter
@MappedSuperclass
public abstract class BaseEntityAudit extends BaseEntity implements Serializable {
    private String createdBy;
    private String updatedBy;
    @CreationTimestamp
    @Column(name = "created_at", updatable = false)
    private Date createdAt;
    @UpdateTimestamp
    @Column(name = "updated_at")
    private Date updatedAt;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof BaseEntityAudit)) return false;
        if (!super.equals(o)) return false;
        BaseEntityAudit that = (BaseEntityAudit) o;
        return createdBy.equals(that.createdBy) &&
                updatedBy.equals(that.updatedBy) &&
                createdAt.equals(that.createdAt) &&
                updatedAt.equals(that.updatedAt);
    }

    @Override
    public int hashCode() {
        return Objects.hash(super.hashCode(),
                createdBy, updatedBy, createdAt, updatedAt);
    }

    @Override
    public String toString() {
        return "BaseEntityAudit{" +
                "createdBy='" + createdBy + '\'' +
                ", updatedBy='" + updatedBy + '\'' +
                ", createdAt=" + createdAt +
                ", updatedAt=" + updatedAt +
                "} " + super.toString();
    }
}

Is there a way to ensure that the auto-increment behavior starts from
the last known ID after a server restart, rather than skipping values?

Not really. Sequence by itself guarantees it returns different number greatest than latest one every time it's called only.

不怎么有意思。序列本身保证它在每次被调用时返回的数字最大值不同于最近的一个。

But sequences are not living in database as final product, they are used in cojunction with table rows usually. Going to use multiple transtactions at same time in PostgreSQL leads to side effects:

但序列并不是作为最终产品存在于数据库中，它们通常与表行结合使用。在PostgreSQL中同时使用多个transaction会导致副作用：

• you are getting gap in column that uses sequences, if a transatction was reverted;

  如果事务被还原，您将在使用序列的列中获得GAP；

  
  



• you are getting larger sequence number before lesser one in column, if a transaction starts after another one, but finishing before that one.

  如果一个事务在另一个事务之后开始，但在该事务之前完成，则您将在列中较小的事务之前获得较大的序列号。

  
  

Sequences also have cache parameter, by default it's value equals to 1, but if it was setted up when a sequqnce was created, it may lead to gaps every time when client (backend / application) connection to PostgreSQL is closed.

Sequence也有缓存参数，默认情况下它的值等于1，但如果它是在创建Sequencce时设置的，那么每次客户端(后端/应用程序)关闭到PostgreSQL的连接时，它可能会导致空白。

Is this related to PostgreSQL's sequence behavior?

It's related to PostgreSQL sequence behavior too. And to the fact that sequences was developed to do their job in ACID-compatible multitransactional DBMS, and not intendet to not have gaps (by reason it's possible only to do 1 thing of 2: be fast and ACID-compatible in concurrent-transaction environment, or to have no gaps).

它也与PostgreSQL序列行为有关。而且，开发序列是为了在兼容ACID的多事务DBMS中完成它们的工作，而不是为了没有差距(因此，可能只做了两件事中的一件：在并发事务环境中快速且与ACID兼容，或者没有差距)。

Seem to me you are configuring hibernate to use a DB sequence with increment set to 50 for the ID value. If the sequence starts from 1 , then 1st time you get a value from it is 1 , and 2nd time is 51 , and 3rd time is 101 and so on.

在我看来，您正在将Hibernate配置为使用一个数据库序列，其中ID值的增量设置为50。如果序列从1开始，则第一次得到的值是1，第二次是51，第三次是101，依此类推。

So it just like whenever hibernate gets a value from it , the next 50 ID will be reserved for it to use only. If all 50 ID are used up , it will call the sequence again to reserve the next 50 ID to use and so on. If the application shutdown before using all of these 50 ID , after it restarts it will call the sequence to reserve the next 50 values , leaving some ID gap as you describe.

因此，就像每当Hibernate从它那里获得一个值时，下一个50个ID将被保留仅供其使用。如果所有50个ID都用完了，它将再次调用该序列以保留下一个50个ID以供使用，依此类推。如果应用程序在使用所有这50个ID之前关闭，则在它重新启动后，它将调用序列以保留接下来的50个值，从而留下一些ID间隙，如您所述。

The correct way to configure the ID to be auto-increment is :

将ID配置为自动递增的正确方法是：

public abstract class BaseEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

}

But not using GenerationType.AUTO. You also need to make sure the ID column in DB is configured as auto-increment

但不使用GenerationType.AUTO。您还需要确保数据库中的ID列配置为自动递增

I just found out what the issue was. The pg_sequence increment_by column was set to 50. I'm not really sure why but this database was configured as such...

我刚发现问题出在哪里。PG_SEQUENCE INCREMENT_BY列被设置为50。我真的不知道为什么，但这个数据库是这样配置的。