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

[Adrian B. <bla...@gm...>]

Suggestion:

Go to your backup of your SQL-Ledger data. copy. untar the copy if
required. Grep for "orderitems" and locate "CREATE TABLE orderitems (
....." as in the second item of the FAQ. Check it has the same format
as in the FAQ and hand edit if necessary.  Do the same for inventory
and assembly.
Also check your data that is the same as in the first item of the FAQ.
Now restore using this modified data.

It is important that you know how to backup, restore and drop a
database in Postgres. It may be worth purchasing the O'Reilly book. A
small price for valuable data.

Adrian


On 12/30/06, Jason Kay <ja...@no...> wrote:
> 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.
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users
>