<commit_before>package state

//
// DefinitionSelect postgres specific query for definitions
//
const DefinitionSelect = `
select coalesce(td.arn, '')                as arn,
       td.definition_id                    as definitionid,
       td.adaptive_resource_allocation     as adaptiveresourceallocation,
       td.image                            as image,
       td.group_name                       as groupname,
       td.container_name                   as containername,
       coalesce(td.user, '')               as "user",
       td.alias                            as alias,
       td.memory                           as memory,
       coalesce(td.command, '')            as command,
       coalesce(td.task_type, '')          as tasktype,
       env::TEXT                           as env,
       td.privileged                       as privileged,
       td.cpu                              as cpu,
       td.gpu                              as gpu,
       array_to_json('{""}'::TEXT[])::TEXT as tags,
       array_to_json('{}'::INT[])::TEXT    as ports
from (select * from task_def) td
`

//
// ListDefinitionsSQL postgres specific query for listing definitions
//
const ListDefinitionsSQL = DefinitionSelect + "\n%s %s limit $1 offset $2"

//
// GetDefinitionSQL postgres specific query for getting a single definition
//
const GetDefinitionSQL = DefinitionSelect + "\nwhere definition_id = $1"

//
// GetDefinitionByAliasSQL get definition by alias
//
const GetDefinitionByAliasSQL = DefinitionSelect + "\nwhere alias = $1"

const TaskResourcesSelectCommandSQL = `
SELECT cast((percentile_disc(0.99) within GROUP (ORDER BY A.max_memory_used)) * 1.75 as int) as memory,
       cast((percentile_disc(0.99) within GROUP (ORDER BY A.max_cpu_used)) * 1.25  as int)  as cpu
FROM (SELECT CASE WHEN exit_code = 137 THEN memory * 2 ELSE max_memory_used END as max_memory_used, max_cpu_used
      FROM TASK
      WHERE
           queued_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
           AND (exit_code = 0 or exit_code = 137)
           AND max_memory_used is not null
           AND max_cpu_used is not null
           AND engine = 'eks'
           AND definition_id = $1
           AND command_hash = (SELECT command_hash FROM task WHERE run_id = $2)
      LIMIT 30) A

`
const TaskExecutionRuntimeCommandSQL = `
SELECT percentile_disc(0.95) within GROUP (ORDER BY A.minutes) as minutes
FROM (SELECT EXTRACT(epoch from finished_at - started_at) / 60 as minutes
      FROM TASK
      WHERE definition_id = $1
        AND exit_code = 0
        AND engine = 'eks'
        AND queued_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
        AND command_hash = (SELECT command_hash FROM task WHERE run_id = $2)
      LIMIT 30) A
`

const ListFailingNodesSQL = `
SELECT instance_dns_name
      FROM TASK
      WHERE (exit_code = 128 OR
            pod_events @> '[{"reason": "Failed"}]' OR
            pod_events @> '[{"reason": "FailedSync"}]' OR
            pod_events @> '[{"reason": "OutOfmemory"}]' OR
            pod_events @> '[{"reason": "FailedCreatePodSandBox"}]' OR
            (exit_code = 1 AND exit_reason is null))
           AND engine = 'eks'
           AND queued_at >= NOW() - INTERVAL '12 HOURS'
      GROUP BY 1<commit_msg>modify list failing nodes query to isolate nodes that may have had jobs timing out (#369)<commit_after>package state

//
// DefinitionSelect postgres specific query for definitions
//
const DefinitionSelect = `
select coalesce(td.arn, '')                as arn,
       td.definition_id                    as definitionid,
       td.adaptive_resource_allocation     as adaptiveresourceallocation,
       td.image                            as image,
       td.group_name                       as groupname,
       td.container_name                   as containername,
       coalesce(td.user, '')               as "user",
       td.alias                            as alias,
       td.memory                           as memory,
       coalesce(td.command, '')            as command,
       coalesce(td.task_type, '')          as tasktype,
       env::TEXT                           as env,
       td.privileged                       as privileged,
       td.cpu                              as cpu,
       td.gpu                              as gpu,
       array_to_json('{""}'::TEXT[])::TEXT as tags,
       array_to_json('{}'::INT[])::TEXT    as ports
from (select * from task_def) td
`

//
// ListDefinitionsSQL postgres specific query for listing definitions
//
const ListDefinitionsSQL = DefinitionSelect + "\n%s %s limit $1 offset $2"

//
// GetDefinitionSQL postgres specific query for getting a single definition
//
const GetDefinitionSQL = DefinitionSelect + "\nwhere definition_id = $1"

//
// GetDefinitionByAliasSQL get definition by alias
//
const GetDefinitionByAliasSQL = DefinitionSelect + "\nwhere alias = $1"

const TaskResourcesSelectCommandSQL = `
SELECT cast((percentile_disc(0.99) within GROUP (ORDER BY A.max_memory_used)) * 1.75 as int) as memory,
       cast((percentile_disc(0.99) within GROUP (ORDER BY A.max_cpu_used)) * 1.25  as int)  as cpu
FROM (SELECT CASE WHEN exit_code = 137 THEN memory * 2 ELSE max_memory_used END as max_memory_used, max_cpu_used
      FROM TASK
      WHERE
           queued_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
           AND (exit_code = 0 or exit_code = 137)
           AND max_memory_used is not null
           AND max_cpu_used is not null
           AND engine = 'eks'
           AND definition_id = $1
           AND command_hash = (SELECT command_hash FROM task WHERE run_id = $2)
      LIMIT 30) A

`
const TaskExecutionRuntimeCommandSQL = `
SELECT percentile_disc(0.95) within GROUP (ORDER BY A.minutes) as minutes
FROM (SELECT EXTRACT(epoch from finished_at - started_at) / 60 as minutes
      FROM TASK
      WHERE definition_id = $1
        AND exit_code = 0
        AND engine = 'eks'
        AND queued_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
        AND command_hash = (SELECT command_hash FROM task WHERE run_id = $2)
      LIMIT 30) A
`

const ListFailingNodesSQL = `
WITH control_plane_errors AS
         (
             SELECT instance_dns_name
             FROM TASK
             WHERE (exit_code = 128 OR
                    pod_events @> '[{"reason": "Failed"}]' OR
                    pod_events @> '[{"reason": "FailedSync"}]' OR
                    pod_events @> '[{"reason": "OutOfmemory"}]' OR
                    pod_events @> '[{"reason": "FailedCreatePodSandBox"}]' OR
                    (exit_code = 1 AND exit_reason is null))
               AND engine = 'eks'
               AND queued_at >= NOW() - INTERVAL '12 HOURS'
             GROUP BY 1
         ),
     timeouts AS
         (
             SELECT instance_dns_name, count(*) as c
             FROM TASK
             WHERE exit_reason like 'Task terminated by - %'
               AND engine = 'eks'
               AND queued_at >= NOW() - INTERVAL '12 HOURS'
             GROUP BY 1
         )
SELECT
   instance_dns_name
FROM
   control_plane_errors
UNION ALL
SELECT
   instance_dns_name
FROM
   timeouts
WHERE
   c > 5
{'fraction_non_alphanumeric': 0.08495997753124561, 'fraction_numerical': 0.014183401207695548, 'mean_word_length': 1.9128834355828221, 'pattern_counts': {'":': 8, '<': 3, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '615905', 'n_tokens_mistral': 2109, 'n_tokens_neox': 1954, 'n_words': 625}