2015-02-04 6:37 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>: > Stephen Frost <sfr...@snowman.net> writes: > > All, > > We recently had a client complain that check_postgres' commitratio > > check would alert about relatively unused databases. As it turns > > out, the reason for this is because they automate running pg_dump > > against their databases (surely a good thing..), but pg_dump doesn't > > close out its transaction cleanly, leading to rolled back > > transactions. > > > At first blush, at least, this strikes me as an oversight which we > > should probably fix and possibly backpatch. > > No, somebody should fix check_postgres to count rollbacks as well as > commits as activity (as they obviously are). > > Well, actually, no. This is a commit ratio, not an activity counter, not even a transactions count.
The formula right now is: round(100.*sd.xact_commit/(sd.xact_commit+sd.xact_rollback), 2) which, AFAICT, is correct. The fact that the OP uses it to know if there's activity on his databases can get him false positives if he has no actual activity, except for dumps. I might be wrong, but there is nothing to fix on the check_postgres (at least, for this issue ;) ). The expectation of this user is to fix :) This is not an oversight, it's 100% intentional. The reason pg_dump > aborts rather than commits is to make entirely sure that it does not > commit any changes to the database. I would be against removing that > safety feature, considering that pg_dump is typically run as superuser. > We have frequently worried about security exploits that involve hijacking > superuser activities, and this behavior provides at least a small > increment of safety against such holes. > > +1 -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
