Re: [PATCH] Unremovable tuple monitoring - Mailing list pgsql-hackers

From Royce Ausburn
Subject Re: [PATCH] Unremovable tuple monitoring
Date
Msg-id A6FD24AB-AFD2-4C7F-A1E6-EE8D0E1A5128@inomial.com
Whole thread Raw
In response to Re: [PATCH] Unremovable tuple monitoring  (Royce Ausburn <royce.ml@inomial.com>)
Responses Re: [PATCH] Unremovable tuple monitoring
List pgsql-hackers
>
>
> 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 (perhaps
configurable)time?  The default might be pretty large, say 6 hours.  Are there common use cases for txs that run for
longerthan 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
andmay prevent vacuum from cleaning up deleted rows. 

Would give a pretty clear indication of a problem :)




pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: ISN was: Core Extensions relocation
Next
From: Joshua Berkus
Date:
Subject: Re: ISN was: Core Extensions relocation