Re: pg_dump's aborted transactions

2015-02-04 6:37 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Stephen Frost <sfrost(at)snowman(dot)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