Reingest bib needs to deal with missing metabib.record_attr entries

[Ben Shum]

Evergreen master

This is for TPAC searches conducted in both public catalog and staff client.

It seems possible that bib records cannot be found even after updating them with more appropriate information or undeleting them. Running the following SQL identified several unfindable bibs in our system:

SELECT bre.id
FROM biblio.record_entry bre
LEFT JOIN metabib.record_attr mra ON (bre.id = mra.id)
WHERE NOT deleted AND active AND attrs is null;

It turns out we needed to add an entry back to metabib.record_attr with the ID number of the biblio.record_entry that was broken and then reingest the bibs to repopulate the values.

Suggestion from IRC (http://evergreen-ils.org/irc_logs/evergreen/2012-12/%23evergreen.18-Tue-2012.log#line406) was to add defensive code to check for existence of a metabib.record_attr entry and if not found, add one via INSERT.

[Robert J Jackson (rjackson-deactivatedaccount)]

Observed first occurrence of this issue after upgrade from 2.2.2 to 2.7.2. To fix an undeleted bib the following steps should work after upgrading beyond 2.6:

• prior to upgraded from 2.2.2 table needing blank entry was metabib.record_attr

• after 2.6?? metabib.record_attr is a view and table needing a blank/dummy entry is metabib.record_attr_vector_list

For bib with id of 19928246 here is a sample of SQL to make an item searchable again:

begin;

insert into metabib.record_attr_vector_list (source, vlist) values (19928246, '{631}'); --any value will do for the vlist entry as long as it is a valid format!

UPDATE config.internal_flag SET enabled = TRUE WHERE name = 'ingest.reingest.force_on_same_marc'; --force reingest

UPDATE biblio.record_entry SET id = id WHERE id = 19928246; --force reingest

select * from metabib.record_attr_vector_list where source = 19928246; -- verify output from the transaction before committing!

UPDATE config.internal_flag SET enabled = FALSE WHERE name = 'ingest.reingest.force_on_same_marc'; --turn off forced reingest

commit; --commit or rollback!!!

[Ben Shum (bshum)]

Updating this bug with some slightly newer SQL for 2.6+ systems:

-- This tells you how many bibs are affected:
SELECT COUNT(*) FROM biblio.record_entry WHERE deleted = FALSE AND id NOT IN (SELECT source FROM metabib.record_attr_vector_list);

-- This tells you exactly which bibs are affected, by bib ID:
SELECT bre.id FROM biblio.record_entry bre LEFT JOIN metabib.record_attr_vector_list mravl ON mravl.source = bre.id WHERE bre.deleted = FALSE AND bre.active = TRUE AND mravl.vlist IS NULL;

[Sarah Childs (sarahc)]

Still an issue in 2.9

[Rogan Hamby (rogan-hamby)]

Still an issue in 3.1. What's happening is in the biblio.indexing_ingest_or_delete it's checking for the trigger to be an update and then when the MARC hasn't changed skipping re-ingest steps. By telling it to only check for MARC being the same when the old row deleted is FALSE it will have it re-ingest properly when being undeleted. There may be some edge cases where a few unnecessary ingests happen but if so it will be only if MARC blobs are being manipulated _and_ left deleted which seems ... unlikely.

Patch forthcoming.

[Rogan Hamby (rogan-hamby)]

patch: user/rogan/lp_1091885_reingest_on_undelete

Do we need a pg_tap test for this on concerto data? The test is basically

SELECT deleted FROM biblio.record_entry WHERE id = 245; --should be false
SELECT EXISTS(SELECT 1 FROM metabib.record_attr WHERE id = 245); --should be true

UPDATE biblio.record_entry SET DELETED = TRUE WHERE id = 245;

SELECT EXISTS(SELECT 1 FROM metabib.record_attr WHERE id = 245); --should be false

UPDATE biblio.record_entry SET DELETED = FALSE WHERE id = 245;

SELECT EXISTS(SELECT 1 FROM metabib.record_attr WHERE id = 245); --should be true

[Rogan Hamby (rogan-hamby)]

Answered my own question:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=256207e2f3a819b6eac2d68a340f242b27431190

user/rogan/lp_1091885_reingest_on_undelete_squashed

pgtap test, upgrade and patch included

[Cesar V (cesardv)]

Tested Rogan's branch and it worked for me.
Signed off branch here:
http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/cesardv/rogan_lp1091885_reingest_on_delete_SIGNOFF

[Ben Shum (bshum)]

The pgtap test had an extra plan argument in it, I removed it from the commit and then tested it successfully.

Pushed to master. Arguably this sounds like it might be a bug fix? If so, will defer to release maintainers on whether to include this for backport to supported Evergreen releases.

[Michele Morgan (mmorgan)]

I would vote to backport this fix.