Re: pg_dump's aborted transactions

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).

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.

regards, tom lane