github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/delegate/show_jobs.go (about)

     1  // Copyright 2017 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package delegate
    12  
    13  import (
    14  	"fmt"
    15  
    16  	"github.com/cockroachdb/cockroach/pkg/jobs/jobspb"
    17  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    18  	"github.com/cockroachdb/cockroach/pkg/sql/sqltelemetry"
    19  )
    20  
    21  func (d *delegator) delegateShowJobs(n *tree.ShowJobs) (tree.Statement, error) {
    22  	sqltelemetry.IncrementShowCounter(sqltelemetry.Jobs)
    23  	const (
    24  		selectClause = `SELECT job_id, job_type, description, statement, user_name, status,
    25  				       running_status, created, started, finished, modified,
    26  				       fraction_completed, error, coordinator_id
    27  				FROM crdb_internal.jobs`
    28  	)
    29  	var typePredicate, whereClause, orderbyClause string
    30  	if n.Jobs == nil {
    31  		// Display all [only automatic] jobs without selecting specific jobs.
    32  		if n.Automatic {
    33  			typePredicate = fmt.Sprintf("job_type = '%s'", jobspb.TypeAutoCreateStats)
    34  		} else {
    35  			typePredicate = fmt.Sprintf(
    36  				"(job_type IS NULL OR job_type != '%s')", jobspb.TypeAutoCreateStats,
    37  			)
    38  		}
    39  		// The query intends to present:
    40  		// - first all the running jobs sorted in order of start time,
    41  		// - then all completed jobs sorted in order of completion time.
    42  		whereClause = fmt.Sprintf(
    43  			`WHERE %s AND (finished IS NULL OR finished > now() - '12h':::interval)`, typePredicate)
    44  		// The "ORDER BY" clause below exploits the fact that all
    45  		// running jobs have finished = NULL.
    46  		orderbyClause = `ORDER BY COALESCE(finished, now()) DESC, started DESC`
    47  	} else {
    48  		// Limit the jobs displayed to the select statement in n.Jobs.
    49  		whereClause = fmt.Sprintf(`WHERE job_id in (%s)`, n.Jobs.String())
    50  	}
    51  
    52  	sqlStmt := fmt.Sprintf("%s %s %s", selectClause, whereClause, orderbyClause)
    53  	if n.Block {
    54  		sqlStmt = fmt.Sprintf(
    55  			`SELECT * FROM [%s]
    56  			 WHERE
    57  			    IF(finished IS NULL,
    58  			      IF(pg_sleep(1), crdb_internal.force_retry('24h'), 0),
    59  			      0
    60  			    ) = 0`, sqlStmt)
    61  	}
    62  	return parse(sqlStmt)
    63  }