Script to remove child and parent elements from data import profiles

Owned by Kateryna Senchenko
Last updated: Feb 03, 2023
1 min read

MODDICONV-293 - Getting issue details... STATUS

MODDICONV-251 - Getting issue details... STATUS

MODSOURMAN-943 - Getting issue details... STATUS

UIDATIMP-1369 - Getting issue details... STATUS

Some profiles contain filled parentProfiles and childProfiles, which should be empty. These fields are filled only when constructing a response for the UI. In case profile json stores these values, they are not only redundant, but cause profile to be too heavy during processing. It may lead to slow performance of mod-data-import-converter-storage (mod-di-converter-storage since Orchid), OOM errors in mod-source-record-manager, and errors from Kafka. Such profiles need to be corrected and profile snapshots should be deleted from job executions that are completed. Run the following queries:

UPDATE {TENANT_ID}_mod_data_import_converter_storage.match_profiles
SET "jsonb" = jsonb_set("jsonb", '{childProfiles}', '[]')

UPDATE {TENANT_ID}_mod_data_import_converter_storage.match_profiles
SET "jsonb" = jsonb_set("jsonb", '{parentProfiles}', '[]')

UPDATE {TENANT_ID}_mod_data_import_converter_storage.action_profiles
SET "jsonb" = jsonb_set("jsonb", '{childProfiles}', '[]')

UPDATE {TENANT_ID}_mod_data_import_converter_storage.action_profiles
SET "jsonb" = jsonb_set("jsonb", '{parentProfiles}', '[]') 

UPDATE {TENANT_ID}_mod_data_import_converter_storage.job_profiles
SET "jsonb" = jsonb_set("jsonb", '{childProfiles}', '[]')

UPDATE {TENANT_ID}_mod_data_import_converter_storage.job_profiles
SET "jsonb" = jsonb_set("jsonb", '{parentProfiles}', '[]') 

UPDATE {TENANT_ID}_mod_data_import_converter_storage.mapping_profiles
SET "jsonb" = jsonb_set("jsonb", '{childProfiles}', '[]')

UPDATE {TENANT_ID}_mod_data_import_converter_storage.mapping_profiles
SET "jsonb" = jsonb_set("jsonb", '{parentProfiles}', '[]')

UPDATE {TENANT_ID}_mod_source_record_manager.job_execution 
SET job_profile_snapshot_wrapper = null WHERE status IN ('COMMITTED', 'ERROR', 'CANCELLED')

Deleting data from {TENANT_ID}_mod_data_import_converter_storage.profile_snapshots might also be required.