Quesnelia (R1 2024): Holdings migration
Pavlo Smahin
Kalibek Turgumbayev
Context
MODINVSTOR-1094 - Getting issue details... STATUS
Important!
The manual migration was included in module migrations in MODINVSTOR-1189 - Getting issue details... STATUS . The instructions for manual script only apply if there are more than 3M affected holding records in the tenant database. If there are more affected records, then the manual script should be applied first and module should be enabled after script is done.
To check the amount of affected records one can use the following script:
do $$ declare tenant varchar; count_all int; count int; begin for tenant in select tenantjson -> 'descriptor' ->> 'id' from public.tenants where tenantjson -> 'descriptor' ->> 'id' <> 'supertenant' -- and tenantjson -> 'descriptor' ->> 'id' in ( -- 'college' -- ) loop execute format($count$ SELECT count(*) from %s_mod_inventory_storage.holdings_record $count$, tenant) into count_all; execute format($count$ SELECT count(*) from %s_mod_inventory_storage.holdings_record WHERE jsonb ? 'permanentLocation' OR jsonb ? 'illPolicy' OR jsonb ? 'holdingsItems' OR jsonb ? 'bareHoldingsItems' OR jsonb ? 'holdingsInstance' $count$, tenant) into count; raise info 'tenant: % - % of %', tenant, count, count_all; end loop; end $$ language plpgsql;
Instructions for a single-tenant environment
Step 1
Run time:
Before or after upgrade
Action:
Step 1.
Run script:
ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER audit_holdings_record; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER set_holdings_record_md_json_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER set_holdings_record_md_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER set_holdings_record_ol_version_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER set_id_in_jsonb; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER update_holdings_record_references; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER updatecompleteupdateddate_holdings_record_delete; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record DISABLE TRIGGER updatecompleteupdateddate_holdings_record_insert_update; UPDATE {tenant_name}_mod_inventory_storage.holdings_record SET jsonb=jsonb - 'permanentLocation' - 'illPolicy' - 'holdingsItems' - 'bareHoldingsItems' - 'holdingsInstance' WHERE jsonb ? 'permanentLocation' OR jsonb ? 'illPolicy' OR jsonb ? 'holdingsItems' OR jsonb ? 'bareHoldingsItems' OR jsonb ? 'holdingsInstance'; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER audit_holdings_record; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER set_holdings_record_md_json_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER set_holdings_record_md_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER set_holdings_record_ol_version_trigger; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER set_id_in_jsonb; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER update_holdings_record_references; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER updatecompleteupdateddate_holdings_record_delete; ALTER TABLE {tenant_name}_mod_inventory_storage.holdings_record ENABLE TRIGGER updatecompleteupdateddate_holdings_record_insert_update;
Action timing:
TBD (will be tested in scope of bugfest preparation)
Instructions for an environment with multiple tenants:
Runtime:
Before or after upgrade
Action:
The script implies that the current user has access to tenant schemas and to public
and information_schema
. To limit the script only to a part of the tenants one can uncomment conditions in the tenant selection part of the script. The update is split to id ranges to allow progress tracking.
do $$ declare tenant varchar; trigger varchar; triggers varchar[] default array [ 'audit_holdings_record', 'set_holdings_record_md_json_trigger', 'set_holdings_record_md_trigger', 'set_holdings_record_ol_version_trigger', 'set_id_in_jsonb', 'update_holdings_record_references', 'updatecompleteupdateddate_holdings_record_delete', 'updatecompleteupdateddate_holdings_record_insert_update' ]; arr uuid[] default array [ '11111111-0000-0000-0000-000000000000', '22222222-0000-0000-0000-000000000000', '33333333-0000-0000-0000-000000000000', '44444444-0000-0000-0000-000000000000', '55555555-0000-0000-0000-000000000000', '66666666-0000-0000-0000-000000000000', '77777777-0000-0000-0000-000000000000', '88888888-0000-0000-0000-000000000000', '99999999-0000-0000-0000-000000000000', 'aaaaaaaa-0000-0000-0000-000000000000', 'bbbbbbbb-0000-0000-0000-000000000000', 'cccccccc-0000-0000-0000-000000000000', 'dddddddd-0000-0000-0000-000000000000', 'ffffffff-0000-0000-0000-000000000000', 'ffffffff-ffff-ffff-ffff-ffffffffffff' ]; lower uuid; cur uuid; rowcount bigint; need_migration boolean; holdings_table_exist boolean; begin for tenant in select tenantjson -> 'descriptor' ->> 'id' from public.tenants where tenantjson -> 'descriptor' ->> 'id' <> 'supertenant' -- and tenantjson -> 'descriptor' ->> 'id' in ( -- 'college' -- ) loop -- apply in each tenant execute format($check$ select exists(SELECT * FROM information_schema.tables WHERE table_schema = '%s_mod_inventory_storage' AND table_name = 'holdings_record'); $check$, tenant) into holdings_table_exist; if holdings_table_exist then begin -- STEP 0 Check if migration is required execute format($check$ select exists(SELECT id from %s_mod_inventory_storage.holdings_record WHERE jsonb ? 'permanentLocation' OR jsonb ? 'illPolicy' OR jsonb ? 'holdingsItems' OR jsonb ? 'bareHoldingsItems' OR jsonb ? 'holdingsInstance' limit 1); $check$, tenant) into need_migration; if need_migration then begin -- STEP 1 disable triggers foreach trigger in array triggers loop execute 'ALTER TABLE ' || tenant || '_mod_inventory_storage.holdings_record ' || 'DISABLE TRIGGER ' || trigger; end loop; -- STEP 2 do updates lower = '00000000-0000-0000-0000-000000000000'; foreach cur in array arr loop raise info 'range: % - %', lower, cur; -- Update scripts execute format($q$ UPDATE %s_mod_inventory_storage.holdings_record SET jsonb=jsonb - 'permanentLocation' - 'illPolicy' - 'holdingsItems' - 'bareHoldingsItems' - 'holdingsInstance' WHERE (jsonb ? 'permanentLocation' OR jsonb ? 'illPolicy' OR jsonb ? 'holdingsItems' OR jsonb ? 'bareHoldingsItems' OR jsonb ? 'holdingsInstance') and (id > %L and id <= %L);$q$ , tenant, lower, cur); GET DIAGNOSTICS rowcount = ROW_COUNT; raise info 'updated % records', rowcount; end loop; -- STEP 3 enable triggers foreach trigger in array triggers loop execute 'ALTER TABLE ' || tenant || '_mod_inventory_storage.holdings_record ' || 'ENABLE TRIGGER ' || trigger; end loop; end; else raise info 'tenant % does not require migration', tenant; end if; end; else raise info 'tenant % does not have holdings table', tenant; end if; end loop; end $$
Action Timing:
TBD (will be tested in scope of bugfest preparation)