Call-numbers migration

Context

The new feature was implemented to make it possible to browse call-numbers by their types. It was implemented by using references to call-number types and changing logic for calculating shelving order that is used for call-number sorting.

Instructions

Step 1

Run time:

Before upgrade

Precondition:

Run script: 

SELECT
    ct1.id AS actual_id,
    expected.expected_id AS expected_id,
    ct1.jsonb->>'name' AS common_name
FROM
    <tenant>_mod_inventory_storage.call_number_type ct1
JOIN
    (VALUES
        ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
        ('6caca63e-5651-4db6-9247-3205156e9699', 'Other scheme'),
        ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
        ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
        ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
    ) AS expected (expected_id, common_name)
ON
    ct1.jsonb->>'name' = expected.common_name
    AND ct1.id::text <> expected.expected_id;

If there are NO results then skip this step

Action:

Run script:

-- Drop foreign key on holdings_record table
ALTER TABLE <tenant>_mod_inventory_storage.holdings_record
DROP CONSTRAINT IF EXISTS callnumbertypeid_call_number_type_fkey;

-- Create a temporary table to store the mapping
CREATE TEMPORARY TABLE mapping_temp AS
SELECT
    ct1.id AS actual_id,
    expected.expected_id AS expected_id,
    ct1.jsonb->>'name' AS common_name
FROM
    <tenant>_mod_inventory_storage.call_number_type ct1
JOIN
    (VALUES
        ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
        ('6caca63e-5651-4db6-9247-3205156e9699', 'Other scheme'),
        ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
        ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
        ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
    ) AS expected (expected_id, common_name)
ON
    ct1.jsonb->>'name' = expected.common_name
    AND ct1.id::text <> expected.expected_id;

-- Update call-number type references in the holdings_record table
UPDATE <tenant>_mod_inventory_storage.holdings_record AS hr
SET jsonb = jsonb_set(hr.jsonb, '{callNumberTypeId}', to_jsonb(updated.expected_id))
FROM (
    SELECT actual_id, expected_id
    FROM mapping_temp
) AS updated
WHERE
    hr.callnumbertypeid = updated.actual_id;

-- Update call-number type references (itemLevelCallNumberTypeId) in the item table
UPDATE <tenant>_mod_inventory_storage.item AS it
SET jsonb = jsonb_set(it.jsonb, '{itemLevelCallNumberTypeId}', to_jsonb(updated.expected_id))
FROM (
    SELECT actual_id, expected_id
    FROM mapping_temp
) AS updated
WHERE
    it.jsonb->>'itemLevelCallNumberTypeId' = updated.actual_id::text;

-- Update call-number type references (effectiveCallNumberComponents,typeId) in the item table
UPDATE <tenant>_mod_inventory_storage.item AS it
SET jsonb = jsonb_set(it.jsonb, '{effectiveCallNumberComponents,typeId}', to_jsonb(updated.expected_id))
FROM (
    SELECT actual_id, expected_id
    FROM mapping_temp
) AS updated
WHERE
    it.jsonb->'effectiveCallNumberComponents'->>'typeId' = updated.actual_id::text;

-- Update call_number_type table with expected ids
UPDATE <tenant>_mod_inventory_storage.call_number_type AS ct1
SET
    id = updated.expected_id::uuid
FROM (
    SELECT actual_id, expected_id
    FROM mapping_temp
) AS updated
WHERE
    ct1.id = updated.actual_id;

