1 | 'use strict'
|
2 |
|
3 | const cli = require('heroku-cli-util')
|
4 |
|
5 | async function run(context, heroku) {
|
6 | const fetcher = require('../lib/fetcher')(heroku)
|
7 | const psql = require('../lib/psql')
|
8 |
|
9 | let db = await fetcher.database(context.app, context.args.database)
|
10 |
|
11 | let query = `
|
12 | WITH table_opts AS (
|
13 | SELECT
|
14 | pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
|
15 | FROM
|
16 | pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
|
17 | ), vacuum_settings AS (
|
18 | SELECT
|
19 | oid, relname, nspname,
|
20 | CASE
|
21 | WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
|
22 | THEN substring(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*')::integer
|
23 | ELSE current_setting('autovacuum_vacuum_threshold')::integer
|
24 | END AS autovacuum_vacuum_threshold,
|
25 | CASE
|
26 | WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
|
27 | THEN substring(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*')::real
|
28 | ELSE current_setting('autovacuum_vacuum_scale_factor')::real
|
29 | END AS autovacuum_vacuum_scale_factor
|
30 | FROM
|
31 | table_opts
|
32 | )
|
33 | SELECT
|
34 | vacuum_settings.nspname AS schema,
|
35 | vacuum_settings.relname AS table,
|
36 | to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,
|
37 | to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
|
38 | to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
|
39 | to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
|
40 | to_char(autovacuum_vacuum_threshold
|
41 | + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
|
42 | CASE
|
43 | WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
|
44 | THEN 'yes'
|
45 | END AS expect_autovacuum
|
46 | FROM
|
47 | pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
|
48 | INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid
|
49 | ORDER BY 1
|
50 | `
|
51 |
|
52 | let output = await psql.exec(db, query)
|
53 | process.stdout.write(output)
|
54 | }
|
55 |
|
56 | const cmd = {
|
57 | topic: 'pg',
|
58 | description: 'show dead rows and whether an automatic vacuum is expected to be triggered',
|
59 | needsApp: true,
|
60 | needsAuth: true,
|
61 | args: [{ name: 'database', optional: true }],
|
62 | run: cli.command({ preauth: true }, run)
|
63 | }
|
64 |
|
65 | module.exports = [
|
66 | Object.assign({ command: 'vacuum-stats' }, cmd),
|
67 | Object.assign({ command: 'vacuum_stats', hidden: true }, cmd)
|
68 | ]
|