database – Ledger123.com

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.

Installation, SQL-Ledger Forum Posts

ERROR: relation “id” already exists

In a forum post from July 2009 on the SQL-Ledger User Forum, a user named Matt (username “red69p51”) reported an issue while creating a new database using SQL-Ledger version 2.8.24 with PostgreSQL 8.3.7 on Fedora 11. He encountered the following error message:

Error!

CREATE SEQUENCE id start 10000
ERROR: relation "id" already exists

Matt traced the error to specific lines in the Pg-tables.sql file, which attempt to create a sequence named “id”:

--
CREATE SEQUENCE id start 10000;
SELECT nextval ('id');
--

He questioned whether his upgrade missed something or if this was a known issue with that version of PostgreSQL.

Another forum member, “dws,” responded by explaining that the error serves as a security measure to prevent accidental overwriting of existing datasets.

Matt replied, indicating that he was unable to create any new SQL-Ledger databases due to this error, despite not overwriting any existing datasets. He mentioned having other SQL-Ledger databases on the same system with different names.

Upon further investigation, Matt discovered that the PostgreSQL template1 database contained both “basex” and “id” sequences and asked if this was normal.

“dws” clarified that this was not standard behavior. The template1 database in PostgreSQL is used as a template for creating new databases; any objects within it are copied into new databases upon creation. Since “id” already existed in template1, any new database would inherit this sequence, causing the CREATE SEQUENCE id command to fail due to the sequence’s prior existence.

Solution:

The root cause of the issue was that template1 had been inadvertently modified to include objects (like the “id” sequence) that should not be present. The recommended solution was to clean up the template1 database by removing the unintended objects. This would prevent the conflict when creating new databases, allowing SQL-Ledger’s setup scripts to execute without errors.

By ensuring that template1 only contains the default objects provided by PostgreSQL, users can avoid similar conflicts when creating new databases in SQL-Ledger.

General

Cloning SQL-Ledger dataset

Sometimes you need to create a new dataset with exact same customers, vendors, items, chart etc. from an existing dataset.

There are two ways to do that:

Create a blank data set, export data from existing data and then import it again in the new dataset using Import feature.
Create new dataset using existing dataset as template and make adjustments.

Below I am describing second method step-by-step as gives you more accurate final results avoiding all data import issues. You need to be familiar with Linux command line as well as PostgreSQL’s psql command line too.

First create new dataset using admin.pl so that new admin user is added in users/members file as well as templates folder is created. Selection of chart etc. during creation does not matter as we shall remove this database and will create again making copy of existing one.
Remove the database you created above using “dropdb -U sql-ledger your-new-dbname”
Recreate new database using existing database as template: “createdb -U sql-ledger your-new-dbname -T your-existing-dbname”
Remove transactions from newly clone database using these psql commands: “delete from ar; delete from ap; delete from gl; delete from oe; delete from invoice; delete from inventory; delete from dpt_trans; delete from acc_trans; delete from audittrail; delete from payment;”
Reset document numbers in System–Defaults menu.

As always, backup everything first. If you run into any problem, you can post your issue in our support forum referencing this post to get help.