On Wed, Jun 8, 2011 at 10:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Pavan Deolasee <pavan.deola...@gmail.com> writes: > > I first thought that analyze and vacuum can not run concurrently on the > same > > table since they take a conflicting lock on the table. So even if we > ignore > > the analyze process while calculating the OldestXmin for vacuum, we > should > > be fine since we know they are working on different tables. But I see > > analyze also acquires sample rows from the inherited tables with a > > non-conflicting lock. I probably do not understand the analyze code well, > > but is that the reason why we can't ignore analyze snapshot while > > determining OldestXmin for vacuum ? > > The reason why we can't ignore that snapshot is that it's being set for > the use of user-defined functions, which might do practically anything. > They definitely could access tables other than the one under analysis. > (I believe that PostGIS does such things, for example --- it wants to > look at its auxiliary tables for metadata.) > > Also keep in mind that we allow ANALYZE to be run inside a transaction > block, which might contain other operations sharing the same snapshot. > > Ah, I see. Would there will be benefits if we can do some special handling for cases where we know that ANALYZE is running outside a transaction block and that its not going to invoke any user-defined functions ? If user is running ANALYZE inside a transaction block, he is probably already aware and ready to handle long-running transaction. But running them under the covers as part of auto-analyze does not see quite right. The pgbench test already shows the severe bloat that a long running analyze may cause for small tables and many wasteful vacuum runs on those tables. Another idea would be to split the ANALYZE into multiple small transactions, each taking a new snapshot. That might result in bad statistics if the table is undergoing huge change, but in that case, the stats will be outdated soon anyways if we run with a old snapshot. I understand there could be issues like counting the same tuple twice or more, but would that be a common case to worry about ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
