DB upgrade script 1057 needs to ignore deleted copies when creating visibility atrributes

[John Merriam]

Evergreen 3.1.8

Likely also affects Evergreen 3.0, 3.2, 3.3 and master.

After our recent upgrade from Evergreen 2.12.12 to 3.1.8 we noticed that bib records which had copies that were marked as deleted in the asset.copy table before the upgrade was performed started showing up in search results for libraries that did not currently own the copy. The bib records were still active because other libraries in our consortium still owned copies of the books. But searches should have returned 'no entries' for the libraries who no longer owned copies (their one and only copy was marked as deleted).

We determined that what had happened is that when we ran the 1057.schema.copy_vis_attr_cache.sql database upgrade script, the INSERT statement to populate asset.copy_vis_attr_cache does so for ALL copies in asset.copy regardless of whether they are marked as deleted when the script is run.

If 1057.schema.copy_vis_attr_cache.sql has already been run on a database the fix is to do something like this:

DELETE FROM asset.copy_vis_attr_cache WHERE target_copy IN (SELECT id FROM asset.copy WHERE deleted IS TRUE);

The fix for the 1057.schema.copy_vis_attr_cache.sql would mean changing:

INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
    SELECT cp.id,
            cn.record,
            asset.calculate_copy_visibility_attribute_set(cp.id)
      FROM asset.copy cp
            JOIN asset.call_number cn ON (cp.call_number = cn.id);

to something like:

INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
    SELECT cp.id,
            cn.record,
            asset.calculate_copy_visibility_attribute_set(cp.id)
      FROM asset.copy cp
            JOIN asset.call_number cn ON (cp.call_number = cn.id)
      WHERE cp.deleted IS FALSE;

The above will probably look terrible once this is posted so I will also try to attach a patch. Basically just add WHERE cp.deleted IS FALSE

[Jason Stephenson (jstephenson)]

I set this bug to won't fix because I think it is too late, now, to go back and fix this old upgrade script. If anyone else disagrees, they can reopen this bug.