How can I selectively remove/delete jobs from the Columbus cluster job status page?

How can I selectively remove/delete jobs from the Columbus cluster job status page?

The cluster_jobstatus table can be found in the columbus_webapp database.

Login to the Columbus server via ssh i.e. using PuTTY or Terminal.

If logged in as the root user, switch to the 'columbus' account:

$ su columbus

Switch to the columbus_webapp database:

$ psql columbus_webapp

Welcome to psql 8.3.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

Initially you might just want to see the most recent jobs stored in the cluster_jobstatus table. In the below example the 5 most recent jobs are listed:

columbus_webapp=> select id,state,created from cluster_jobstatus order by id desc limit 5;

id  | state |            created

-----+-------+-------------------------------

344 | UN    | 2018-04-24 20:23:24.610583-04

343 | UN    | 2018-04-24 19:40:50.602429-04

342 | FA    | 2018-02-15 16:08:31.875577-05

341 | CO    | 2018-02-15 14:45:13.530119-05

340 | CO    | 2018-02-15 13:53:14.780292-05

The IDs listed in the 'id' column are the same as those that you see on the Cluster Job Status page.

To list all jobs with unknown state:

columbus_webapp=> select id,state,created from cluster_jobstatus where state='UN';

id  | state |            created

-----+-------+-------------------------------

343 | UN    | 2018-04-24 19:40:50.602429-04

344 | UN    | 2018-04-24 20:23:24.610583-04

Now, to selectively remove job status entries, you just need to add a WHERE qualifier to the delete command, eg:

columbus_webapp=> delete from cluster_jobstatus where state='UN';

which removes all jobs that have unknown state.

Other examples of job 'states' include:

CO - Completed

FA - Failed

UN - Unknown

PE - Pending

IM - Importing

SU - Submitting

You could also remove individual jobs by their id. In the below example we first list the job with id 343 followed by the command to delete the entry for id 343.

columbus_webapp=> select id,state,created from cluster_jobstatus where id=343;

columbus_webapp=> delete from cluster_jobstatus where id=343;

You can also delete entries in a range e.g. using the  > and < symbol.

The below command will delete all entries greater than id 343

columbus_webapp=> delete from cluster_jobstatus where id>343;