On 16/11/2011, at 12:26 PM, Robert Haas wrote:
> On Tue, Nov 15, 2011 at 7:59 PM, Royce Ausburn <royce.ml@inomial.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
tryingto diagnose an ineffective vacuum and postgres' logs weren't giving me any hints about what was wrong. I turned
tothe mailing list and got immediate help, but I felt that ideally postgres would be logging something to tell me that
some1 day old transactions were preventing auto vacuum from doing its job. Something, anything that I could google.
Othernovices in my situation probably wouldn't know to look in the pg_stats* tables, so in retrospect my patch isn't
reallyachieving 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
(perhapsconfigurable) time? The default might be pretty large, say 6 hours. Are there common use cases for txs that
runfor 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
operatingand 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.
Fair enough -- someone knowledgable could set that up if they wanted. My goal was mostly to have something helpful in
thelogs. If that's not something postgres wants/needs Ill drop it =)