Page Comparison

...

Step N	Execution time
1	1s
2	1h 10m
3	20m
4	1h 30m
5	1s
6	2s
Total	3h

Step 2 (Option 1). Deprecated(effective from Poppy CSP #3), see Option 3

Run time:

After upgrade, before reindex

...

Tested on database with 9M rows in item table.

Execution time: TBD

Step 2 (Option 2). Deprecated(effective from Poppy CSP #3), see Option 3

Run time:

After upgrade, before reindex

...

Disable triggers

Code Block

language	sql
collapse	true

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;

Create uuid_range table

Code Block

language	sql
collapse	true

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;

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

Code Block

language	sql

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;

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

Code Block

language	sql
collapse	true

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
$$;

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

Code Block

language	sql
collapse	true

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

Enable triggers

Code Block

language	sql
collapse	true

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;

Step 2 (Option 3). Requires Poppy CSP #3

Run time:

After upgrade, before reindex

Precondition:

No preconditions

Action

Run script:

Code Block

language	sql

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

-- Recalculate effective values for items with no call number type specified. No shelving order recalculation
UPDATE item
SET jsonb = set_item_effective_values_no_shelv_recalc(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;

Step 3. Requires Poppy CSP #3

Run async item shelving order migration via api. Anytime, may be during runtime. Values surrounded by "<>" to be replaced

Code Block

language	text

curl --location --request POST '<okapi-url>/inventory-storage/migrations/jobs' \
--header 'X-Okapi-Tenant: <tenant>' \
--header 'X-Okapi-Token: <token>' \
--header 'Content-Type: application/json' \
--data '{
  "migrations": ["itemShelvingOrderMigration"],
  "affectedEntities": ["ITEM"]
}'

Monitor execution until response body parameter "jobStatus" is "Completed". <jobId> is returned from previous request as "id" parameter in response body

Code Block

language	text

curl --location --request GET '<okapi-url>/inventory-storage/migrations/jobs/<jobId>' \
--header 'X-Okapi-Tenant: <tenant>' \
--header 'X-Okapi-Token: <token>' \
--header 'Content-Type: application/json'

Once migration is completed - reindex instances

Versions Compared

Old Version 18

New Version 19

Key

Step 2 (Option 1). Deprecated(effective from Poppy CSP #3), see Option 3

Step 2 (Option 2). Deprecated(effective from Poppy CSP #3), see Option 3

Step 2 (Option 3). Requires Poppy CSP #3

Step 3. Requires Poppy CSP #3