Upgrade Failure Due to Duplicate Invoice IDs

In August 2009, Dominic Raywood (username “RubiconCSL“) posted on the SQL-Ledger User Forum seeking assistance with an upgrade issue.

Issue

Dominic was attempting to upgrade from SQL-Ledger version 2.0.8 to 2.8.24. During the upgrade process, he encountered the following error:

Error!

insert into invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, deliverydate, project_id, serialnumber, itemnotes, lineitemdetail)
select id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, deliverydate, project_id, serialnumber, itemnotes, lineitemdetail from temp
ERROR: duplicate key violates unique constraint "invoice_pkey"

He attempted to adjust the nextval of the id sequence after restoring the database but continued to receive the same error.

Community Response

A forum member, “izzyb,” responded with suggestions:

• Database Backend Inquiry: Asked whether Dominic was using PostgreSQL 7.x or 8.x and recommended checking the FAQ for PostgreSQL 8.x if applicable.
• Adjusting the Sequence Properly:
• Suggested verifying the highest id in the invoice table:
  sql SELECT id FROM invoice ORDER BY id DESC LIMIT 1;
• Recommended setting the sequence value using the retrieved id: SELECT setval('invoiceid', xxx); where xxx is the highest id obtained.
• Potential Version Upgrade Issues: Noted that upgrading from version 2.0.8 to 2.8.24 is a significant jump and may introduce other issues, possibly causing the sequence to be out of sync.
• Caution: Advised Dominic to ensure he has a backup before attempting any changes.

Resolution

Dominic discovered the root cause of the problem:

• Duplicate IDs in Invoice Table: His existing installation had duplicate id values in the invoice table, which violated the unique constraint invoice_pkey during the upgrade.
• Steps Taken:
• Reset the ID Sequence: Adjusted the sequence to align with the correct IDs.
• Updated Duplicate Rows: Modified the duplicate row IDs to ensure uniqueness. He noted that these IDs did not seem to be used as foreign keys in other tables.
• Outcome: After making these changes, the upgrade proceeded successfully.
• Next Challenge: Dominic mentioned that he now faced a login issue, which he intended to address separately.

Summary

When upgrading SQL-Ledger and encountering a “duplicate key violates unique constraint” error:

• Check for Duplicate IDs in the affected table (e.g., invoice).
• Reset the Sequence to match the highest existing ID:

  SELECT id FROM invoice ORDER BY id DESC LIMIT 1;
  SELECT setval('invoiceid', <highest_id>);

• Update Duplicate Rows to ensure all IDs are unique.
• Ensure Data Integrity: Verify that changes do not adversely affect foreign key relationships.
• Backup: Always create a backup before making significant database changes.

By following these steps, users can resolve upgrade issues related to duplicate keys and successfully transition between SQL-Ledger versions.