Problem


The Spire 2.8 migration code that upgrades the database now ensures that all Vendor Price records are unique.

It is possible that records can exist where the migration code can not determine which record to delete and you will see this error while upgrading the data.

---------------------------
Migration Failed
---------------------------
An error occurred while running migration 0003_vendor_price_unique.sql: (psycopg2.IntegrityError) could not create unique index "vendor_pricing_inventory_id_uom_vendor_id_idx"
DETAIL:  Key (inventory_id, uom, vendor_id)=(7376, EA, 97) is duplicated.
---------------------------
OK  
---------------------------


This means that there are two Vendor Price records virtually identical in every way except that the Price itself is different.


Solution

  1. Run this script in pgAdmin to see the records that need manual intervention.
    CREATE TEMPORARY TABLE vendor_price_dups (old_id INTEGER, new_id INTEGER);
    
    UPDATE vendor_pricing SET vendor_code = ''
        WHERE vendor_code IS NULL;
    
    -- Identical dups
    INSERT INTO vendor_price_dups
        SELECT v1.id, v2.id
        FROM vendor_pricing AS v1, vendor_pricing AS v2
        WHERE v1.id > v2.id
          AND v1.inventory_id = v2.inventory_id
          AND v1.uom = v2.uom
          AND v1.vendor_id = v2.vendor_id
          AND v1.price = v2.price
          AND v1.starting_date IS NULL AND v2.starting_date IS NULL
          AND v1.ending_date IS NULL AND v2.ending_date IS NULL
    --      AND (v1.valid_dates IS NULL AND v2.valid_dates IS NULL)
          AND v1.vendor_code = v2.vendor_code
          AND v1.updatecostprice = v2.updatecostprice
          AND v1.break_qty = v2.break_qty
          AND v1.break_qty_price = v2.break_qty_price;
    
    -- Dups with zero price
    INSERT INTO vendor_price_dups
        SELECT v1.id, v2.id
        FROM vendor_pricing AS v1, vendor_pricing AS v2
        WHERE v1.id <> v2.id
          AND v1.inventory_id = v2.inventory_id
          AND v1.uom = v2.uom
          AND v1.vendor_id = v2.vendor_id
          AND v1.price = 0 AND v2.price <> 0
          AND v1.starting_date IS NULL AND v2.starting_date IS NULL
          AND v1.ending_date IS NULL AND v2.ending_date IS NULL
    --      AND (v1.valid_dates IS NULL AND v2.valid_dates IS NULL)
          AND v1.updatecostprice = v2.updatecostprice
          AND v1.break_qty = v2.break_qty
          AND v1.break_qty_price = v2.break_qty_price
          AND NOT EXISTS (SELECT * FROM vendor_price_dups WHERE old_id = v1.id);
    
    -- Dups missing vendor code
    INSERT INTO vendor_price_dups
        SELECT v1.id, v2.id
        FROM vendor_pricing AS v1, vendor_pricing AS v2
        WHERE v1.id <> v2.id
          AND v1.inventory_id = v2.inventory_id
          AND v1.uom = v2.uom
          AND v1.vendor_id = v2.vendor_id
          AND v1.price = v2.price
          AND v1.starting_date IS NULL AND v2.starting_date IS NULL
          AND v1.ending_date IS NULL AND v2.ending_date IS NULL
    --      AND (v1.valid_dates IS NULL AND v2.valid_dates IS NULL)
          AND v1.vendor_code = '' AND v2.vendor_code <> ''
          AND v1.updatecostprice = v2.updatecostprice
          AND v1.break_qty = v2.break_qty
          AND v1.break_qty_price = v2.break_qty_price
          AND NOT EXISTS (SELECT * FROM vendor_price_dups WHERE old_id = v1.id);
    
    -- Normalize records
    DO $$
    BEGIN
        LOOP
            UPDATE vendor_price_dups AS a
            SET new_id = b.new_id
            FROM vendor_price_dups AS b
            WHERE a.new_id = b.old_id;
    
            IF NOT FOUND THEN
                EXIT;
            END IF;
        END LOOP;
    END;
    $$;
    
    
    WITH vprices AS (
        SELECT * FROM vendor_pricing
        WHERE id NOT IN (SELECT old_id FROM vendor_price_dups))
    SELECT v1.* 
    FROM vprices as v1, vprices as v2
    WHERE v1.id <> v2.id 
      AND v1.inventory_id = v2.inventory_id 
      AND v1.uom = v2.uom
      AND v1.vendor_id = v2.vendor_id;



    You should see something like this;

    Notice that inventory_id, vendor_id and uom have identical values. It is possible that you will see multiple pairs of records that are almost identical. Spire can not be certain about which record to delete since the first or second record may contain the correct price.

  2. Take note of the value(s) from id field(s) that you would like to delete and run this query using the id numbers of the records that you want to delete. Replacing the x, y and z with the desired ids. You may have as many id values as needed, separated by commas.

    DELETE FROM vendor_pricing WHERE id IN (x, y, z)


  3. To use the example above, I have decided that I would like to keep the top record because it has a Vendor part number and the higher price, so the second record would be deleted like this:

    DELETE FROM vendor_pricing WHERE id IN (7779)


    Press F5 or click the Execute Query button to run the query and you should see a message like seen below. This means that one record was deleted which is what I expected.

    Query returned successfully: one row affected, 36 ms execution time.

  4. Go back to Spire Tray and highlight the company. Click File, Upgrade database and enter the Password.

    Please contact Spire Support if you have questions or need assistance.