1

[Issue resolved. See Answer below.]

I have just encountered a series of “duplicate key value violates unique constraint” errors with a system that has been working well for months. And I cannot determine why they occurred.

Here is the error:

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback;
SQL [
INSERT INTO transaction_item 
  (transaction_group_id, transaction_type, start_time, end_time) VALUES 
  (?, ?::transaction_type_enum, ?, ?)
];
ERROR: duplicate key value violates unique constraint "transaction_item_pkey"
  Detail: Key (transaction_id)=(67109) already exists.;

Here is the definition of the relevant SEQUENCE and TABLE:

CREATE SEQUENCE transaction_id_seq AS bigint;

CREATE TABLE transaction_item (
  transaction_id bigint PRIMARY KEY DEFAULT NEXTVAL('transaction_id_seq'),
  transaction_group_id bigint NOT NULL,
  transaction_type transaction_type_enum NOT NULL,
  start_time timestamp NOT NULL,
  end_time timestamp NOT NULL
);

And here is the only SQL statement used for inserting to that table:

INSERT INTO transaction_item
  (transaction_group_id, transaction_type, start_time, end_time) VALUES 
  (:transaction_group_id, :transaction_type::transaction_type_enum, :start_time, :end_time)

As you can see, I’m not explicitly trying to set the value of transaction_id. I've defined a default value for the column definition, and using that to fetch a value formthe SEQUENCE.

I have been under the impression that the above approach is safe, even for use in high-concurrency situation. A SEQUENCE should never return the same value twice, right?

I’d really appreciate some help to understand why this has occurred, and how to fix it. Thank you!

8
  • 4
    At some point someone (or something) inserted rows into that table bypassing the default value by explicitly a value and thus the sequence wasn't incremented. That's the reason why generated always as identity is preferred over manually serial (which is essentially what you have created) Commented Jun 22, 2021 at 21:18
  • 1
    "the only SQL statement used for inserting to that table" - are you sure there? Commented Jun 22, 2021 at 21:20
  • Thanks. I am absolutely certain there were no manual inserts to this table or inserts from any other part of the system. I am literally the only developer and administrator of the system. Nobody else even has access to the database. Commented Jun 22, 2021 at 21:29
  • 1
    Postgres does not create new rows "out of the blue" and it does not reset sequences on its own either. I am inclined to believe Postgres more than you. If you are indeed the only user, then you did insert rows manually. Maybe due to a bug in your code or some automatic things happening in Spring/JPA under the hoods that you weren't aware of. Commented Jun 23, 2021 at 5:29
  • 1
    Thanks for the help, everyone. Please see above update. Problem solved. Commented Jun 23, 2021 at 12:59

1 Answer 1

1

I found the cause of this issue.

A few months ago (during development of this system) an issue was discovered that meant it was necessary to purge any existing test data from the database. I did this using DELETE FROM statements for all TABLES and ALTER ... RESTART statements for all SEQUENCES. These statements were added to the Liquibase configuration to be executing during startup for the new code. From inspecting the logs at the time, it appears that an instance of the system was still running at the time of the migration. And this happened: The new instance of the system deleted all data from the TRANSACTION_ITEM table, the still-running instance then added more data to that table, and then the new instance restarted the SEQUENCE use for inserting those records. So yesterday, when I received the duplicate key violations, it was because the SEQUENCE finally reached the ID values corresponding to the TRANSACTION_ITEM records that were added by still-running instance back when DB purge and migration occurred.

Long story, but it all makes sense now. Thanks to those who commented on this issue.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.