[check_postgres] problem with last_analyze and last_vacuum checks

[Jeff Frost]

I'm having an issue with the last_analyze and last_vacuum checks.  It
seems if you specify a perflimit, then it complains that no matching
tables are found.  If you run the script like so to grab the sql:

PGOPTIONS='-c log_statement=all' perl ./check_postgres.pl -u postgres 
--action=last_analyze --warning='8d' --critical='15d' --perflimit 3
--exclude='~pg_'

You'll find the query is:

SELECT nspname, relname, CASE WHEN v IS NULL THEN -1 ELSE
round(extract(epoch FR
OM now()-v)) END,  CASE WHEN v IS NULL THEN '?' ELSE TO_CHAR(v, 'HH24:MI
FMMonth
 DD, YYYY') END FROM (SELECT nspname, relname,
GREATEST(pg_stat_get_last_analyze
_time(c.OID), pg_stat_get_last_autoanalyze_time(c.OID)) AS v FROM
pg_class c, pg
_namespace n WHERE relkind = 'r' AND n.OID = c.relnamespace AND
n.nspname <> 'in
formation_schema' ORDER BY 3) AS foo ORDER BY 3 DESC LIMIT 3;

which actually just returns these pg_catalog relations:

  nspname   |   relname    | round | to_char
------------+--------------+-------+---------
 pg_catalog | pg_authid    |    -1 | ?
 pg_catalog | pg_database  |    -1 | ?
 pg_catalog | pg_statistic |    -1 | ?
(3 rows)

BUT, next the code is going to exclude the ~pg_ tables and then
complains that there are no matching tables:

POSTGRES_LAST_ANALYZE UNKNOWN: DB "jefftest" No matching tables found
due to exclusion/inclusion options | time=0.01

So, the --perflimit 3 is causing a problem.  It seems we should either
process the excluded tables in the query or remove the limit and process
the limit programmatically.

The workaround for now is to remove the perflimit.

-- 
Jeff Frost, Owner 	<jeff at frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032