How do I fix the upgrade error on invcitem_misc_check?

Some folks have seen the following error when upgrading to xTuple ERP 4.12.0 and later releases:

The following error was encountered while trying to import postbooks-upgrade.sql into the database:
ERROR: check constraint "invcitem_misc_check" is violated by some row
CONTEXT: SQL statement "alter table public.invcitem add constraint invcitem_misc_check CHECK ((COALESCE(invcitem_item_id, -1) > 0 AND COALESCE(invcitem_number, '') = '' AND COALESCE(invcitem_descrip, '') = '' AND COALESCE(invcitem_salescat_id, -1) 0));"
PL/pgSQL function xt.add_constraint(text,text,text,text) line 22 at EXECUTE
QPSQL: Unable to create query
The upgrade has been aborted due to an error and your database was rolled back to the state it was in when the upgrade was initiated.

This is caused by a tightening of data quality rules in newer releases. If you see this error in the Updater then there is at least one invoice line that is both a specific item (e.g. YTRUCK1) and a miscellaneous item. To fix it and allow the upgrade, you need to clarify in the database which it is.

Run the following query in your SQL tool of choice (psql, pgAdmin, MetaSQL editor, etc.):

SELECT invchead_invcnumber, invchead_posted, invcitem_id, invcitem_linenumber, item_number, salescat_name, invcitem_number, invcitem_descrip
FROM invcitem
JOIN invchead ON invcitem_invchead_id = invchead_id
JOIN item ON invcitem_item_id = item_id
LEFT OUTER JOIN salescat ON invcitem_salescat_id = salescat_id
WHERE COALESCE(invcitem_item_id, -1) >= 0
AND (COALESCE(invcitem_number, '') != ''
OR COALESCE(invcitem_descrip, '') != ''
OR COALESCE(invcitem_salescat_id, -1) >= 0

That query should point you to the specific invoice lines causing the trouble. There are different ways to fix them depending on circumstances.

  • Start by backing up your database
  • If the invoice has not been posted (the second column of the query output is f or false):
    1. Accounting > Accounts Receivable > Invoice > List Unposted
    2. Edit the invoice (first column)
    3. Edit the specific line number (fourth column)
    4. If the data look correct on screen, click SAVE on the Invoice Item window. This is the most common case.
    5. If the data look incorrect on screen, fix things and click SAVE on the Invoice Item window. This most likely will be necessary when the invoice was supposed to be for a miscellaneous item but the user accidentally entered a specific item after the information about the miscellaneous item. Use the last three result columns from the query above to fix the record.
  • If the invoice has been posted then you'll need to fix the data more directly (psql, pgAdmin, MetaSQL editor, etc.):
    • Make note of the invcitem_id (third column) for each invoice line that should be a specific item (e.g. YTRUCK1) and update the database directly
      UPDATE invcitem SET invcitem_salescat_id = NULL, invcitem_number = NULL, invcitem_descrip = NULL
        WHERE invcitem_id = Your_invcitem_id_HERE;
    • Make note of the invcitem_id (third column) for each invoice line that should be a miscellaneous item and update the database directly
      UPDATE invcitem SET invcitem_item_id = NULL where invcitem_id = Your_invcitem_id_HERE;

If you rerun the SELECT above you should get no rows returned. When this is true, you've fixed this particular upgrade problem and should be able to try the upgrade again.

If the UPDATEs fail with complaints that you cannot edit posted invoices, you may have to temporarily disable triggers on the invcitem table:

ALTER TABLE public.invcitem DISABLE TRIGGER invcitembeforetrigger;

Don't forget to reenable the triggers when you're done:

ALTER TABLE public.invcitem ENABLE TRIGGER invcitembeforetrigger;