2.0-2.1-upgrade-db.sql choke points during upgrade from 2.0.6

[Robert Soulliere]

Evergreen version: Upgrading process from 2.0.6 - 2.1RC2
OpenSRF version: 2.0
PostgreSQL version: 9.0
Linux distribution: Ubuntu 10.04

Description: When running the upgrade 2.0-2.1-upgrade-db.sql script on our virtual test server with our existing data I encountered several choke points in the transaction block which prevented the SQL updates from completing.

Here are the sql statements causing the errors and how I had to work around them:

DROP TABLE config.language_map;
– Error because tables linked to this table; used CASCADE to drop linked tables.

INSERT INTO metabib.record_attr (id,attrs)

    SELECT mrd.record, hstore(mrd) - '{id,record}'::TEXT[] FROM metabib.rec_descriptor mrd;

--Duplicate record error. I had to fix by deleting rows with duplicate (record) fileds in metabib.rec_descriptor before running script.

INSERT INTO config.org_unit_setting_type

( name, label, description, datatype ) VALUES

( 'circ.user_merge.delete_addresses',

 'Circ: Patron Merge Address Delete',

 'Delete address(es) of subordinate user(s) in a patron merge',

  'bool'

);

INSERT INTO config.org_unit_setting_type

( name, label, description, datatype ) VALUES

( 'circ.user_merge.delete_cards',

  'Circ: Patron Merge Barcode Delete',

  'Delete barcode(s) of subordinate user(s) in a patron merge',

  'bool'

);

INSERT INTO config.org_unit_setting_type

( name, label, description, datatype ) VALUES

( 'circ.user_merge.deactivate_cards',

  'Circ: Patron Merge Deactivate Card',

 'Mark barcode(s) of subordinate user(s) in a patron merge as inactive',

  'bool'

);

--Error indicated that these org setting types (names) already exist; I commented these lines out to bypass these inserts since they were in the transaction block and prevented other changes.

[Mike Rylander (mrylander)]

Going through these in order ...

==========
DROP TABLE config.language_map;
– Error because tables linked to this table; used CASCADE to drop linked tables.

*** This should have succeeded, because of the preceding:

ALTER TABLE config.i18n_locale DROP CONSTRAINT i18n_locale_marc_code_fkey;

*** In fact, since you dropped the i18n_locale table you'll probably have other issues.

==========

INSERT INTO metabib.record_attr (id,attrs) ...

*** Not distinct enough, so changing to:

INSERT INTO metabib.record_attr (id,attrs)
    SELECT DISTINCT ON (mrd.record) mrd.record, hstore(mrd) - '{id,record}'::TEXT[] FROM metabib.rec_descriptor mrd;

==========

New circ.user_merge.delete_addresses YAOUS and friends not new ...

*** Moving these to after the COMMIT

==========

[Mike Rylander (mrylander)]

I believe all of these are now addressed.