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 



59
60
61
62
 
# File 'lib/pghero/methods/sequences.rb', line 59

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
53
54
55
56
57
 
# 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 "    SELECT\n      n.nspname AS table_schema,\n      c.relname AS table,\n      attname AS column,\n      format_type(a.atttypid, a.atttypmod) AS column_type,\n      pg_get_expr(d.adbin, d.adrelid) AS default_value\n    FROM\n      pg_catalog.pg_attribute a\n    INNER JOIN\n      pg_catalog.pg_class c ON c.oid = a.attrelid\n    INNER JOIN\n      pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n    INNER JOIN\n      pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)\n    WHERE\n      NOT a.attisdropped\n      AND a.attnum > 0\n      AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'\n      AND n.nspname NOT LIKE 'pg\\\\_temp\\\\_%'\n  SQL\n\n  # parse out sequence\n  sequences.each do |column|\n    column[:max_value] = column[:column_type] == 'integer' ? 2147483647 : 9223372036854775807\n\n    column[:schema], column[:sequence] = parse_default_value(column[:default_value])\n    column.delete(:default_value) if column[:sequence]\n  end\n\n  add_sequence_attributes(sequences)\n\n  last_value = {}\n  sequences.select { |s| s[:readable] }.map { |s| [s[:schema], s[:sequence]] }.uniq.each_slice(1024) do |slice|\n    sql = slice.map { |s| \"SELECT last_value FROM \#{quote_ident(s[0])}.\#{quote_ident(s[1])}\" }.join(\" UNION ALL \")\n\n    select_all(sql).zip(slice) do |row, seq|\n      last_value[seq] = row[:last_value]\n    end\n  end\n\n  sequences.select { |s| s[:readable] }.each do |seq|\n    seq[:last_value] = last_value[[seq[:schema], seq[:sequence]]]\n  end\n\n  # use to_s for unparsable sequences\n  sequences.sort_by { |s| s[:sequence].to_s }\nend\n"