Re: pg_dump's aborted transactions

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

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