windowed_job_readiness_query.sql

SELECT
	rank_filter.*
FROM
	(
		SELECT
			job.id AS job_id,
			job.create_time AS job_create_time,
			job.update_time AS job_update_time,
			job.history_id AS job_history_id,
			job.library_folder_id AS job_library_folder_id,
			job.tool_id AS job_tool_id,
			job.tool_version AS job_tool_version,
			job.galaxy_version AS job_galaxy_version,
			job.dynamic_tool_id AS job_dynamic_tool_id,
			job.state AS job_state,
			job.info AS job_info,
			job.copied_from_job_id AS job_copied_from_job_id,
			job.command_line AS job_command_line,
			job.dependencies AS job_dependencies,
			job.job_messages AS job_job_messages,
			job.param_filename AS job_param_filename,
			job.runner_name AS job_runner_name_1,
			job.job_stdout AS job_job_stdout,
			job.job_stderr AS job_job_stderr,
			job.tool_stdout AS job_tool_stdout,
			job.tool_stderr AS job_tool_stderr,
			job.exit_code AS job_exit_code,
			job.traceback AS job_traceback,
			job.session_id AS job_session_id,
			job.user_id AS job_user_id,
			job.job_runner_name AS job_job_runner_name,
			job.job_runner_external_id AS job_job_runner_external_id,
			job.destination_id AS job_destination_id,
			job.destination_params AS job_destination_params,
			job.object_store_id AS job_object_store_id,
			job.imported AS job_imported,
			job.params AS job_params,
			job.handler AS job_handler,
			rank() OVER (PARTITION BY job.user_id ORDER BY job.id)
		FROM
			job LEFT JOIN galaxy_user ON galaxy_user.id = job.user_id
		WHERE
			job.state = 'new'
			AND (job.user_id IS NULL OR galaxy_user.active = true)
			AND job.handler = 'main_w3_handler2'
			AND job.id
				NOT IN (
						SELECT
							job.id
						FROM
							job
							JOIN job_to_input_dataset ON
									job.id
									= job_to_input_dataset.job_id
							JOIN history_dataset_association ON
									history_dataset_association.id
									= job_to_input_dataset.dataset_id
							JOIN dataset ON
									dataset.id
									= history_dataset_association.dataset_id
						WHERE
							job.state = 'new'
							AND dataset.state
								IN (
										'new',
										'upload',
										'queued',
										'running',
										'setting_metadata'
									)
					)
			AND job.id
				NOT IN (
						SELECT
							job.id
						FROM
							job
							JOIN job_to_input_library_dataset ON
									job.id
									= job_to_input_library_dataset.job_id
							JOIN library_dataset_dataset_association ON
									library_dataset_dataset_association.id
									= job_to_input_library_dataset.ldda_id
							JOIN dataset ON
									dataset.id
									= library_dataset_dataset_association.dataset_id
						WHERE
							job.state = 'new'
							AND dataset.state
								IN (
										'new',
										'upload',
										'queued',
										'running',
										'setting_metadata'
									)
					)
		ORDER BY
			job.id
	)
		AS rank_filter
WHERE
	rank <= 20;