Re: [SL] Various Problems SL 2.4.11/PGSQL 8.1.3

[Jason K. <ja...@no...>]

Hello.  I am currently trying to fix some problems I am having with SL
2.4.11 on Postgresql 8.1.3 (migrated to another machine with 8.2.0 while
I try and fix it):

1) When I try to print an invoice containing an assembly, I get the
following error:

DBD::Pg::st execute failed: ERROR: column a.oid does not exist
LINE 8: ORDER BY a.oid
^
Error!
SELECT p.partnumber, p.description, p.unit, a.qty, 			pg.partsgroup,
p.partnumber AS sku FROM assembly a JOIN parts p ON (a.parts_id = p.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE a.bom = '1'
AND a.id = '10151' ORDER BY a.oid ERROR: column a.oid does not exist
LINE 8: ORDER BY a.oid ^

2) When I create a new inventory item, duplicate inventory items are
sometimes created (and when I add or remove stock of such, it is
duplicated as well).

3) When I create a new invoice, (very) old inventory items and payments
are sometimes shown on the new invoice along with what I have entered,
after the invoice is posted.

I have been reading the SQL-Ledger FAQ, the SQL-Ledger manual, the
Postgresql FAQ and Online Documentation and Googling the internet in
general in an attempt to determine how to fix my SL installation.  I've
especially been paying attention to any information regarding OID's.  I
have also been learning how to do basic querying in Postgresql.

I have looked at the assembly table and it has the following columns:
id   | parts_id | qty | bom | adj
Can I add the missing a.oid column to the table?  If so, how do I
populate it with the correct values?

In the FAQ, it states:

If you already built a dataset without this change (referring to
creating the orderitems, inventory and assembly tables WITH OIDS) you
will get an error when you try to save a PO or assembly.

"ERROR: column "oid" does not exist"

Drop the tables and rebuild WITH OIDS.

I attempted this (even though I can create and save an assembly without
a problem -- I just can't print an invoice that contains an assembly) by
using the COPY TO command to send the contents of each table to file,
dropping each table, creating each table as per the FAQ and then using
the COPY FROM command to repopulate the tables.  This did not help with
my first problem.  I will be testing to see if the second and third
issues are still present or not.

I believe this started (at least the assembly-related issues) when I
attempted to upgrade to SL 2.6.19 a few months ago.  I'm pretty sure I
didn't lock the database prior to untarring the upgrade file (and fixing
the file permissions).  The database was upgraded and I ended up with an
unusable mess!  Since I did not have the time to play around, I restored
a backup of the SQL-Ledger directory and restored a backup of the
database that I had made just prior.  Unfortunately, I don't recall
changing the DROP SEQUENCE entries to SELECT SETVAL entries as per the
FAQ.  Since I don't use assemblies very often, I didn't notice anything
wrong until recently.  The second and third problems that I listed above
didn't show up until much later.

If anyone has any wisdom to share, I would be most appreciative!!  Thanks!

Jason Kay.