Re: Upgrade duplicate key "transactions_pkey" problem SL 2.6.5 -> Ledgersmb 1.1.12

["Chris Travers" <..hidden..>]

Ok.  Here is the problem and the recommended solution.

SQL-Ledger uses a master id sequence to track all db objects.
However, there is no guarantee of uniqueness between tables in this
sequence.  However, since the id's are used as silent global foreign
keys, it is possible to have ambiguous data without realizing it.
This is what has happened here.  Frequent causes include:

1)  Bad data import scripts (the GNU-cash converter script is known to
introduce this problem
2)  There are bugs in old versions of pg_dump (prior to 8.0) which did
not properly set sequence values.

I have never seen this happen with SQL-Ledger by itself but I would
consider this a design flaw in the db schema and we are working to
move away from that model as fast as we can (customers and vendors no
longer use this sequence in 1.3).

This problem can result in the following further problems:

1)  In this case (limited to customers and vendors), you may ambiguity
relating to invoices (invoices and their payments may appear doubled
in the AR/AP reports but not in financial statements)
2)  Shipto addresses could be ambiguous.

If this were to happen in other tables, you could have really bad data
ambiguity problems (a payment against an invoice with a duplicate id
would double itself in the GL report).

Either way, it is a serious problem and needs to be addressed as soon
as possible,  My recommended solution is:

BEGIN;
LOCK customer IN EXCLUSIVE MODE;
ALTER TABLE customer ADD COLUMN new_id INT;
UPDATE customer SET new_id = nextval('id');
UPDATE ar SET customer_id = (select new_id FROM customer WHERE
customer.id = customer_id);
UPDATE project SET customer_id = (select new_id FROM customer WHERE
customer.id = customer_id);
UPDATE shipto SET trans_id = (select new_id FROM customer where
customer.id = trans_id);
UPDATE customer SET id = new_id;
ALTER TABLE customer DROP COLUMN new_id;
COMMIT;

Do the same for vendor id, updating the vendor_id field in ap and the
trans_id field in shipto.

Best Wishes,
Chris Travers