Module: PgHero::Methods::Sequences

Included in:
Database
Defined in:
lib/pghero/methods/sequences.rb

Instance Method Summary collapse

Instance Method Details

#sequence_danger(threshold: 0.9, sequences: nil) ⇒ Object 



54
55
56
57
 
# File 'lib/pghero/methods/sequences.rb', line 54

def sequence_danger(threshold: 0.9, sequences: nil)
  sequences ||= self.sequences
  sequences.select { |s| s[:last_value] && s[:last_value] / s[:max_value].to_f > threshold }.sort_by { |s| s[:max_value] - s[:last_value] }
end

#sequences ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/pghero/methods/sequences.rb', line 4

def sequences
  # get columns with default values
  # use pg_get_expr to get correct default value
  # it's what information_schema.columns uses
  # also, exclude temporary tables to prevent error
  # when accessing across sessions
  sequences = select_all <<~SQL
    SELECT
      n.nspname AS table_schema,
      c.relname AS table,
      attname AS column,
      format_type(a.atttypid, a.atttypmod) AS column_type,
      pg_get_expr(d.adbin, d.adrelid) AS default_value
    FROM
      pg_catalog.pg_attribute a
    INNER JOIN
      pg_catalog.pg_class c ON c.oid = a.attrelid
    INNER JOIN
      pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    INNER JOIN
      pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
    WHERE
      NOT a.attisdropped
      AND a.attnum > 0
      AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'
      AND n.nspname NOT LIKE 'pg\\_temp\\_%'
  SQL

  # parse out sequence
  sequences.each do |column|
    column[:max_value] = column[:column_type] == 'integer' ? 2147483647 : 9223372036854775807

    column[:schema], column[:sequence] = parse_default_value(column[:default_value])
    column.delete(:default_value) if column[:sequence]
  end

  add_sequence_attributes(sequences)

  sequences.select { |s| s[:readable] }.each_slice(1024) do |slice|
    sql = slice.map { |s| "SELECT last_value FROM #{quote_ident(s[:schema])}.#{quote_ident(s[:sequence])}" }.join(" UNION ALL ")

    select_all(sql).zip(slice) do |row, seq|
      seq[:last_value] = row[:last_value]
    end
  end

  # use to_s for unparsable sequences
  sequences.sort_by { |s| s[:sequence].to_s }
end