[OPEN-ILS-DEV] Question about another error when upgrading to 2.2 database schema

[Lazar, Alexey Vladimirovich]

On 2013-01-10, at 12:33 , "Lazar, Alexey Vladimirovich" <alexey.lazar at mnsu.edu> wrote:

> Hi, developers.
> 
> (Using my slightly modified version of the 2.1-2.2-upgrade-db.sql script from the 2.3.2 release tar ball.)
> 
> I am now running into the following error:
> 
> 9921 psql:2.1-2.2-upgrade-db_lazar.sql:9789: ERROR:  duplicate key value violates unique constraint "browse_entry_value_key"
> 9922 DETAIL:  Key (value)=(Walt Disney Studios Home Entertainment ) already exists.
> 9923 CONTEXT:  SQL statement "INSERT INTO metabib.browse_entry (value) VALUES
> 9924                     (metabib.browse_normalize(ind_data.value, ind_data.field))"
> 9925 PL/pgSQL function "reingest_metabib_field_entries" line 32 at SQL statement
> 9926 SQL statement "SELECT metabib.reingest_metabib_field_entries(NEW.id)"
> 9927 PL/pgSQL function "indexing_ingest_or_delete" line 129 at PERFORM
> 9928 SQL statement "UPDATE biblio.record_entry SET marc=marc WHERE id IN (SELECT record
> 9929         FROM
> 9930             metabib.full_rec a JOIN metabib.full_rec b USING (record)
> 9931         WHERE
> 9932             a.tag = 'LDR' AND a.value LIKE '______g%'
> 9933         AND b.tag = '007' AND b.value LIKE 'v___s%')"
> 9934 PL/pgSQL function "inline_code_block" line 24 at SQL statement
> 
> The error appears to be caused by the following chunk of code:
> 
>   9756 -- FIXME: add/check SQL statements to perform the upgrade
>   9757 DO $FUNC$
>   9758 DECLARE
>   9759     same_marc BOOL;
>   9760 BEGIN
>   9761     -- Check if it is already there
>   9762     PERFORM * FROM config.marc21_physical_characteristic_value_map v
>   9763         JOIN config.marc21_physical_characteristic_subfield_map s ON v.ptype_subfield = s.id
>   9764         WHERE s.ptype_key = 'v' AND s.subfield = 'e' AND s.start_pos = '4' AND s.length = '1'
>   9765             AND v.value = 's';
>   9766 
>   9767     -- If it is, bail.
>   9768     IF FOUND THEN
>   9769         RETURN;
>   9770     END IF;
>   9771 
>   9772     -- Otherwise, insert it
>   9773     INSERT INTO config.marc21_physical_characteristic_value_map (value,ptype_subfield,label)
>   9774     SELECT 's',id,'Blu-ray'
>   9775         FROM config.marc21_physical_characteristic_subfield_map
>   9776         WHERE ptype_key = 'v' AND subfield = 'e' AND start_pos = '4' AND length = '1';
>   9777 
>   9778     -- And reingest the blue-ray items so that things see the new value
>   9779     SELECT INTO same_marc enabled FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';
>   9780     UPDATE config.internal_flag SET enabled = true WHERE name = 'ingest.reingest.force_on_same_marc';
>   9781     UPDATE biblio.record_entry SET marc=marc WHERE id IN (SELECT record
>   9782         FROM
>   9783             metabib.full_rec a JOIN metabib.full_rec b USING (record)
>   9784         WHERE
>   9785             a.tag = 'LDR' AND a.value LIKE '______g%'
>   9786         AND b.tag = '007' AND b.value LIKE 'v___s%');
>   9787     UPDATE config.internal_flag SET enabled = same_marc WHERE name = 'ingest.reingest.force_on_same_marc';
>   9788 END;
>   9789 $FUNC$;
> 
> While I continue to poke around the related tables and views, I wonder if anyone with deeper knowledge of what's going on here has any helpful troubleshooting suggestions.

Continuing to poke around and an extra set of eyes looking at the problem eventually revealed that this error was related to the the first error I encountered when running this script [1]. That extra entry we had in config.metabib_field first prevented a new row from being added [1] and later caused a duplicate key value to be generated, as seen in this error above. Removing that custom entry fixed the issue and the schema upgrade script worked.

[1] http://libmail.georgialibraries.org/pipermail/open-ils-dev/2013-January/008747.html

Aleksey Lazar
PALS
IS Developer and Intergrator
507-389-2907
http://www.pals.org/
alexey.lazar at mnsu.edu