vandelay.auto_overlay_bib_record discrepancy

[Ben Shum]

From systems coming from Evergreen 1.6 and prior upgrading through to the more recent versions of Evergreen.

So it seems that at one point in time, the function vandelay.auto_overlay_bib_record had references to match_attr, and this is part of the 1.6-2.0 upgrade SQL. Later on, it seems it was removed (per http://git.evergreen-ils.org/?p=Evergreen.git;a=commit;h=813ac36) but there wasn't an upgrade SQL to fix this for people who had used the 1.6-2.0 upgrade SQL.

The resulting issue is that attempts to perform match-only overlay with 901c on systems upgraded from 1.6 and prior seem to fail on import. The complaining log entry was something like:

2013-04-18 13:24:04 violin open-ils.cstore: [ERR :12596:oils_sql.c:5585:13663042279640241] open-ils.cstore: Error with query [SELECT * FROM vandelay.auto_overlay_bib_record( '238505', '12' ) AS "vandelay.auto_overlay_bib_record" ;]: 0 ERROR: column m.matched_attr does not exist#012LINE 3: JOIN vandelay.bib_match m ON (m.matched_attr = a...#012 ^#012QUERY: SELECT d.* FROM vandelay.bib_attr_definition d#012 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)#012 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)#012 WHERE m.queued_record = import_id#012CONTEXT: PL/pgSQL function "auto_overlay_bib_record" line 22 at SQL statement

On inspecting the queue of bibs, it would show all the matched 901c bibs perfectly, but still be unable to import anything and overlay.

I'm going to try using the latest version of vandelay.auto_overlay_bib_record() that comes with master to see if it fixes our situation. If that works, we may need to add that to future upgrade SQLs and ensure that people don't get caught with the wrong function coming from 1.6 and prior.

[Ben Shum (bshum)]

So we grabbed the most up to date version of vandelay.auto_overlay_bib_record() from master to apply to our system and it solved our inability to load vandelay bibs with exact match on 901c.

Curious to get feedback on whether this really does affect all pre-2.0 systems or if it's just us now.

[Dan Scott (denials)]

Our system (currently on 2.3-ish, dating back to 1.4-ish days) has references to match_attr in vandelay.auto_overlay_bib_record().

[Dan Wells (dbw2)]

Hello Ben. I can confirm that our upgraded 1.6->2.3 system also has the old 2.0 version of vandelay.auto_overlay_bib_record(). There were a lot of changes for, I think 2.1 (or was it 2.2?). I wonder if anything else is missing?

Since anything missing is more likely to be a function issue and not a data issue, and not be at all heavy from a runtime perspective, maybe a 'reset_vandelay' type upgrade script would be our best option? By that I mean an upgrade which simply replaces all the vandelay functions with their expected versions. What do you think?

[Ben Shum (bshum)]

I think that sounds like a good idea to re-run all the vandelay functions in a future upgrade script to reset things to intended normal. It's what I would do if I had time to look closer at the other functions.

[Chris Sharp (chrissharp123)]

Hitting this nearly two years after reported. I can confirm that (re-)creating that function solves the problem for me. The farther away we get from the 1.6-2.0 era, though, I wonder how many others will encounter the problem in the future. We may just mark the bug "Won't Fix" and know that the bug report here shows the workaround.

[Justin Hopkins (hopkinsju)]

And another year later we add our voices to the chorus! We came up in the 1.6 days and only just now had this issue come up.

Pulling from the now current 2.9.1 sql scripts, running the following resolved the issue:

=====

CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
DECLARE
    eg_id BIGINT;
    match_count INT;
BEGIN

    PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;

    IF FOUND THEN
        -- RAISE NOTICE 'already imported, cannot auto-overlay'
        RETURN FALSE;
    END IF;

    SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;

    IF match_count <> 1 THEN
        -- RAISE NOTICE 'not an exact match';
        RETURN FALSE;
    END IF;

    -- Check that the one match is on the first 901c
    SELECT m.eg_record INTO eg_id
      FROM vandelay.queued_bib_record q
            JOIN vandelay.bib_match m ON (m.queued_record = q.id)
      WHERE q.id = import_id
            AND m.eg_record = oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]',marc)::BIGINT;

    IF NOT FOUND THEN
        -- RAISE NOTICE 'not a 901c match';
        RETURN FALSE;
    END IF;

    RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
END;
$$ LANGUAGE PLPGSQL;

=====

Why wouldn't we add this to an upgrade script?

[Chris Sharp (chrissharp123)]

Branch that adds an upgrade script here:

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

Not necessary for sites that started using Evergreen after the 2.1 era, but harmless to run and fixes it so that we don't keep stumbling over this.

[Bill Erickson (berick)]

Tested, signed-off, and pushed to master and rel_2_12. Thanks to you all.