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!

Gurtz
  • 293
  • 1
  • 2
  • 16
  • 3
    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) –  Jun 22 '21 at 21:18
  • 1
    "*the only SQL statement used for inserting to that table*" - are you sure there? – Bergi Jun 22 '21 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. – Gurtz Jun 22 '21 at 21:29
  • Look at the data in those conflicting rows. Maybe they trigger your memory. – Laurenz Albe Jun 23 '21 at 04:04
  • 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. –  Jun 23 '21 at 05:29
  • Check data of transaction_id = 67109, maybe start_time and end_time can get you an idea when that row was inserted (I am not sure because I don't know your data model). If you log insert statements you can look for that insert in them. – nachospiu Jun 23 '21 at 12:09
  • 1
    Thanks for the help, everyone. Please see above update. Problem solved. – Gurtz Jun 23 '21 at 12:59
  • @a_horse_with_no_name Gotcha. I've added the answer and will accept it tomorrow. (Apparently I can't do so today.) Thanks! – Gurtz Jun 23 '21 at 13:37

1 Answers1

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.

Gurtz
  • 293
  • 1
  • 2
  • 16