Duplicate key value violates unique constraint with JPA in clustered heroku environment

Question

We faced a problem in production which we could not reproduce so far. The error message is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "token_pkey"

While it is clear what this error message is telling us, this error happened only once or better said during a time window of about 1 hour and until now and we cannot reproduce it.

We use Postgres 11.5 and did scale this Spring Boot App from 1 Node to 3 Nodes (and back to 1 Node). We are running this on Heroku. My current hypothesis is, that JPA does some caching of the next/last primary key value with @GeneratedValue(strategy = GenerationType.IDENTITY) and therefore if one node inserts a new row, the other node could still have the old value? If that is true, how can it be avoided?

Here is the code. Can you spot any problems with it?

create table token
(
    id                 bigserial primary key,
    user_id            bigint unique references user_ (id),
    access_token       varchar(255),
    code               varchar(255),
    expires_time_stamp bigint,
    refresh_token      varchar(255),
    event_sync_token   varchar(255)
);

Java JPA Entity Class

@Entity
public class Token {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id", nullable = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private User user;

    private String code;
    private String accessToken;
    private String refreshToken;
    private Long expiresTimeStamp;
    private String eventSyncToken;

}

Method creating the Token

public void initTokensFromCode(String code) {
        // ... skipped some non relevant lines
        Token token = googleTokenRepository.findByUserId(authenticationService.getCurrentUser().getId())
                .orElse(new Token());
        token.setUser(authenticationService.getCurrentUser());
        token.setCode(code);
        token.setAccessToken((String) jsonObject.get("access_token"));
        token.setRefreshToken(refresh_token);
        token.setExpiresTimeStamp(expiresTimestamp((long) jsonObject.get("expires_in")));

        repository.save(token);
    }

Repository (org.springframework.data.repository.CrudRepository)

public interface TokenRepository extends CrudRepository<Token, Long> {
    Optional<Token> findByUserId(@Param("user_id") long userId);
}

As you're scaling the DB access on multiple nodes, I suppose your nodes are pre-allocating the same or overlapping id ranges. You might need to reduce the allocationSize (INCREMENT BY) for your id field. This will but lead to more round-trips to the DB. — Hakan Dilek, Aug 14 '19 at 08:22
I don't see any option to define the Allocation Size with the IDENTITY strategy. Do you suggest switching strategy to SEQUENCE? – Following this answer, it should be ok to use IDENTITY with bigserial and the DB should handle the generation of the IDs. https://stackoverflow.com/a/40499193/621438 — LukeSolar, Aug 14 '19 at 09:04
The point is, a `serial` column is equivalent to using a sequence in Postgresql. https://www.postgresql.org/docs/8.1/datatype.html#DATATYPE-SERIAL So it might be better to explicitly use a `GenerationType.SEQUENCE` and and explicit `@SequenceGenerator` with `allocationSize` and keep an eye on the performance with the round-trips mentioned earlier. — Hakan Dilek, Aug 14 '19 at 09:22

Duplicate key value violates unique constraint with JPA in clustered heroku environment

0 Answers0