-- Recreate foreign key on holdings_record table
ALTER TABLE IF EXISTS <tenant>_mod_inventory_storage.holdings_record
    ADD CONSTRAINT callnumbertypeid_call_number_type_fkey FOREIGN KEY (callnumbertypeid)
    REFERENCES <tenant>_mod_inventory_storage.call_number_type (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

-- Drop the temporary mapping table
DROP TABLE IF EXISTS mapping_temp;

The script could be split into several steps:

1. Drop foreign key on holdings_record table: 

   Disable FK Expand source

   ALTER TABLE <tenant>_mod_inventory_storage.holdings_record 
   DROP CONSTRAINT callnumbertypeid_call_number_type_fkey;

2. Update call-number type references in the holdings_record table: 

   Update holdings Expand source

   UPDATE <tenant>_mod_inventory_storage.holdings_record AS hr
   SET jsonb = jsonb_set(hr.jsonb, '{callNumberTypeId}', to_jsonb(updated.expected_id))
   FROM (
       SELECT
           ct1.id AS actual_id,
           expected.expected_id AS expected_id,
           ct1.jsonb->>'name' AS common_name
       FROM
           <tenant>_mod_inventory_storage.call_number_type ct1
       JOIN
           (VALUES
               ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
               ('6caca63e-5651-4db6-9247-3205156e9699','Other scheme'),
               ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
               ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
               ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
           ) AS expected (expected_id, common_name)
       ON
           ct1.jsonb->>'name' = expected.common_name
           AND ct1.id::text <> expected.expected_id
   ) AS updated
   WHERE
       hr.callnumbertypeid = updated.actual_id;

3. Update call-number type references (itemLevelCallNumberTypeId) in the item table: 

   Item's itemLevelCallNumberTypeId update Expand source

   UPDATE <tenant>_mod_inventory_storage.item AS it
   SET jsonb = jsonb_set(it.jsonb, '{itemLevelCallNumberTypeId}', to_jsonb(updated.expected_id))
   FROM (
       SELECT
           ct1.id AS actual_id,
           expected.expected_id AS expected_id,
           ct1.jsonb->>'name' AS common_name
       FROM
           <tenant>_mod_inventory_storage.call_number_type ct1
       JOIN
           (VALUES
               ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
               ('6caca63e-5651-4db6-9247-3205156e9699','Other scheme'),
               ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
               ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
               ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
           ) AS expected (expected_id, common_name)
       ON
           ct1.jsonb->>'name' = expected.common_name
           AND ct1.id::text <> expected.expected_id
   ) AS updated
   WHERE
       it.jsonb->>'itemLevelCallNumberTypeId' = updated.actual_id::text;
   
   

4. Update call-number type references (effectiveCallNumberComponents,typeId) in the item table: 

   Item's effectiveCallNumberComponents update Expand source

   UPDATE <tenant>_mod_inventory_storage.item AS it
   SET jsonb = jsonb_set(it.jsonb, '{effectiveCallNumberComponents,typeId}', to_jsonb(updated.expected_id))
   FROM (
       SELECT
           ct1.id AS actual_id,
           expected.expected_id AS expected_id,
           ct1.jsonb->>'name' AS common_name
       FROM         
   		<tenant>_mod_inventory_storage.call_number_type ct1
       JOIN
           (VALUES
               ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
               ('6caca63e-5651-4db6-9247-3205156e9699','Other scheme'),
               ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
               ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
               ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
           ) AS expected (expected_id, common_name)
       ON
           ct1.jsonb->>'name' = expected.common_name
           AND ct1.id::text <> expected.expected_id
   ) AS updated
   WHERE
       it.jsonb->'effectiveCallNumberComponents'->>'typeId' = updated.actual_id::text;
   
   

5. Update call_number_type table with expected ids: 

   Call-number type update Expand source

   UPDATE <tenant>_mod_inventory_storage.call_number_type AS ct1
   SET
       id = updated.expected_id
   FROM (
       SELECT
           ct1.id AS actual_id,
           expected.expected_id::uuid AS expected_id,
           ct1.jsonb->>'name' AS common_name
       FROM         
   		<tenant>_mod_inventory_storage.call_number_type ct1
       JOIN
           (VALUES
               ('95467209-6d7b-468b-94df-0f5d7ad2747d', 'Library of Congress classification'),
               ('6caca63e-5651-4db6-9247-3205156e9699','Other scheme'),
               ('03dd64d0-5626-4ecd-8ece-4531e0069f35', 'Dewey Decimal classification'),
               ('054d460d-d6b9-4469-9e37-7a78a2266655', 'National Library of Medicine classification'),
               ('fc388041-6cd0-4806-8a74-ebe3b9ab4c6e', 'Superintendent of Documents classification')
           ) AS expected (expected_id, common_name)
       ON
           ct1.jsonb->>'name' = expected.common_name
           AND ct1.id::text <> expected.expected_id
   ) AS updated
   WHERE
       ct1.id = updated.actual_id;
   
   

6. Recreate foreign key on holdings_record table:

   Recreate FK Expand source

   ALTER TABLE IF EXISTS <tenant>_mod_inventory_storage.holdings_record
       ADD CONSTRAINT callnumbertypeid_call_number_type_fkey FOREIGN KEY (callnumbertypeid)
       REFERENCES <tenant>_mod_inventory_storage.call_number_type (id) MATCH SIMPLE
       ON UPDATE NO ACTION
       ON DELETE NO ACTION;

Action timing:

Tested on database with 7.8M rows in holdings_record table and 9M rows in item table.

Step 2 (Option 1)

Run time:

After upgrade, before reindex

Precondition:

No preconditions

Action:

Run script:

-- Change search path for the script
SET search_path = <tenant>_mod_inventory_storage;

-- Crete/replace function to recalculate effective values and shelving order in items
CREATE OR REPLACE FUNCTION set_item_effective_values(holding jsonb, item jsonb)
  RETURNS jsonb AS $$
  BEGIN
    RETURN set_effective_shelving_order(
          item
          || jsonb_build_object('effectiveLocationId',
               COALESCE(item->>'temporaryLocationId', item->>'permanentLocationId', holding->>'effectiveLocationId'))
          || effective_call_number_components(holding, item));
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

-- Recalculate effective values and shelving order in items
UPDATE item
SET jsonb = set_item_effective_values(item_data.holdings, item_data.item)
FROM (
    select item.id, hr.jsonb as holdings, item.jsonb as item from holdings_record hr
    join item on item.holdingsRecordId = hr.id
    where trim2null(hr.jsonb->>'callNumberTypeId') IS NOT NULL
    and trim2null(item.jsonb->'effectiveCallNumberComponents'->>'typeId') IS NULL
) AS item_data
WHERE item.id = item_data.id;

Action timing:

Tested on database with 9M rows in item table.

Execution time: TBD

Step 2 (Option 2)

Run time:

After upgrade, before reindex

Precondition:

No preconditions

Action:

1. Disable triggers

    Expand source

   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE TRIGGER audit_item;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER check_statistical_code_references_on_insert;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER check_statistical_code_references_on_update;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER set_id_in_jsonb;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER set_item_md_json_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER set_item_md_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER set_item_ol_version_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER update_item_references;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER update_item_status_date;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER updatecompleteupdateddate_item_delete;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item DISABLE  TRIGGER updatecompleteupdateddate_item_insert_update;

2. Create uuid_range table

    Expand source

   create table public.uuid_range
   (
       id             serial primary key,
       partition      uuid,
       subrange_start uuid,
       subrange_end   uuid,
       completed      boolean default false
   );-- If table exists and filled then do:
   UPDATE public.uuid_range SET completed=false;
   -- If table was just created then fill it:
   
   DO
   $$
       DECLARE
           partition_start UUID;
           subrange_start  UUID;
           subrange_end    UUID;
       BEGIN
           FOR i IN 0..15
               LOOP
                   partition_start := (rpad(to_hex(i), 8, '0') || '-0000-0000-0000-000000000000')::UUID;
                   subrange_start := partition_start;
   
                   FOR j IN 1..4096
                       LOOP
                           IF i < 15 OR (i = 15 AND j < 4096) THEN
                               if (j < 4096) then
                                   subrange_end := (to_hex(i)::text || rpad(lpad(to_hex(j), 3, '0'), 7, '0') ||
                                                    '-0000-0000-0000-000000000000')::UUID;
                               else
                                   subrange_end := (rpad(to_hex(i + 1), 8, '0') || '-0000-0000-0000-000000000000')::UUID;
                               end if;
                           ELSE
                               subrange_end := 'ffffffff-ffff-ffff-ffff-ffffffffffff'::UUID; -- upper bound for last subrange in last partition
                           END IF;
   
                           INSERT INTO public.uuid_range (partition, subrange_start, subrange_end)
                           VALUES (partition_start, subrange_start, subrange_end);
   
                           subrange_start := subrange_end;
                       END LOOP;
               END LOOP;
       END;
   $$
   LANGUAGE plpgsql; 

3. Create/replace function to recalculate effective values and shelving order in items

   SET search_path = <tenant>_mod_inventory_storage;
   
   CREATE OR REPLACE FUNCTION set_item_effective_values(holding jsonb, item jsonb)
     RETURNS jsonb AS $$
     BEGIN
       RETURN set_effective_shelving_order(
             item
             || jsonb_build_object('effectiveLocationId',
                  COALESCE(item->>'temporaryLocationId', item->>'permanentLocationId', holding->>'effectiveLocationId'))
             || effective_call_number_components(holding, item));
     END;
   $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

4. Update step should be executed for each partition created from step #2. Update script can be executed concurrently.

    Expand source

   set search_path = "{tenant}_mod_inventory_storage", "public";
   
   do
   $$
       declare
           partition_to_process uuid := '{partition}'::uuid;
           range_rec           public.uuid_range%rowtype;
       begin
           for range_rec in select ur.*
                            from public.uuid_range ur
                            where ur.partition = partition_to_process
                              and ur.completed = false
               loop
                   --             start transaction;
                   UPDATE item i SET jsonb = set_item_effective_values(item_data.holdings, item_data.item)
   				FROM (
   					select item.id, hr.jsonb as holdings, item.jsonb as item from holdings_record hr
   					join item on item.holdingsRecordId = hr.id
                   	where item.id between range_rec.subrange_start and range_rec.subrange_end
                   	and item.jsonb->'effectiveCallNumberComponents'->'callNumber' IS NOT NULL
   				) AS item_data
   				WHERE i.id = item_data.id;
    
                   update uuid_range
                   set completed = true
                   where id = range_rec.id;
                   --
                   raise notice 'subrange id: % completed', range_rec.id;
                   commit;
               end loop;
       end
   $$;

5. Monitor how many sub-partitions in partitions are left to be processed

    Expand source

   select partition, count(partition)
   from public.uuid_range
   where completed = false
   group by partition
   order by partition;  

6. Enable triggers

    Expand source

   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER audit_item;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER check_statistical_code_references_on_insert;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER check_statistical_code_references_on_update;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER set_id_in_jsonb;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER set_item_md_json_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER set_item_md_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER set_item_ol_version_trigger;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER update_item_references;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER update_item_status_date;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER updatecompleteupdateddate_item_delete;
   
   ALTER TABLE {tenant}_mod_inventory_storage.item ENABLE TRIGGER updatecompleteupdateddate_item_insert_update;