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)