[check_postgres] custom query problem, getting cache hit rate for a DB.

[Greg Sabino Mullane]

> check_postgres.pl --action=custom_query --db=yuma_prod -H joliet -u
> craig --query="SELECT
> round(SUM(pg_stat_get_db_blocks_hit(d.oid)+0.01)::real /
> SUM(pg_stat_get_db_blocks_fetched(d.oid)+0.01)::real*100) AS hit_rate
> FROM pg_database d" -c 97
> POSTGRES_CUSTOM_QUERY UNKNOWN: DB "yuma_prod" (host:joliet) Invalid
> format returned by custom query | time=0.11
> 
> Which seems to be saying it's not getting back an integer, but I
> happen to know that 97 is indeed an integer, so I'm not sure what's
> going on.

It's not complaining about the 97, per se, but about your query. You must 
return a column named "result"; any other column is used as the label 
for the performance data. So your query should look like this:

SELECT round(SUM(pg_stat_get_db_blocks_hit(oid)+0.01)::real /
  SUM(pg_stat_get_db_blocks_fetched(oid)+0.01)::real*100) AS result,
  1 AS hit_rate
  FROM pg_database;


-- 
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/check_postgres/attachments/20110911/fbc733b8/attachment.bin