[check_postgres] problem with last_analyze and last_vacuum checks
Jeff Frost
jeff at frostconsultingllc.com
Wed Apr 8 22:30:27 UTC 2009
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
More information about the Check_postgres
mailing list