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 



81
82
83
84
 
# File 'lib/pghero/methods/sequences.rb', line 81

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/pghero/methods/sequences.rb', line 4

def sequences
  # get columns with default values and identity columns
  # use pg_depend to get owned sequences (like pg_get_serial_sequence)
  # use pg_get_expr to get correct default value for rest
  # it's what information_schema.columns uses
  # also, exclude temporary tables to prevent error
  # when accessing across sessions
  sequences = select_all <<~SQL
    SELECT
      sn.nspname AS schema,
      s.relname AS sequence,
      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
    LEFT JOIN
      pg_catalog.pg_depend dep ON dep.refclassid = 'pg_catalog.pg_class'::regclass
      AND dep.refobjid = a.attrelid
      AND dep.refobjsubid = a.attnum
      AND dep.classid = 'pg_catalog.pg_class'::regclass
      AND dep.objsubid = 0
      AND dep.deptype IN ('i', 'a')
      AND dep.objid IN (SELECT oid FROM pg_class WHERE relkind = 'S')
    LEFT JOIN
      pg_catalog.pg_class s ON s.oid = dep.objid
    LEFT JOIN
      pg_catalog.pg_namespace sn ON sn.oid = s.relnamespace
    LEFT JOIN
      pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid
      AND a.attnum = d.adnum
      AND s.relkind IS NULL
    WHERE
      NOT a.attisdropped
      AND a.attnum > 0
      AND (pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%' OR s.relname IS NOT NULL)
      AND c.relpersistence <> 't'
  SQL

  sequences.each do |column|
    column[:max_value] =
      case column[:column_type]
      when "smallint"
        32767
      when "integer"
        2147483647
      else
        9223372036854775807
      end

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

  add_sequence_attributes(sequences)

  last_value = {}
  select_all("SELECT schemaname AS schema, sequencename AS sequence, COALESCE(last_value, start_value) AS last_value FROM pg_sequences").each do |row|
    last_value[[row[:schema], row[:sequence]]] = row[:last_value]
  end

  sequences.select { |s| s[:readable] }.each do |seq|
    seq[:last_value] = last_value[[seq[:schema], seq[:sequence]]]
  end

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