Re: [PATCH] Unremovable tuple monitoring

On Tue, Nov 15, 2011 at 7:59 PM, Royce Ausburn <royce(dot)ml(at)inomial(dot)com> wrote:
>> Personally I think some log output, done better, would have been more useful for me at the time.  At the time I was trying to diagnose an ineffective vacuum and postgres' logs weren't giving me any hints about what was wrong.  I turned to the mailing list and got immediate help, but I felt that ideally postgres would be logging something to tell me that some 1 day old transactions were preventing auto vacuum from doing its job.  Something, anything that I could google.  Other novices in my situation probably wouldn't know to look in the pg_stats* tables, so in retrospect my patch isn't really achieving my original goal.
>>
>> Should we consider taking a logging approach instead?
>
> Dopey suggestion:
>
> Instead of logging around vacuum and auto_vacuum, perhaps log transactions that are open for longer than some (perhaps configurable) time?  The default might be pretty large, say 6 hours.  Are there common use cases for txs that run for longer than 6 hours?  Seeing a message such as:
>
> WARNING: Transaction <X> has been open more than Y.  This tx may be holding locks preventing other txs from operating and may prevent vacuum from cleaning up deleted rows.
>
> Would give a pretty clear indication of a problem :)

Well, you could that much just by periodically querying pg_stat_activity.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company