Stephen Frost <sfrost@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 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.
