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:

Count impacted hodlings

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:

Action 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)

Browser not supported