On Dec 3, 2007 2:02 AM, Mark Clarke <
..hidden..> wrote:
Hi Chris,
Thanks for the help. Jus to be sure it should be safe to run:
delete from transactions left join ap on ap.id <
http://ap.id> =
transactions.id <http://transactions.id> where
table_name = 'ap' and
ap.id <http://ap.id> is null
delete from transactions left join ar on ar.id <
http://ar.id> =
transactions.id <http://transactions.id> where
table_name = 'ar' and
ar.id <http://ar.id> is null
delete from transactions left join gl on gl.id <
http://gl.id> =
transactions.id <http://transactions.id> where
table_name = 'gl' and
gl.id <http://gl.id> is null
What you are doing will not cause major issues. However, I would use a different method.
On second, thought, there is a bug to be aware of. THis doesn't cause a loss of accounting data integrity, but it can cause some other difficulties in dealing with this specific table. What I would actually do is rebuild these portions of your transaction table:
DELETE FROM transactions WHERE table_name IN ('ar', 'ap', 'gl');
INSERT INTO transactions (id, table_name)
SELECT id, 'ar' FROM ar
UNION
SELECT id, 'ap' FROM ap
UNION
SELECT id, 'gl' FROM gl
Just to note what is actually going on here: SQL-Ledger (which we forked from) did not provide any tracking of unique id's between tables hence you can get some weird join projection issues if the same id is used twice. This used to happen when older versions of PostgreSQL (which we no longer support) were used, and their backup/restore would fail to set the sequence value properly. I haven't seen this for a long time, but it is possible that it could be reset through other means, and bad import scripts could cause problems as well.
LedgerSMB 1.2 attempted to correct this issue by using rules on associated tables and replicating some information to this transactions table. Unfortunately there was a bug in our approach (corrected in 1.3) which causes the id sequence to be incremented again when the insert into transactions occurs. In most cases, this is not a big issue since it still guarantees the unique use of id's, but it has been fixed in
1.3 nonetheless.
Hope this helps,
Chris Travers