Question 247017 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Auto-Increment Behavior After Server Restart in Spring Boot

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.

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.

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?

Thank you for your assistance in advance.

Here’s my 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…

@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();
    }
}
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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...


  2. 0

    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:

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

    • 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.

    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).

    Login or Signup to reply.
  3. 0

    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.

    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.

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

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search