<commit_before>require "heroku/command/base"
require File.expand_path('lib/heroku/command/pgbackups', File.dirname(__FILE__))

class Heroku::Command::Pg < Heroku::Command::Base

  # pg:cache_hit [DATABASE]
  #
  # calculates your cache hit rate (effective databases are at 99% and up)
  #
  def cache_hit
    sql = %q(
      SELECT
        'index hit rate' as name,
        (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio
      FROM pg_statio_user_indexes
      union all
      SELECT
       'cache hit rate' as name,
        sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
      FROM pg_statio_user_tables;)

    puts exec_sql(sql)
  end

  def cachehit
    puts "WARNING: pg:cachehit is deprecated. Use pg:cache_hit instead"
    cache_hit
  end

  # pg:index_usage [DATABASE]
  #
  # calculates your index hit rate (effective databases are at 99% and up)
  #
  def index_usage
  sql = %q(SELECT
         relname,
         CASE idx_scan
           WHEN 0 THEN 'Insufficient data'
           ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
         END percent_of_times_index_used,
         n_live_tup rows_in_table
       FROM
         pg_stat_user_tables
       ORDER BY
         n_live_tup DESC;)
    puts exec_sql(sql)
  end

  def indexusage
    puts "WARNING: pg:indexusage is deprecated. Use pg:index_usage instead"
    index_usage
  end

  # pg:blocking [DATABASE]
  #
  # display queries holding locks other queries are waiting to be released
  #
  def blocking
    sql = %Q(
      select bl.pid as blocked_pid,
        ka.#{query_column} as blocking_statement,
        now() - ka.query_start as blocking_duration,
        kl.pid as blocking_pid,
        a.#{query_column} as blocked_statement,
        now() - a.query_start as blocked_duration
 from pg_catalog.pg_locks bl
      join pg_catalog.pg_stat_activity a
      on bl.pid = a.#{pid_column}
      join pg_catalog.pg_locks kl
           join pg_catalog.pg_stat_activity ka
           on kl.pid = ka.#{pid_column}
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 where not bl.granted)

   puts exec_sql(sql)
  end

  # pg:locks [DATABASE]
  #
  # display queries with active locks
  #
  def locks
    sql = %Q(
   select
     pg_stat_activity.#{pid_column},
     pg_class.relname,
     pg_locks.transactionid,
     pg_locks.granted,
     substr(pg_stat_activity.#{query_column},1,30) as query_snippet,
     age(now(),pg_stat_activity.query_start) as "age"
   from pg_stat_activity,pg_locks left
     outer join pg_class on (pg_locks.relation = pg_class.oid)
   where pg_stat_activity.#{query_column} <> '<insufficient privilege>' and
      pg_locks.pid=pg_stat_activity.#{pid_column} and pg_locks.mode = 'ExclusiveLock' order by query_start)

   puts exec_sql(sql)
  end

  # pg:ps [DATABASE]
  #
  # view active queries with execution time
  #
  def ps
    sql = %Q(
    select
      #{pid_column},
      application_name as source,
      age(now(),query_start) as running_for,
      waiting,
      #{query_column} as query
   from pg_stat_activity
   where
     #{query_column} <> '<insufficient privilege>'
     #{
        if nine_two?
          "AND state <> 'idle'"
        else
          "AND current_query <> '<IDLE>'"
        end
     }
     and #{pid_column} <> pg_backend_pid()
   order by 3 desc
   )

    puts exec_sql(sql)
  end

  # pg:kill procpid [DATABASE]
  #
  # kill a query
  #
  # -f,--force  # terminates the connection in addition to cancelling the query
  #
  def kill
    procpid = shift_argument
    output_with_bang "procpid to kill is required" unless procpid && procpid.to_i != 0
    procpid = procpid.to_i

    cmd = options[:force] ? 'pg_terminate_backend' : 'pg_cancel_backend'
    sql = %Q(select #{cmd}(#{procpid});)

    puts exec_sql(sql)
  end

  # pg:killall [DATABASE]
  #
  # terminates ALL connections
  #
  def killall
    sql = %Q(
      select pg_terminate_backend(#{pid_column})
      from pg_stat_activity
      where #{pid_column} <> pg_backend_pid()
      and #{query_column} <> '<insufficient privilege>'
    )

    puts exec_sql(sql)
  end

  # pg:mandelbrot [DATABASE]
  #
  # show the mandelbrot set
  #
  def mandelbrot
    sql = %q(WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
                SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
                FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix),
                     (select -1.5 + 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy)
                UNION ALL
                SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1
                FROM Z
                WHERE X * X + Y * Y < 16::float
                AND I < 100
          )
    SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1)),'')
    FROM (
          SELECT IX, IY, MAX(I) AS I
          FROM Z
          GROUP BY IY, IX
          ORDER BY IY, IX
         ) AS ZT
    GROUP BY IY
    ORDER BY IY
)

    puts exec_sql(sql)
  end

  # pg:total_index_size [DATABASE]
  #
  # show the total size of the indexes in MB
  #
  def total_index_size
    sql = %q(
      SELECT pg_size_pretty(sum(relpages*1024)) AS size
      FROM pg_class
      WHERE reltype=0;)

    puts exec_sql(sql)
  end

  # pg:index_size [DATABASE]
  #
  # show the size of the indexes, descending by size
  #
  def index_size
    sql = %q(
      SELECT relname AS name,
        pg_size_pretty(sum(relpages*1024)) AS size
      FROM pg_class
      WHERE reltype=0
      GROUP BY relname
      ORDER BY sum(relpages) DESC;)

    puts exec_sql(sql)
  end

  # pg:unused_indexes [DATABASE]
  #
  # Show unused and almost unused indexes, ordered by their size relative to
  # the number of index scans. Exclude indexes of very small tables (less than
  # 5 pages), where the planner will almost invariably select a sequential
  # scan, but may not in the future as the table grows.
  #
  def unused_indexes
    sql = %q(
	  SELECT
	    schemaname || '.' || relname AS table,
	    indexrelname AS index,
	    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
	    idx_scan as index_scans
	  FROM pg_stat_user_indexes ui
	  JOIN pg_index i ON ui.indexrelid = i.indexrelid
	  WHERE NOT indisunique AND idx_scan < 50 AND (pg_relation_size(relid) / 1024 ) > 5
	  ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,<commit_msg>Fix thinkos in page calculations<commit_after>require "heroku/command/base"
require File.expand_path('lib/heroku/command/pgbackups', File.dirname(__FILE__))

class Heroku::Command::Pg < Heroku::Command::Base

  # pg:cache_hit [DATABASE]
  #
  # calculates your cache hit rate (effective databases are at 99% and up)
  #
  def cache_hit
    sql = %q(
      SELECT
        'index hit rate' as name,
        (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio
      FROM pg_statio_user_indexes
      union all
      SELECT
       'cache hit rate' as name,
        sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
      FROM pg_statio_user_tables;)

    puts exec_sql(sql)
  end

  def cachehit
    puts "WARNING: pg:cachehit is deprecated. Use pg:cache_hit instead"
    cache_hit
  end

  # pg:index_usage [DATABASE]
  #
  # calculates your index hit rate (effective databases are at 99% and up)
  #
  def index_usage
  sql = %q(SELECT
         relname,
         CASE idx_scan
           WHEN 0 THEN 'Insufficient data'
           ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
         END percent_of_times_index_used,
         n_live_tup rows_in_table
       FROM
         pg_stat_user_tables
       ORDER BY
         n_live_tup DESC;)
    puts exec_sql(sql)
  end

  def indexusage
    puts "WARNING: pg:indexusage is deprecated. Use pg:index_usage instead"
    index_usage
  end

  # pg:blocking [DATABASE]
  #
  # display queries holding locks other queries are waiting to be released
  #
  def blocking
    sql = %Q(
      select bl.pid as blocked_pid,
        ka.#{query_column} as blocking_statement,
        now() - ka.query_start as blocking_duration,
        kl.pid as blocking_pid,
        a.#{query_column} as blocked_statement,
        now() - a.query_start as blocked_duration
 from pg_catalog.pg_locks bl
      join pg_catalog.pg_stat_activity a
      on bl.pid = a.#{pid_column}
      join pg_catalog.pg_locks kl
           join pg_catalog.pg_stat_activity ka
           on kl.pid = ka.#{pid_column}
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 where not bl.granted)

   puts exec_sql(sql)
  end

  # pg:locks [DATABASE]
  #
  # display queries with active locks
  #
  def locks
    sql = %Q(
   select
     pg_stat_activity.#{pid_column},
     pg_class.relname,
     pg_locks.transactionid,
     pg_locks.granted,
     substr(pg_stat_activity.#{query_column},1,30) as query_snippet,
     age(now(),pg_stat_activity.query_start) as "age"
   from pg_stat_activity,pg_locks left
     outer join pg_class on (pg_locks.relation = pg_class.oid)
   where pg_stat_activity.#{query_column} <> '<insufficient privilege>' and
      pg_locks.pid=pg_stat_activity.#{pid_column} and pg_locks.mode = 'ExclusiveLock' order by query_start)

   puts exec_sql(sql)
  end

  # pg:ps [DATABASE]
  #
  # view active queries with execution time
  #
  def ps
    sql = %Q(
    select
      #{pid_column},
      application_name as source,
      age(now(),query_start) as running_for,
      waiting,
      #{query_column} as query
   from pg_stat_activity
   where
     #{query_column} <> '<insufficient privilege>'
     #{
        if nine_two?
          "AND state <> 'idle'"
        else
          "AND current_query <> '<IDLE>'"
        end
     }
     and #{pid_column} <> pg_backend_pid()
   order by 3 desc
   )

    puts exec_sql(sql)
  end

  # pg:kill procpid [DATABASE]
  #
  # kill a query
  #
  # -f,--force  # terminates the connection in addition to cancelling the query
  #
  def kill
    procpid = shift_argument
    output_with_bang "procpid to kill is required" unless procpid && procpid.to_i != 0
    procpid = procpid.to_i

    cmd = options[:force] ? 'pg_terminate_backend' : 'pg_cancel_backend'
    sql = %Q(select #{cmd}(#{procpid});)

    puts exec_sql(sql)
  end

  # pg:killall [DATABASE]
  #
  # terminates ALL connections
  #
  def killall
    sql = %Q(
      select pg_terminate_backend(#{pid_column})
      from pg_stat_activity
      where #{pid_column} <> pg_backend_pid()
      and #{query_column} <> '<insufficient privilege>'
    )

    puts exec_sql(sql)
  end

  # pg:mandelbrot [DATABASE]
  #
  # show the mandelbrot set
  #
  def mandelbrot
    sql = %q(WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
                SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
                FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix),
                     (select -1.5 + 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy)
                UNION ALL
                SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1
                FROM Z
                WHERE X * X + Y * Y < 16::float
                AND I < 100
          )
    SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1)),'')
    FROM (
          SELECT IX, IY, MAX(I) AS I
          FROM Z
          GROUP BY IY, IX
          ORDER BY IY, IX
         ) AS ZT
    GROUP BY IY
    ORDER BY IY
)

    puts exec_sql(sql)
  end

  # pg:total_index_size [DATABASE]
  #
  # show the total size of the indexes in MB
  #
  def total_index_size
    sql = %q(
      SELECT pg_size_pretty(sum(relpages*8192)) AS size
      FROM pg_class
      WHERE reltype=0;)

    puts exec_sql(sql)
  end

  # pg:index_size [DATABASE]
  #
  # show the size of the indexes, descending by size
  #
  def index_size
    sql = %q(
      SELECT relname AS name,
        pg_size_pretty(sum(relpages*8192)) AS size
      FROM pg_class
      WHERE reltype=0
      GROUP BY relname
      ORDER BY sum(relpages) DESC;)

    puts exec_sql(sql)
  end

  # pg:unused_indexes [DATABASE]
  #
  # Show unused and almost unused indexes, ordered by their size relative to
  # the number of index scans. Exclude indexes of very small tables (less than
  # 5 pages), where the planner will almost invariably select a sequential
  # scan, but may not in the future as the table grows.
  #
  def unused_indexes
    sql = %q(
	  SELECT
	    schemaname || '.' || relname AS table,
	    indexrelname AS index,
	    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
	    idx_scan as index_scans
	  FROM pg_stat_user_indexes ui
	  JOIN pg_index i ON ui.indexrelid = i.indexrelid
	  WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
	  ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
{'fraction_non_alphanumeric': 0.09559164733178654, 'fraction_numerical': 0.009899458623356535, 'mean_word_length': 2.512904102146156, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '838184', 'n_tokens_mistral': 4688, 'n_tokens_neox': 4430, 'n_words': 1249}