ps.ts

import {Command, flags} from '@heroku-cli/command'
import {utils} from '@heroku/heroku-cli-util'
import {Args} from '@oclif/core'
import tsheredoc from 'tsheredoc'

import {nls} from '../../nls.js'

const heredoc = tsheredoc.default

export default class Ps extends Command {
  static args = {
    database: Args.string({description: `${nls('pg:database:arg:description')} ${nls('pg:database:arg:description:default:suffix')}`}),
  }
  static description = 'view active queries with execution time'
  static flags = {
    app: flags.app({required: true}),
    remote: flags.remote(),
    verbose: flags.boolean({char: 'v'}),
  }
  static topic = 'pg'

  public async run(): Promise<void> {
    const {args, flags} = await this.parse(Ps)
    const {database: databaseName} = args
    const {app, verbose} = flags
    const dbResolver = new utils.pg.DatabaseResolver(this.heroku)
    const db = await dbResolver.getDatabase(app, databaseName)
    const psqlService = new utils.pg.PsqlService(db)
    const num = Math.random()
    const waitingMarker = `${num}${num}`
    const waitingQuery = heredoc(`
    SELECT '${num}' || '${num}'
    WHERE EXISTS
        (SELECT 1
         FROM information_schema.columns
         WHERE table_schema = 'pg_catalog'
           AND TABLE_NAME = 'pg_stat_activity'
           AND COLUMN_NAME = 'waiting')
    `)
    const waitingOutput = await psqlService.execQuery(waitingQuery)
    const waiting = waitingOutput.includes(waitingMarker) ? 'waiting' : 'wait_event IS NOT NULL AS waiting'
    const query = heredoc(`SELECT pid,
           state,
           application_name AS SOURCE,
           usename AS username,
           age(now(), xact_start) AS running_for,
           xact_start AS transaction_start, ${waiting}, query
    FROM pg_stat_activity
    WHERE query <> '<insufficient privilege>'${verbose ? '' : " AND state <> 'idle'"}
      AND pid <> pg_backend_pid()
      AND NOT (
        state = 'idle in transaction'
        AND usename = 'postgres'
        AND query LIKE '%pg_backup_start%'
      )
    ORDER BY query_start DESC
    `)
    const output = await psqlService.execQuery(query)
    process.stdout.write(output)
  }
}