Database performance issue

[bzizou]

When the database grows, some queries are very slow because they are using "!=" for job state.

2024-09-06 11:50:52.797 CEST [71836] oar@oar LOG: duration: 2056.619 ms statement: UPDATE jobs SET state='Launching' WHERE jobs.job_id = 13982 AND jobs.state != 'Error' AND jobs.state != 'Terminated' AND jobs.state != 'Launching'

The problem is in this function:

oar3/oar/lib/job_handling.py

Lines 1658 to 1665 in 5c8e5d1

	def set_job_state(session, config, jid, state):
	result = (
	session.query(Job)
	.filter(Job.id == jid)
	.filter(Job.state != "Error")
	.filter(Job.state != "Terminated")
	.filter(Job.state != state)
	.update({Job.state: state}, synchronize_session=False)

The filter should be constructed with Job.state in ('state1','state2') syntax and this requires to parse the possible job states which are ('Waiting','Hold','toLaunch','toError','toAckReservation','Launching','Running','Suspended','Resuming','Finishing','Terminated','Error')