Quesnelia (R1 2024): Holdings migration
Owned by Pavlo Smahin
Context
MODINVSTOR-1094 - Getting issue details... STATUS
Instructions for a single-tenant environment
Step 1
Run time:
Before or after upgrade
Action:
Step 1.
Run script:
Action script Expand source
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.
Expand source
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)