check_vacuum issue

[fabioporta]

Hi,

i m using your plugin to check if a manual vacuum happened

i m using it with the option:
last_vacuum - Check the maximum time in seconds since any one table has been vacuumed.

and i expected it to check if only a manual vacuum happened (and not an autovacuum because there is already an option 'last_autovacuum' to check that). it would be great to have a flag to only check manual vacuums. given this, i found a bug:

in case that on an entire cluster we fall in this situation:

• manual vacuum happened
• autovacuum never happened since its creation

then the plugin reports that 'vacuum has never been performed'.

while it is not true. (no autovacuum but only manual vacuums happened).

the performed query on the plugin is:

SELECT current_database() AS datname, nspname AS sname, relname AS tname, CASE WHEN v IS NULL THEN -1 ELSE round(extract(epoch FROM now()-v)) END AS ltime, CASE WHEN v IS NULL THEN '?' ELSE TO_CHAR(v, 'HH24:MI FMMonth DD, YYYY') END AS ptime

    FROM (
    
    SELECT nspname, relname, GREATEST(pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid)) AS v
          FROM pg_class c, pg_namespace n
          WHERE relkind = 'r'
          AND n.oid = c.relnamespace
          AND n.nspname <> 'information_schema' ;

and i believe the problem is on:

GREATEST(pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid))

how to reproduce:

• on a fresh postgres installation (tested on 9.2.4) disable autovacuum in postgresql.conf autovacuum = off

• then create a database.

• and populate the database.

• run manual vacuum: vacuum ;

run the plugin against your cluster:

./check_postgres.pl -H IP_ADDRESS --db database -u user --action=last_vacuum

plugin will return:

POSTGRES_LAST_VACUUM UNKNOWN: DB your_db (host:IP_ADDRESS) No matching tables have ever been vacuumed

while if you manually inspect the tables, you will notice that manual vacuums ran, while autovacuums column is all empty.