maintenance.rb in pghero-2.0.0

- old
+ new

@@ -3,44 +3,36 @@
     module Maintenance
       # http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
       # "the system will shut down and refuse to start any new transactions
       # once there are fewer than 1 million transactions left until wraparound"
       # warn when 10,000,000 transactions left
-      def transaction_id_danger(options = {})
-        threshold = options[:threshold] || 10000000
+      def transaction_id_danger(threshold: 10000000, max_value: 2146483648)
+        max_value = max_value.to_i
+        threshold = threshold.to_i
+
         select_all <<-SQL
           SELECT
-            c.oid::regclass::text AS table,
-            2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_shutdown
+            n.nspname AS schema,
+            c.relname AS table,
+            #{quote(max_value)} - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left
           FROM
             pg_class c
+          INNER JOIN
+            pg_catalog.pg_namespace n ON n.oid = c.relnamespace
           LEFT JOIN
             pg_class t ON c.reltoastrelid = t.oid
           WHERE
             c.relkind = 'r'
-            AND (2146483648 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < #{threshold}
+            AND (#{quote(max_value)} - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < #{quote(threshold)}
           ORDER BY
            2, 1
         SQL
       end
 
       def autovacuum_danger
-        select_all <<-SQL
-          SELECT
-            c.oid::regclass::text as table,
-            (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int -
-            GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_before_autovacuum
-          FROM
-            pg_class c
-          LEFT JOIN
-            pg_class t ON c.reltoastrelid = t.oid
-          WHERE
-            c.relkind = 'r'
-            AND (SELECT setting FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')::int - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) < 2000000
-          ORDER BY
-            transactions_before_autovacuum
-        SQL
+        max_value = select_one("SHOW autovacuum_freeze_max_age").to_i
+        transaction_id_danger(threshold: 2000000, max_value: max_value)
       end
 
       def maintenance_info
         select_all <<-SQL
           SELECT
@@ -55,23 +47,28 @@
           ORDER BY
             1, 2
         SQL
       end
 
-      def analyze(table)
-        execute "ANALYZE #{quote_table_name(table)}"
+      def analyze(table, verbose: false)
+        execute "ANALYZE #{verbose ? "VERBOSE " : ""}#{quote_table_name(table)}"
         true
       end
 
-      def analyze_tables
-        table_stats.reject { |s| %w(information_schema pg_catalog).include?(s["schema"]) }.map { |s| s.slice("schema", "table") }.each do |stats|
+      def analyze_tables(verbose: false, min_size: nil, tables: nil)
+        tables = table_stats(table: tables).reject { |s| %w(information_schema pg_catalog).include?(s[:schema]) }
+        tables = tables.select { |s| s[:size_bytes] > min_size } if min_size
+        tables.map { |s| s.slice(:schema, :table) }.each do |stats|
           begin
-            with_lock_timeout(5000) do
-              analyze "#{stats["schema"]}.#{stats["table"]}"
+            with_transaction(lock_timeout: 5000, statement_timeout: 120000) do
+              analyze "#{stats[:schema]}.#{stats[:table]}", verbose: verbose
             end
+            success = true
           rescue ActiveRecord::StatementInvalid => e
             $stderr.puts e.message
+            success = false
           end
+          stats[:success] = success
         end
       end
     end
   end
 end