Project

General

Profile

Delete a list of parameters

We got a request today to delete parameters from a SAM instance; Due to foreign key constraints this means you have to delete a bunch of things, once you're sure you have the right list of param_types to clean up.

So first, check your list of param_types; we can have Integer(=1) or String(=5) param types...

Connect to the database for the sam instance; you can mooch the connection parameters and the password from
the samweb config on :config/$EXPERIMENT.conf, in the example below I'm doing gm2.

psql -h cspgsprd2.fnal.gov -p 5438 -d sam_gm2_prd -U samdbs
Enter password for samdbs:
sam_gmw_prd=> 

Next lets check our list of param_types

sam_gm2_pre=> select param_type_id, param_category, param_type , data_type_id
from param_types, param_categories
where param_types.param_category_id = param_categories.param_category_id
  and param_category||'.'||param_type||':'||data_type_id in (
   'mc.particle:1', 'mc.gun:1', 'mc.requestid:1', 'mc.run_number:1', 'mc.subrun_number:1',
   'fhicl.name:1', 'mc.harps:1', 'gm2.fhicl_name:1', 'gm2.harps:1',
   'gm2.application_family:1', 'gm2.file_format_version:1', 'gm2.application_version:1',
   'gm2.process_name:1', 'gm2.run_config:1', 'gm2.file_format_era:1',
   'gm2.run_number:1', 'gm2.application_name:1', 'gm2.subrun_number:1',
   'gm2.requestid:1', 'gm2.sub_run_number:1',
   'gm2.file_format_version:5', 'offline.requestid:5'
);
param_type_id |   param_category     | param_type  | data_type_id
------------------------------------------------------------------
...

check that the list you get back from the database matches the list you have; now we can use a slight variation on this query to pic param_type_id's for the cleanup.

We have 3 levels of tables to clean up;
  • the attachment of files to params (data_files_param_values, num_data_files_param_values)
  • the intermediate string values table (param_values)
  • the actual parameter types (param_types)
delete  from data_files_param_values where param_value_id in (
select param_value_id from param_values where param_type_id in (
  select param_type_id
  from param_types, param_categories
  where param_types.param_category_id = param_categories.param_category_id
    and param_category||'.'||param_type||':'||data_type_id in (
     'mc.particle:1', 'mc.gun:1', 'mc.requestid:1', 'mc.run_number:1', 'mc.subrun_number:1',
     'fhicl.name:1', 'mc.harps:1', 'gm2.fhicl_name:1', 'gm2.harps:1',
     'gm2.application_family:1', 'gm2.file_format_version:1', 'gm2.application_version:1',
     'gm2.process_name:1', 'gm2.run_config:1', 'gm2.file_format_era:1',
     'gm2.run_number:1', 'gm2.application_name:1', 'gm2.subrun_number:1',
     'gm2.requestid:1', 'gm2.sub_run_number:1',
     'gm2.file_format_version:5', 'offline.requestid:5'
  )
 )
);
delete from num_data_files_param_values where param_type_id in (
  select param_type_id
  from param_types, param_categories
  where param_types.param_category_id = param_categories.param_category_id
    and param_category||'.'||param_type||':'||data_type_id in (
     'mc.particle:1', 'mc.gun:1', 'mc.requestid:1', 'mc.run_number:1', 'mc.subrun_number:1',
     'fhicl.name:1', 'mc.harps:1', 'gm2.fhicl_name:1', 'gm2.harps:1',
     'gm2.application_family:1', 'gm2.file_format_version:1', 'gm2.application_version:1',
     'gm2.process_name:1', 'gm2.run_config:1', 'gm2.file_format_era:1',
     'gm2.run_number:1', 'gm2.application_name:1', 'gm2.subrun_number:1',
     'gm2.requestid:1', 'gm2.sub_run_number:1',
     'gm2.file_format_version:5', 'offline.requestid:5'
  )
);
delete  from data_files_param_values where param_value_id in (
select param_value_id from param_values where param_type_id in (
  select param_type_id
  from param_types, param_categories
  where param_types.param_category_id = param_categories.param_category_id
    and param_category||'.'||param_type||':'||data_type_id in (
     'mc.particle:1', 'mc.gun:1', 'mc.requestid:1', 'mc.run_number:1', 'mc.subrun_number:1',
     'fhicl.name:1', 'mc.harps:1', 'gm2.fhicl_name:1', 'gm2.harps:1',
     'gm2.application_family:1', 'gm2.file_format_version:1', 'gm2.application_version:1',
     'gm2.process_name:1', 'gm2.run_config:1', 'gm2.file_format_era:1',
     'gm2.run_number:1', 'gm2.application_name:1', 'gm2.subrun_number:1',
     'gm2.requestid:1', 'gm2.sub_run_number:1',
     'gm2.file_format_version:5', 'offline.requestid:5'
  )
                                                  48,0-1        44%

delete from param types where param_type_id in (
  select param_type_id
  from param_types, param_categories
  where param_types.param_category_id = param_categories.param_category_id
    and param_category||'.'||param_type||':'||data_type_id in (
     'mc.particle:1', 'mc.gun:1', 'mc.requestid:1', 'mc.run_number:1', 'mc.subrun_number:1',
     'fhicl.name:1', 'mc.harps:1', 'gm2.fhicl_name:1', 'gm2.harps:1',
     'gm2.application_family:1', 'gm2.file_format_version:1', 'gm2.application_version:1',
     'gm2.process_name:1', 'gm2.run_config:1', 'gm2.file_format_era:1',
     'gm2.run_number:1', 'gm2.application_name:1', 'gm2.subrun_number:1',
     'gm2.requestid:1', 'gm2.sub_run_number:1',
     'gm2.file_format_version:5', 'offline.requestid:5'
  )
);