Re: auto-vacuum & Negative "anl" Values

[Alvaro Herrera <alvherre(at)commandprompt(dot)com>]

Dylan Hansen wrote:
> So can I assume that this is a bug?

Definitively a bug.

> The only resolution I can see right now is to setup a cron job that  
> will perform an ANALYZE periodically, as the pg_autovacuum ANALYZE  
> threshold is never reached.
> 
> Any other suggestions?  Thanks for the input!

I just committed a fix, so the other alternative is get a CVS checkout
from the 8.1 branch and put it up to see if it fixes your problem.  The
relevant patch is below.

Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.111.2.3
diff -c -p -r1.111.2.3 pgstat.c
*** src/backend/postmaster/pgstat.c	19 May 2006 15:15:38 -0000	1.111.2.3
--- src/backend/postmaster/pgstat.c	27 Jun 2006 03:36:03 -0000
*************** pgstat_recv_vacuum(PgStat_MsgVacuum *msg
*** 2919,2924 ****
--- 2919,2930 ----
  	tabentry->n_dead_tuples = 0;
  	if (msg->m_analyze)
  		tabentry->last_anl_tuples = msg->m_tuples;
+ 	else
+ 	{
+ 		/* last_anl_tuples must never exceed n_live_tuples */
+ 		tabentry->last_anl_tuplse = Min(tabentry->last_anl_tuples,
+ 										msg->m_tuples);
+ 	}
  }
  
  /* ----------
*************** pgstat_recv_tabstat(PgStat_MsgTabstat *m
*** 3055,3061 ****
  			tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
  			tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;
  
! 			tabentry->n_live_tuples += tabmsg[i].t_tuples_inserted;
  			tabentry->n_dead_tuples += tabmsg[i].t_tuples_updated +
  				tabmsg[i].t_tuples_deleted;
  
--- 3061,3068 ----
  			tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
  			tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;
  
! 			tabentry->n_live_tuples += tabmsg[i].t_tuples_inserted -
! 				tabmsg[i].t_tuples_deleted;
  			tabentry->n_dead_tuples += tabmsg[i].t_tuples_updated +
  				tabmsg[i].t_tuples_deleted;
  

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.