Autovacuum Integration Patch Take 5

Started by Matthew T. O'Connoralmost 22 years ago10 messagespatches

matthew@zeut.net

almost 22 years ago

Well I didn't get out of the office as early as I had hoped, and I have
stayed up longer than I had planned, but I have a patch that addresses
many of the issues raised by Tom. Please take a look at let me know if
I'm heading in the right direction.

Issues addressed:
* Add ability to read username and password from autovac.passwd file in
$PGDATA (format is username:password)
* Update patch, make sure it doesn't revert the recent write_stderr
changes in postmaster.c
* Remove static from functions defined in pg_autovacuum.h
* Remove if(sigsetjmp ...) code block
* Removed improper exit()'s, replaced with proc_exit() or
ereport(ERROR)
* Change elog() calls to ereport()
* Change elog(WARNING, "pg_autovacuum: autovac is enabled, but requires
stats_row_level which is not enabled"); to only be called once on
postmaster startup
* Remove unneeded code that was cut and pasted from the bgwriter example

Issues not addressed in this patch:
* Dynamic linking of libpq: I need someone else to do this.
* Autovacuum Shutdown: I'm going to take a stab at this tonight
(Wednesday) we will see what I come up with.

To apply this patch:
1) Move pg_autovacuum.[ch] from contrib to
src/backend/postmaster/autovacuum.c and
src/include/postmaster/autovacuum.h respectively.
2) Place the attached pg_autovacuum.h in src/include/catelog/
3) Apply the attached diff file

Thanks again,

Matthew O'Connor

Matthew T. O'Connor

matthew@zeut.net

almost 22 years ago

In reply to: Matthew T. O'Connor (#1)

Re: Autovacuum Integration Patch Take 5

Just to reply to my own post here, I spent a few hours last night trying
to improve the shutdown sequence for the Autovacuum patch. I think I
was headed in the right direction, but unfortunatly I wasn't able to get
anything to a high enough quality where I could submit the patch.
Basically I changed the postmaster shutdown code to signal the
autovacuum process before it signals any of it's normal child processes,
then inside the autovacuum signal handler I was issuing a query cancel
in the current database connection is there was one. For some unknown
reason, when I went to shut down the autovacuum process by editing
postgresql.conf and signaling the postmaster to reread it, I would get
one proc_exit(0) in the log followed by several hundred proc_exit(1) and
it was too late for me to track this down. I can send in what I have so
far, but it's not pretty.

Unfortunately, I am not going to be available to work on this for the
next few days. I don't know what to do.

Thanks,

Matthew O'Connor

Matthew T. O'Connor wrote:

Show quoted text

Well I didn't get out of the office as early as I had hoped, and I have
stayed up longer than I had planned, but I have a patch that addresses
many of the issues raised by Tom. Please take a look at let me know if
I'm heading in the right direction.

Issues addressed:
* Add ability to read username and password from autovac.passwd file in
$PGDATA (format is username:password)
* Update patch, make sure it doesn't revert the recent write_stderr
changes in postmaster.c
* Remove static from functions defined in pg_autovacuum.h
* Remove if(sigsetjmp ...) code block
* Removed improper exit()'s, replaced with proc_exit() or
ereport(ERROR)
* Change elog() calls to ereport()
* Change elog(WARNING, "pg_autovacuum: autovac is enabled, but requires
stats_row_level which is not enabled"); to only be called once on
postmaster startup
* Remove unneeded code that was cut and pasted from the bgwriter example

Issues not addressed in this patch:
* Dynamic linking of libpq: I need someone else to do this.
* Autovacuum Shutdown: I'm going to take a stab at this tonight
(Wednesday) we will see what I come up with.

To apply this patch:
1) Move pg_autovacuum.[ch] from contrib to
src/backend/postmaster/autovacuum.c and
src/include/postmaster/autovacuum.h respectively.
2) Place the attached pg_autovacuum.h in src/include/catelog/
3) Apply the attached diff file

Thanks again,

Matthew O'Connor

------------------------------------------------------------------------

/*-------------------------------------------------------------------------
*
* pg_autovacuum.h
* definition of the system "autovacuum" relation (pg_autovacuum)
*
* NOTE: an object is identified by the OID of the row that primarily
* defines the object, plus the OID of the table that that row appears in.
* For example, a function is identified by the OID of its pg_proc row
* plus the pg_class OID of table pg_proc. This allows unique identification
* of objects without assuming that OIDs are unique across tables.
*
* Since attributes don't have OIDs of their own, we identify an attribute
* comment by the objoid+classoid of its parent table, plus an "objsubid"
* giving the attribute column number. "objsubid" must be zero in a comment
* for a table itself, so that it is distinct from any column comment.
* Currently, objsubid is unused and zero for all other kinds of objects,
* but perhaps it might be useful someday to associate comments with
* constituent elements of other kinds of objects (arguments of a function,
* for example).
*
*
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
* $PostgreSQL: pgsql-server/src/include/catalog/pg_autovacuum.h,v 1.20 2003/11/29 22:40:58 pgsql Exp $
*
* NOTES
* the genbki.sh script reads this file and generates .bki
* information from the DATA() statements.
*
* XXX do NOT break up DATA() statements into multiple lines!
* the scripts are not as smart as you might think...
*
*-------------------------------------------------------------------------
*/
#ifndef PG_AUTOVACUUM_H
#define PG_AUTOVACUUM_H

/* ----------------
* postgres.h contains the system type definitions and the
* CATALOG(), BOOTSTRAP and DATA() sugar words so this file
* can be read by both genbki.sh and the C compiler.
* ----------------
*/

/* ----------------
* pg_autovacuum definition. cpp turns this into
* typedef struct FormData_pg_autovacuum
* ----------------
*/
CATALOG(pg_autovacuum) BKI_WITHOUT_OIDS
{
Oid table_oid; /* OID of table */
int4 analyze_base_threshold; /* Base Threshold value */
int4 vacuum_base_threshold; /* Base Threshold value */
float4 analyze_scaling_factor; /* Threshold of ins/upd/del's before analyze */
float4 vacuum_scaling_factor; /* Threshold of ins/upd/del's before vacuuum */
float4 analyze_threshold; /* Threshold of ins/upd/del's before analyze */
float4 vacuum_threshold; /* Threshold of ins/upd/del's before vacuuum */
float4 cnt_at_last_analyze; /* equal to: inserts + updates as
* of the last analyze or initial
* values at startup */
float4 cnt_at_last_vacuum; /* equal to: deletes + updates as
* of the last vacuum or initial
* values at startup */
} FormData_pg_autovacuum;

/* ----------------
* Form_pg_autovacuum corresponds to a pointer to a tuple with
* the format of pg_autovacuum relation.
* ----------------
*/
typedef FormData_pg_autovacuum *Form_pg_autovacuum;

/* ----------------
* compiler constants for pg_autovacuum
* ----------------
*/
#define Natts_pg_autovacuum 9
#define Anum_pg_autovacuum_table_oid 1
#define Anum_pg_autovacuum_analyze_base_threshold 2
#define Anum_pg_autovacuum_vacuum_base_threshold 3
#define Anum_pg_autovacuum_analyze_scaling_factor 4
#define Anum_pg_autovacuum_vacuum_scaling_factor 5
#define Anum_pg_autovacuum_analyze_threshold 6
#define Anum_pg_autovacuum_vacuum_threshold 7
#define Anum_pg_autovacuum_cnt_at_last_analyze 8
#define Anum_pg_autovacuum_cnt_at_last_vacuum 9

/* ----------------
* initial contents of pg_autovacuum
* ----------------
*/

/*
* Because the contents of this table are taken from the other *.h files,
* there is no initialization here. The initial contents are extracted
* by genbki.sh and loaded during initdb.
*/

#endif /* PG_AUTOVACUUM_H */

------------------------------------------------------------------------
*** ./src/backend/bootstrap/bootstrap.c.orig	2004-08-04 01:41:29.044078073 -0400
--- ./src/backend/bootstrap/bootstrap.c	2004-08-03 00:47:35.000000000 -0400
***************
*** 33,38 ****
--- 33,39 ----
#include "libpq/pqsignal.h"
#include "miscadmin.h"
#include "postmaster/bgwriter.h"
+ #include "postmaster/autovacuum.h"
#include "storage/freespace.h"
#include "storage/ipc.h"
#include "storage/pg_shmem.h"
***************
*** 361,366 ****
--- 362,370 ----
case BS_XLOG_BGWRITER:
statmsg = "writer process";
break;
+ 			case BS_XLOG_AUTOVAC:
+ 				statmsg = "auto vacuum process";
+ 				break;
default:
statmsg = "??? process";
break;
***************
*** 397,402 ****
--- 401,409 ----
case BS_XLOG_BGWRITER:
InitDummyProcess(DUMMY_PROC_BGWRITER);
break;
+ 			case BS_XLOG_AUTOVAC:
+ 				InitDummyProcess(DUMMY_PROC_AUTOVAC);
+ 				break;
default:
InitDummyProcess(DUMMY_PROC_DEFAULT);
***************
*** 433,438 ****
--- 440,451 ----
BackgroundWriterMain();
proc_exit(1);		/* should never return */
+ 		case BS_XLOG_AUTOVAC:
+ 			/* don't set signals, autovac has its own agenda */
+ 			InitXLOGAccess();
+ 			AutoVacMain();
+ 			proc_exit(1);		/* should never return */
+ 		
default:
elog(PANIC, "unrecognized XLOG op: %d", xlogop);
proc_exit(1);
*** ./src/backend/catalog/Makefile.orig	2004-08-04 01:41:52.546499796 -0400
--- ./src/backend/catalog/Makefile	2004-07-22 23:50:31.000000000 -0400
***************
*** 32,38 ****
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h pg_group.h \
! 	pg_tablespace.h pg_depend.h indexing.h \
)
pg_includes := $(sort -I$(top_srcdir)/src/include -I$(top_builddir)/src/include)
--- 32,38 ----
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h pg_group.h \
! 	pg_tablespace.h pg_depend.h pg_autovacuum.h indexing.h \
)
pg_includes := $(sort -I$(top_srcdir)/src/include -I$(top_builddir)/src/include)
*** ./src/backend/Makefile.orig	2004-08-04 01:41:41.458716157 -0400
--- ./src/backend/Makefile	2004-08-03 00:47:34.000000000 -0400
***************
*** 29,41 ****
##########################################################################

! all: submake-libpgport postgres $(POSTGRES_IMP)

ifneq ($(PORTNAME), cygwin)
ifneq ($(PORTNAME), win32)

postgres: $(OBJS)
! $(CC) $(CFLAGS) $(LDFLAGS) $(export_dynamic) $^ $(LIBS) -o $@
endif
endif
--- 29,41 ----
##########################################################################

! all: submake-libpgport submake-libpq postgres $(POSTGRES_IMP)

ifneq ($(PORTNAME), cygwin)
ifneq ($(PORTNAME), win32)

postgres: $(OBJS)
! $(CC) $(CFLAGS) $(LDFLAGS) -I $(libpq_srcdir) $(export_dynamic) $^ $(LIBS) $(libpq) -o $@
endif
endif
*** ./src/backend/postmaster/autovacuum.c.orig	2004-06-29 09:27:14.000000000 -0400
--- ./src/backend/postmaster/autovacuum.c	2004-08-04 02:00:32.005810127 -0400
***************
*** 1,153 ****
! /* pg_autovacuum.c
* All the code for the pg_autovacuum program
* (c) 2003 Matthew T. O'Connor
* Revisions by Christopher B. Browne, Liberty RMS
*/
! #include "pg_autovacuum.h"

- FILE *LOGOUTPUT;
char logbuffer[4096];

! static void
! log_entry(const char *logentry)
! {
! time_t curtime;
! struct tm *loctime;
! char timebuffer[128];
!
! curtime = time(NULL);
! loctime = localtime(&curtime);
! strftime(timebuffer, sizeof(timebuffer), "%Y-%m-%d %H:%M:%S %Z", loctime);
! fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry);
! }

/*
! * Function used to detach the pg_autovacuum daemon from the tty and go into
! * the background.
*
! * This code is mostly ripped directly from pm_dameonize in postmaster.c with
! * unneeded code removed.
*/
! static void
! daemonize()
{
! pid_t pid;

! pid = fork();
! if (pid == (pid_t) -1)
! {
! log_entry("Error: cannot disassociate from controlling TTY");
! fflush(LOGOUTPUT);
! _exit(1);
! }
! else if (pid)
! { /* parent */
! /* Parent should just exit, without doing any atexit cleanup */
! _exit(0);
! }

! /* GH: If there's no setsid(), we hopefully don't need silent mode.
! * Until there's a better solution. */
! #ifdef HAVE_SETSID
! if (setsid() < 0)
! {
! log_entry("Error: cannot disassociate from controlling TTY");
! fflush(LOGOUTPUT);
! _exit(1);
! }
#endif

! }
!
! /* Create and return tbl_info struct with initialized to values from row or res */
! static tbl_info *
! init_table_info(PGresult *res, int row, db_info * dbi)
! {
! tbl_info *new_tbl = (tbl_info *) malloc(sizeof(tbl_info));

! if (!new_tbl)
! {
! log_entry("init_table_info: Cannot get memory");
! fflush(LOGOUTPUT);
! return NULL;
! }

! if (res == NULL)
! return NULL;

- new_tbl->dbi = dbi; /* set pointer to db */

! new_tbl->schema_name = (char *)
! malloc(strlen(PQgetvalue(res, row, PQfnumber(res, "schemaname"))) + 1);
! if (!new_tbl->schema_name)
! {
! log_entry("init_table_info: malloc failed on new_tbl->schema_name");
! fflush(LOGOUTPUT);
! return NULL;
! }
! strcpy(new_tbl->schema_name,
! PQgetvalue(res, row, PQfnumber(res, "schemaname")));

! new_tbl->table_name = (char *)
! malloc(strlen(PQgetvalue(res, row, PQfnumber(res, "relname"))) +
! strlen(new_tbl->schema_name) + 6);
! if (!new_tbl->table_name)
! {
! log_entry("init_table_info: malloc failed on new_tbl->table_name");
! fflush(LOGOUTPUT);
! return NULL;
! }

/*
! * Put both the schema and table name in quotes so that we can work
! * with mixed case table names
*/
! strcpy(new_tbl->table_name, "\"");
! strcat(new_tbl->table_name, new_tbl->schema_name);
! strcat(new_tbl->table_name, "\".\"");
! strcat(new_tbl->table_name, PQgetvalue(res, row, PQfnumber(res, "relname")));
! strcat(new_tbl->table_name, "\"");
!
! new_tbl->CountAtLastAnalyze =
! (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_ins"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))));
! new_tbl->curr_analyze_count = new_tbl->CountAtLastAnalyze;
!
! new_tbl->CountAtLastVacuum =
! (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))));
! new_tbl->curr_vacuum_count = new_tbl->CountAtLastVacuum;
!
! new_tbl->relid = atooid(PQgetvalue(res, row, PQfnumber(res, "oid")));
! new_tbl->reltuples = atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
! new_tbl->relpages = atooid(PQgetvalue(res, row, PQfnumber(res, "relpages")));
!
! if (strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
! new_tbl->relisshared = 0;
! else
! new_tbl->relisshared = 1;
!
! new_tbl->analyze_threshold =
! args->analyze_base_threshold + args->analyze_scaling_factor * new_tbl->reltuples;
! new_tbl->vacuum_threshold =
! args->vacuum_base_threshold + args->vacuum_scaling_factor * new_tbl->reltuples;

! if (args->debug >= 2)
! print_table_info(new_tbl);

! return new_tbl;
}

/* Set thresholds = base_value + scaling_factor * reltuples
Should be called after a vacuum since vacuum updates values in pg_class */
! static void
! update_table_thresholds(db_info * dbi, tbl_info * tbl, int vacuum_type)
{
- PGresult *res = NULL;
int disconnect = 0;
! char query[128];
if (dbi->conn == NULL)
{
--- 1,188 ----
! /*-------------------------------------------------------------------------
!  *
!  * pg_autovacuum.c
!  *
!  * The background autovacuum daemon was in 7.4 contribis but is being newly 
!  * integrated into 7.5.  It monitors database activity using data from the 
!  * stats system (though at some point is should also look at FSM data) so 
!  * as to perform vacuum commands on specific tables when and only when 
!  * a sufficient amount activity has been performed on that table.
!  *
!  * The autovacuum process is started by the postmaster on startup.
!  * It remains alive until the postmaster commands it to terminate. Normal 
!  * termination is by SIGUSR2, which instructs the autovacuum process to proc_exit().  
!  * Emergency termination is by SIGQUIT; like any
!  * backend, the autovacuum process will simply abort and proc_exit on SIGQUIT.
!  *
* All the code for the pg_autovacuum program
* (c) 2003 Matthew T. O'Connor
* Revisions by Christopher B. Browne, Liberty RMS
+  *-------------------------------------------------------------------------
*/
+ #include "postgres.h"
+ 
+ #include <signal.h>
+ #include <time.h>
! #include "access/xlog.h"
!
! #include "libpq/pqsignal.h"
! #include "miscadmin.h"
! #include "storage/bufmgr.h"
! #include "storage/freespace.h"
! #include "storage/ipc.h"
! #include "storage/pmsignal.h"
! #include "storage/smgr.h"
! #include "tcop/tcopprot.h"
! #include "utils/guc.h"
! #include "postmaster/autovacuum.h"

char logbuffer[4096];

! /*
! * GUC parameters
! */
! bool autovacuum_start_daemon = false;
! int autovacuum_vacuum_base = 1000;
! double autovacuum_vacuum_scaling_factor = 2;
! int autovacuum_analyze_base = 500;
! double autovacuum_analyze_scaling_factor = 1;
! int PostPortNumber = 5432;
!
! /*
! * These used to be taken from command lines args
! * and might need to move to GUC, but I'm not sure.
! */
! char *pg_user = NULL,
! *pg_user_password = NULL;
!
! /*
! * Flags set by interrupt handlers for later service in the main loop.
! */
! static volatile sig_atomic_t got_SIGHUP = false;
! static volatile sig_atomic_t shutdown_requested = false;

/*
! * Private state
! */
! static bool am_autovac = false;
!
!
! static void autovac_quickdie(SIGNAL_ARGS);
! static void AutoVacSigHupHandler(SIGNAL_ARGS);
! static void ReqShutdownHandler(SIGNAL_ARGS);
!
!
! /*
! * Main entry point for autovacuum sub-process
*
! * This is invoked from BootstrapMain, which has already created the basic
! * execution environment, but not enabled signals yet.
*/
! void
! AutoVacMain(void)
{
! am_autovac = true;

! /*
! * Properly accept or ignore signals the postmaster might send us
! *
! * Note: we deliberately ignore SIGTERM, because during a standard Unix
! * system shutdown cycle, init will SIGTERM all processes at once. We
! * want to wait for the backends to proc_exit, whereupon the postmaster will
! * tell us it's okay to shut down (via SIGUSR2).
! *
! * SIGUSR1 is presently unused; keep it spare in case someday we want
! * this process to participate in sinval messaging.
! */
! pqsignal(SIGHUP, AutoVacSigHupHandler); /* set flag to read config file */
! pqsignal(SIGTERM, SIG_IGN); /* ignore SIGTERM */
! pqsignal(SIGQUIT, autovac_quickdie); /* hard crash time */
! pqsignal(SIGALRM, SIG_IGN);
! pqsignal(SIGPIPE, SIG_IGN);
! pqsignal(SIGUSR1, SIG_IGN); /* reserve for sinval */
! pqsignal(SIGUSR2, ReqShutdownHandler); /* request shutdown */

! /*
! * Reset some signals that are accepted by postmaster but not here
! */
! pqsignal(SIGCHLD, SIG_DFL);
! pqsignal(SIGTTIN, SIG_DFL);
! pqsignal(SIGTTOU, SIG_DFL);
! pqsignal(SIGCONT, SIG_DFL);
! pqsignal(SIGWINCH, SIG_DFL);
!
! /* We allow SIGQUIT (quickdie) at all times */
! #ifdef HAVE_SIGPROCMASK
! sigdelset(&BlockSig, SIGQUIT);
! #else
! BlockSig &= ~(sigmask(SIGQUIT));
#endif

! /*
! * If an exception is encountered, processing resumes here.
! */

! /*
! * Unblock signals (they were blocked when the postmaster forked us)
! */
! PG_SETMASK(&UnBlockSig);

! AutoVacLoop();
! }

! /* --------------------------------
! * signal handler routines
! * --------------------------------
! */

! /*
! * autovac_quickdie() occurs when signalled SIGQUIT by the postmaster.
! *
! * Some backend has bought the farm,
! * so we need to stop what we're doing and proc_exit.
! */
! static void
! autovac_quickdie(SIGNAL_ARGS)
! {
! PG_SETMASK(&BlockSig);

/*
! * DO NOT proc_exit() -- we're here because shared memory may be
! * corrupted, so we don't want to try to clean up our transaction.
! * Just nail the windows shut and get out of town.
! *
! * Note we do exit(1) not exit(0). This is to force the postmaster into
! * a system reset cycle if some idiot DBA sends a manual SIGQUIT to a
! * random backend. This is necessary precisely because we don't clean
! * up our shared memory state.
*/
! exit(1);
! }

! /* SIGHUP: set flag to re-read config file at next convenient time */
! static void
! AutoVacSigHupHandler(SIGNAL_ARGS)
! {
! got_SIGHUP = true;
! }

! /* SIGUSR2: set flag to run a shutdown checkpoint and exit */
! static void
! ReqShutdownHandler(SIGNAL_ARGS)
! {
! shutdown_requested = true;
}

+
/* Set thresholds = base_value + scaling_factor * reltuples
Should be called after a vacuum since vacuum updates values in pg_class */
! void
! update_table_thresholds(db_info * dbi, Oid table_oid, int vacuum_type)
{
int disconnect = 0;
! char query[255];

if (dbi->conn == NULL)
{
***************
*** 157,215 ****

if (dbi->conn != NULL)
{
! snprintf(query, sizeof(query), PAGES_QUERY, tbl->relid);
! res = send_query(query, dbi);
! if (res != NULL)
{
! tbl->reltuples =
! atof(PQgetvalue(res, 0, PQfnumber(res, "reltuples")));
! tbl->relpages = atooid(PQgetvalue(res, 0, PQfnumber(res, "relpages")));
!
! /*
! * update vacuum thresholds only of we just did a vacuum
! * analyze
! */
! if (vacuum_type == VACUUM_ANALYZE)
! {
! tbl->vacuum_threshold =
! (args->vacuum_base_threshold + args->vacuum_scaling_factor * tbl->reltuples);
! tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
! }
!
! /* update analyze thresholds */
! tbl->analyze_threshold =
! (args->analyze_base_threshold + args->analyze_scaling_factor * tbl->reltuples);
! tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
!
! PQclear(res);
!
! /*
! * If the stats collector is reporting fewer updates then we
! * have on record then the stats were probably reset, so we
! * need to reset also
! */
! if ((tbl->curr_analyze_count < tbl->CountAtLastAnalyze) ||
! (tbl->curr_vacuum_count < tbl->CountAtLastVacuum))
! {
! tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
! tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
! }
}
}
if (disconnect)
db_disconnect(dbi);
}

! static void
! update_table_list(db_info * dbi)
{
int disconnect = 0;
! PGresult *res = NULL;
! tbl_info *tbl = NULL;
! Dlelem *tbl_elem = DLGetHead(dbi->table_list);
! int i = 0,
! t = 0,
! found_match = 0;
if (dbi->conn == NULL)
{
--- 192,244 ----
if (dbi->conn != NULL)
{
! 		/*
! 		* update vacuum and analyze thresholds if 
! 		* we did a vacuum analyze
! 		*/
! 		if (vacuum_type == VACUUM_ANALYZE)
{
! 			sprintf(query, "update pg_autovacuum set cnt_at_last_analyze = n_tup_ins + n_tup_upd, cnt_at_last_vacuum = n_tup_upd + n_tup_del from pg_stat_all_tables where table_oid = relid and relid = %u", table_oid);
! 			send_query(query, dbi);
}
+ 		/* 
+ 			* update only the analyze thresholds  if
+ 			* we only did an analyze
+ 			*/
+ 		else
+ 		{
+ 			sprintf(query, "update pg_autovacuum set cnt_at_last_analyze = n_tup_ins + n_tup_upd from pg_stat_all_tables where table_oid = relid and relid = %u", table_oid);
+ 			send_query(query, dbi);
+ 		}
+ 			
+ 		/* FIXME: Need to think about this and pg_stat roll over issues */
+ 		/*
+ 		 * If the stats collector is reporting fewer updates then we
+ 		 * have on record then the stats were probably reset, or overflowed, 
+ 		 * so we need to reset also our numbers also
+ 		 */
+ /*		if ((tbl->curr_analyze_count < tbl->CountAtLastAnalyze) ||
+ 			(tbl->curr_vacuum_count < tbl->CountAtLastVacuum))
+ 		{
+ 			tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
+ 			tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
+ 		}*/
}
if (disconnect)
db_disconnect(dbi);
}
! void
! update_pg_autovacuum_table(db_info *dbi)
{
+ 	/*
+ 	 * This function will update the pg_autovacuum system table in two steps:
+ 	 * 1) Delete entries that are no longer in pg_class an
+ 	 * 2) Add zero'd out entries to the pg_autovacuum table for new tables that exist in pg_class not in pg_autovacuum
+ 	 */
+ 	 
int			disconnect = 0;
! 	char		query[4096];
if (dbi->conn == NULL)
{
***************
*** 219,394 ****

if (dbi->conn != NULL)
{
/*
! * Get a result set that has all the information we will need to
! * both remove tables from the list that no longer exist and add
! * tables to the list that are new
*/
! res = send_query((char *) TABLE_STATS_QUERY, dbi);
! if (res != NULL)
! {
! t = PQntuples(res);
!
! /*
! * First: use the tbl_list as the outer loop and the result set as
! * the inner loop, this will determine what tables should be
! * removed
! */
! while (tbl_elem != NULL)
! {
! tbl = ((tbl_info *) DLE_VAL(tbl_elem));
! found_match = 0;
!
! for (i = 0; i < t; i++)
! { /* loop through result set looking for a
! * match */
! if (tbl->relid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
! {
! found_match = 1;
! break;
! }
! }
! if (found_match == 0)
! { /* then we didn't find this tbl_elem in
! * the result set */
! Dlelem *elem_to_remove = tbl_elem;
!
! tbl_elem = DLGetSucc(tbl_elem);
! remove_table_from_list(elem_to_remove);
! }
! else
! tbl_elem = DLGetSucc(tbl_elem);
! } /* Done removing dropped tables from the
! * table_list */
!
! /*
! * Then loop use result set as outer loop and tbl_list as the
! * inner loop to determine what tables are new
! */
! for (i = 0; i < t; i++)
! {
! tbl_elem = DLGetHead(dbi->table_list);
! found_match = 0;
! while (tbl_elem != NULL)
! {
! tbl = ((tbl_info *) DLE_VAL(tbl_elem));
! if (tbl->relid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
! {
! found_match = 1;
! break;
! }
! tbl_elem = DLGetSucc(tbl_elem);
! }
! if (found_match == 0) /* then we didn't find this result
! * now in the tbl_list */
! {
! DLAddTail(dbi->table_list, DLNewElem(init_table_info(res, i, dbi)));
! if (args->debug >= 1)
! {
! sprintf(logbuffer, "added table: %s.%s", dbi->dbname,
! ((tbl_info *) DLE_VAL(DLGetTail(dbi->table_list)))->table_name);
! log_entry(logbuffer);
! }
! }
! } /* end of for loop that adds tables */
! }
! fflush(LOGOUTPUT);
! PQclear(res);
! res = NULL;
! if (args->debug >= 3)
! print_table_list(dbi->table_list);
! if (disconnect)
! db_disconnect(dbi);
}
}

- /* Free memory, and remove the node from the list */
- static void
- remove_table_from_list(Dlelem *tbl_to_remove)
- {
- tbl_info *tbl = ((tbl_info *) DLE_VAL(tbl_to_remove));
-
- if (args->debug >= 1)
- {
- sprintf(logbuffer, "Removing table: %s from list.", tbl->table_name);
- log_entry(logbuffer);
- fflush(LOGOUTPUT);
- }
- DLRemove(tbl_to_remove);
-
- if (tbl->schema_name)
- {
- free(tbl->schema_name);
- tbl->schema_name = NULL;
- }
- if (tbl->table_name)
- {
- free(tbl->table_name);
- tbl->table_name = NULL;
- }
- if (tbl)
- {
- free(tbl);
- tbl = NULL;
- }
- DLFreeElem(tbl_to_remove);
- }
-
- /* Free the entire table list */
- static void
- free_tbl_list(Dllist *tbl_list)
- {
- Dlelem *tbl_elem = DLGetHead(tbl_list);
- Dlelem *tbl_elem_to_remove = NULL;
-
- while (tbl_elem != NULL)
- {
- tbl_elem_to_remove = tbl_elem;
- tbl_elem = DLGetSucc(tbl_elem);
- remove_table_from_list(tbl_elem_to_remove);
- }
- DLFreeList(tbl_list);
- }
-
- static void
- print_table_list(Dllist *table_list)
- {
- Dlelem *table_elem = DLGetHead(table_list);
-
- while (table_elem != NULL)
- {
- print_table_info(((tbl_info *) DLE_VAL(table_elem)));
- table_elem = DLGetSucc(table_elem);
- }
- }
-
- static void
- print_table_info(tbl_info * tbl)
- {
- sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
- log_entry(logbuffer);
- sprintf(logbuffer, " relid: %u; relisshared: %i", tbl->relid, tbl->relisshared);
- log_entry(logbuffer);
- sprintf(logbuffer, " reltuples: %f; relpages: %u", tbl->reltuples, tbl->relpages);
- log_entry(logbuffer);
- sprintf(logbuffer, " curr_analyze_count: %li; curr_vacuum_count: %li",
- tbl->curr_analyze_count, tbl->curr_vacuum_count);
- log_entry(logbuffer);
- sprintf(logbuffer, " last_analyze_count: %li; last_vacuum_count: %li",
- tbl->CountAtLastAnalyze, tbl->CountAtLastVacuum);
- log_entry(logbuffer);
- sprintf(logbuffer, " analyze_threshold: %li; vacuum_threshold: %li",
- tbl->analyze_threshold, tbl->vacuum_threshold);
- log_entry(logbuffer);
- fflush(LOGOUTPUT);
- }

/* End of table Management Functions */

/* Beginning of DB Management Functions */
/* init_db_list() creates the db_list and initalizes template1 */
! static Dllist *
init_db_list()
{
Dllist	   *db_list = DLNewList();
--- 248,276 ----
if (dbi->conn != NULL)
{
+ 		/* 
+ 		 * Delete entries in pg_autovacuum that are no longer in pg_class 
+ 		 */
+ 		send_query("DELETE from pg_autovacuum where table_oid not in (select relid from pg_stat_all_tables )",dbi);
+ 		
/*
! 		 * Insert entires into pg_autovacuum for new tables (ones that exist in pg_class / pg_stat.., but not in pg_autovacuum) 
! 		 * and fill in defaut values for these new tables.
! 		 * then add them to the table list.
*/
! 		sprintf(query, "insert into pg_autovacuum (table_oid, analyze_base_threshold, vacuum_base_threshold, analyze_scaling_factor, vacuum_scaling_factor, analyze_threshold, vacuum_threshold, cnt_at_last_analyze, cnt_at_last_vacuum)   select a.oid, current_setting('autovacuum_analyze_threshold_base')::int, current_setting('autovacuum_vacuum_threshold_base')::int, current_setting('autovacuum_analyze_threshold_sf')::float, current_setting('autovacuum_vacuum_threshold_sf')::float, -1, -1, 0, 0 from pg_class a inner join pg_stat_all_tables b on a.oid=b.relid left outer join pg_autovacuum c on a.oid = c.table_oid where a.relkind = 'r' and schemaname not like 'pg_temp_%%' and a.oid not in (select distinct table_oid from pg_autovacuum)"); 
! 		elog(DEBUG5, query);
! 		send_query(query,dbi);
}
}
/* End of table Management Functions */

/* Beginning of DB Management Functions */

/* init_db_list() creates the db_list and initalizes template1 */
! Dllist *
init_db_list()
{
Dllist *db_list = DLNewList();
***************
*** 398,405 ****
DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1", 0, 0)));
if (DLGetHead(db_list) == NULL)
{ /* Make sure init_dbinfo was successful */
! log_entry("init_db_list(): Error creating db_list for db: template1.");
! fflush(LOGOUTPUT);
return NULL;
}
--- 280,287 ----
DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1", 0, 0)));
if (DLGetHead(db_list) == NULL)
{							/* Make sure init_dbinfo was successful */
! 		ereport(ERROR,(errmsg("pg_autovacuum: Error initializing db_list")));
! 		
return NULL;
}
***************
*** 419,427 ****
dbs->age = atol(PQgetvalue(res, 0, PQfnumber(res, "age")));
if (res)
PQclear(res);
- 	
- 			if (args->debug >= 2)
- 				print_db_list(db_list, 0);
}
else
return NULL;
--- 301,306 ----
***************
*** 431,470 ****
/* Simple function to create an instance of the dbinfo struct
Initalizes all the pointers and connects to the database */
! static db_info *
init_dbinfo(char *dbname, Oid oid, long age)
{
db_info *newdbinfo = (db_info *) malloc(sizeof(db_info));

- newdbinfo->analyze_threshold = args->vacuum_base_threshold;
- newdbinfo->vacuum_threshold = args->analyze_base_threshold;
newdbinfo->dbname = (char *) malloc(strlen(dbname) + 1);
strcpy(newdbinfo->dbname, dbname);
newdbinfo->username = NULL;
! if (args->user != NULL)
{
! newdbinfo->username = (char *) malloc(strlen(args->user) + 1);
! strcpy(newdbinfo->username, args->user);
}
newdbinfo->password = NULL;
! if (args->password != NULL)
{
! newdbinfo->password = (char *) malloc(strlen(args->password) + 1);
! strcpy(newdbinfo->password, args->password);
}
newdbinfo->oid = oid;
newdbinfo->age = age;
- newdbinfo->table_list = DLNewList();
newdbinfo->conn = NULL;

- if (args->debug >= 2)
- print_table_list(newdbinfo->table_list);
-
return newdbinfo;
}
/* Function adds and removes databases from the db_list as appropriate */
! static void
update_db_list(Dllist *db_list)
{
int			disconnect = 0;
--- 310,345 ----
/* Simple function to create an instance of the dbinfo struct
Initalizes all the pointers and connects to the database */
! db_info *
init_dbinfo(char *dbname, Oid oid, long age)
{
db_info *newdbinfo = (db_info *) malloc(sizeof(db_info));

newdbinfo->dbname = (char *) malloc(strlen(dbname) + 1);
strcpy(newdbinfo->dbname, dbname);
newdbinfo->username = NULL;
! if (pg_user != NULL)
{
! newdbinfo->username = (char *) malloc(strlen(pg_user) + 1);
! strcpy(newdbinfo->username, pg_user);
}
+
newdbinfo->password = NULL;
! if (pg_user_password != NULL)
{
! newdbinfo->password = (char *) malloc(strlen(pg_user_password) + 1);
! strcpy(newdbinfo->password, pg_user_password);
}
+
newdbinfo->oid = oid;
newdbinfo->age = age;
newdbinfo->conn = NULL;

return newdbinfo;
}

/* Function adds and removes databases from the db_list as appropriate */
! void
update_db_list(Dllist *db_list)
{
int disconnect = 0;
***************
*** 476,486 ****
t = 0,
found_match = 0;

! if (args->debug >= 2)
! {
! log_entry("updating the database list");
! fflush(LOGOUTPUT);
! }
if (dbi_template1->conn == NULL)
{
--- 351,357 ----
t = 0,
found_match = 0;
! ereport(DEBUG2,(errmsg("pg_autovacuum: updating the database list")));
if (dbi_template1->conn == NULL)
{
***************
*** 563,581 ****
(PQgetvalue(res, i, PQfnumber(res, "datname")),
atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))),
atol(PQgetvalue(res, i, PQfnumber(res, "age"))))));
! 					if (args->debug >= 1)
! 					{
! 						sprintf(logbuffer, "added database: %s", ((db_info *) DLE_VAL(DLGetTail(db_list)))->dbname);
! 						log_entry(logbuffer);
! 					}
}
! 			}						/* end of for loop that adds tables */
}
- 		fflush(LOGOUTPUT);
PQclear(res);
res = NULL;
! 		if (args->debug >= 3)
! 			print_db_list(db_list, 0);
if (disconnect)
db_disconnect(dbi_template1);
}
--- 434,446 ----
(PQgetvalue(res, i, PQfnumber(res, "datname")),
atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))),
atol(PQgetvalue(res, i, PQfnumber(res, "age"))))));
! 					ereport(DEBUG1,(errmsg("pg_autovacuum: added database %s", ((db_info *) DLE_VAL(DLGetTail(db_list)))->dbname)));
}
! 			}	/* end of for loop that adds tables */
}
PQclear(res);
res = NULL;
! 		print_db_list(db_list, 0);
if (disconnect)
db_disconnect(dbi_template1);
}
***************
*** 595,601 ****
return 0 if nothing happened,
return 1 if the database needed a database wide vacuum
*/
! static int
xid_wraparound_check(db_info * dbi)
{
/*
--- 460,466 ----
return 0 if nothing happened,
return 1 if the database needed a database wide vacuum
*/
! int
xid_wraparound_check(db_info * dbi)
{
/*
***************
*** 620,636 ****
}
/* Close DB connection, free memory, and remove the node from the list */
! static void
remove_db_from_list(Dlelem *db_to_remove)
{
db_info *dbi = ((db_info *) DLE_VAL(db_to_remove));
! 	if (args->debug >= 1)
! 	{
! 		sprintf(logbuffer, "Removing db: %s from list.", dbi->dbname);
! 		log_entry(logbuffer);
! 		fflush(LOGOUTPUT);
! 	}
DLRemove(db_to_remove);
if (dbi->conn)
db_disconnect(dbi);
--- 485,497 ----
}
/* Close DB connection, free memory, and remove the node from the list */
! void
remove_db_from_list(Dlelem *db_to_remove)
{
db_info *dbi = ((db_info *) DLE_VAL(db_to_remove));
! 	ereport(DEBUG1,(errmsg("pg_autovacuum: Removing db: %s from list.", dbi->dbname)));
! 	
DLRemove(db_to_remove);
if (dbi->conn)
db_disconnect(dbi);
***************
*** 649,659 ****
free(dbi->password);
dbi->password = NULL;
}
- 	if (dbi->table_list)
- 	{
- 		free_tbl_list(dbi->table_list);
- 		dbi->table_list = NULL;
- 	}
if (dbi)
{
free(dbi);
--- 510,515 ----
***************
*** 664,670 ****
/* Function is called before program exit to free all memory
mostly it's just to keep valgrind happy */
! static void
free_db_list(Dllist *db_list)
{
Dlelem	   *db_elem = DLGetHead(db_list);
--- 520,526 ----
/* Function is called before program exit to free all memory
mostly it's just to keep valgrind happy */
! void
free_db_list(Dllist *db_list)
{
Dlelem *db_elem = DLGetHead(db_list);
***************
*** 680,686 ****
DLFreeList(db_list);
}
! static void
print_db_list(Dllist *db_list, int print_table_lists)
{
Dlelem	   *db_elem = DLGetHead(db_list);
--- 536,542 ----
DLFreeList(db_list);
}
! void
print_db_list(Dllist *db_list, int print_table_lists)
{
Dlelem *db_elem = DLGetHead(db_list);
***************
*** 692,726 ****
}
}

! static void
print_db_info(db_info * dbi, int print_tbl_list)
{
! sprintf(logbuffer, "dbname: %s", (dbi->dbname) ? dbi->dbname : "(null)");
! log_entry(logbuffer);
!
! sprintf(logbuffer, " oid: %u", dbi->oid);
! log_entry(logbuffer);
!
! sprintf(logbuffer, " username: %s", (dbi->username) ? dbi->username : "(null)");
! log_entry(logbuffer);
!
! sprintf(logbuffer, " password: %s", (dbi->password) ? dbi->password : "(null)");
! log_entry(logbuffer);

if (dbi->conn != NULL)
! log_entry(" conn is valid, (connected)");
else
! log_entry(" conn is null, (not connected)");
!
! sprintf(logbuffer, " default_analyze_threshold: %li", dbi->analyze_threshold);
! log_entry(logbuffer);
!
! sprintf(logbuffer, " default_vacuum_threshold: %li", dbi->vacuum_threshold);
! log_entry(logbuffer);
!
! fflush(LOGOUTPUT);
! if (print_tbl_list > 0)
! print_table_list(dbi->table_list);
}
/* End of DB List Management Function */
--- 548,565 ----
}
}
! void
print_db_info(db_info * dbi, int print_tbl_list)
{
! ereport(DEBUG3,(errmsg("pg_autovacuum: dbname = %s", (dbi->dbname) ? dbi->dbname : "(null)")));
! ereport(DEBUG3,(errmsg(" oid: %u", dbi->oid)));
! ereport(DEBUG3,(errmsg(" username: %s", (dbi->username) ? dbi->username : "(null)")));
! ereport(DEBUG3,(errmsg(" password: %s", (dbi->password) ? dbi->password : "(null)")));

if (dbi->conn != NULL)
! ereport(DEBUG3,(errmsg(" conn is valid, (connected)")));
else
! ereport(DEBUG3,(errmsg(" conn is null, (not connected)")));
}

/* End of DB List Management Function */
***************
*** 728,753 ****
/* Beginning of misc Functions */

/* Perhaps add some test to this function to make sure that the stats we need are available */
! static PGconn *
db_connect(db_info * dbi)
{
PGconn *db_conn =
! PQsetdbLogin(args->host, args->port, NULL, NULL, dbi->dbname,
dbi->username, dbi->password);

if (PQstatus(db_conn) != CONNECTION_OK)
{
! sprintf(logbuffer, "Failed connection to database %s with error: %s.",
! dbi->dbname, PQerrorMessage(db_conn));
! log_entry(logbuffer);
! fflush(LOGOUTPUT);
PQfinish(db_conn);
db_conn = NULL;
}
return db_conn;
} /* end of db_connect() */
! static void
db_disconnect(db_info * dbi)
{
if (dbi->conn != NULL)
--- 567,590 ----
/* Beginning of misc Functions */
/* Perhaps add some test to this function to make sure that the stats we need are available */
! PGconn *
db_connect(db_info * dbi)
{
PGconn *db_conn =
! PQsetdbLogin(NULL,NULL, NULL, NULL, dbi->dbname,
dbi->username, dbi->password);

if (PQstatus(db_conn) != CONNECTION_OK)
{
! ereport(LOG,(errmsg("pg_autovacuum: Failed connection to database %s with error: %s.",
! dbi->dbname, PQerrorMessage(db_conn))));
PQfinish(db_conn);
db_conn = NULL;
}
return db_conn;
} /* end of db_connect() */

! void
db_disconnect(db_info * dbi)
{
if (dbi->conn != NULL)
***************
*** 757,778 ****
}
}
! static int
! check_stats_enabled(db_info * dbi)
! {
! 	PGresult   *res;
! 	int			ret = 0;
! 
! 	res = send_query("SHOW stats_row_level", dbi);
! 	if (res != NULL)
! 	{
! 		ret = strcmp("on", PQgetvalue(res, 0, PQfnumber(res, "stats_row_level")));
! 		PQclear(res);
! 	}
! 	return ret;
! }
! 
! static PGresult *
send_query(const char *query, db_info * dbi)
{
PGresult   *res;
--- 594,600 ----
}
}
! PGresult *
send_query(const char *query, db_info * dbi)
{
PGresult *res;
***************
*** 780,811 ****
if (dbi->conn == NULL)
return NULL;

! if (args->debug >= 4)
! log_entry(query);

res = PQexec(dbi->conn, query);
if (!res)
{
! 		sprintf(logbuffer,
! 		   "Fatal error occured while sending query (%s) to database %s",
! 				query, dbi->dbname);
! 		log_entry(logbuffer);
! 		sprintf(logbuffer, "The error is [%s]", PQresultErrorMessage(res));
! 		log_entry(logbuffer);
! 		fflush(LOGOUTPUT);
return NULL;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK &&
PQresultStatus(res) != PGRES_COMMAND_OK)
{
! 		sprintf(logbuffer,
! 		  "Can not refresh statistics information from the database %s.",
! 				dbi->dbname);
! 		log_entry(logbuffer);
! 		sprintf(logbuffer, "The error is [%s]", PQresultErrorMessage(res));
! 		log_entry(logbuffer);
! 		fflush(LOGOUTPUT);
PQclear(res);
return NULL;
}
--- 602,622 ----
if (dbi->conn == NULL)
return NULL;
! elog(DEBUG5, query);

res = PQexec(dbi->conn, query);

if (!res)
{
! ereport(ERROR,(errmsg("pg_autovacuum: Fatal error occured while sending query (%s) to database %s; The error is [%s]",
! query, dbi->dbname, PQresultErrorMessage(res))));
return NULL;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK &&
PQresultStatus(res) != PGRES_COMMAND_OK)
{
! ereport(ERROR,(errmsg("pg_autovacuum: Fatal error occured while sending query (%s) to database %s; The error is [%s]",
! query, dbi->dbname, PQresultErrorMessage(res))));
PQclear(res);
return NULL;
}
***************
*** 813,1055 ****
} /* End of send_query() */

static void
! free_cmd_args()
{
! if (args != NULL)
{
! if (args->user != NULL)
! free(args->user);
! if (args->password != NULL)
! free(args->password);
! free(args);
}
! }
!
! static cmd_args *
! get_cmd_args(int argc, char *argv[])
! {
! int c;

! args = (cmd_args *) malloc(sizeof(cmd_args));
! args->sleep_base_value = SLEEPBASEVALUE;
! args->sleep_scaling_factor = SLEEPSCALINGFACTOR;
! args->vacuum_base_threshold = VACBASETHRESHOLD;
! args->vacuum_scaling_factor = VACSCALINGFACTOR;
! args->analyze_base_threshold = -1;
! args->analyze_scaling_factor = -1;
! args->debug = AUTOVACUUM_DEBUG;
! args->daemonize = 0;
! args->user = 0;
! args->password = 0;
! args->host = 0;
! args->logfile = 0;
! args->port = 0;

! /*
! * Fixme: Should add some sanity checking such as positive integer
! * values etc
! */
! while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hD")) != -1)
{
! switch (c)
! {
! case 's':
! args->sleep_base_value = atoi(optarg);
! break;
! case 'S':
! args->sleep_scaling_factor = atof(optarg);
! break;
! case 'v':
! args->vacuum_base_threshold = atoi(optarg);
! break;
! case 'V':
! args->vacuum_scaling_factor = atof(optarg);
! break;
! case 'a':
! args->analyze_base_threshold = atoi(optarg);
! break;
! case 'A':
! args->analyze_scaling_factor = atof(optarg);
! break;
! case 'D':
! args->daemonize++;
! break;
! case 'd':
! args->debug = atoi(optarg);
! break;
! case 'U':
! args->user = optarg;
! break;
! case 'P':
! args->password = optarg;
! break;
! case 'H':
! args->host = optarg;
! break;
! case 'L':
! args->logfile = optarg;
! break;
! case 'p':
! args->port = optarg;
! break;
! case 'h':
! usage();
! exit(0);
! default:
!
! /*
! * It's here that we know that things are invalid... It is
! * not forcibly an error to call usage
! */
! fprintf(stderr, "Error: Invalid Command Line Options.\n");
! usage();
! exit(1);
! break;
! }

! /*
! * if values for insert thresholds are not specified, then they
! * default to 1/2 of the delete values
! */
! if (args->analyze_base_threshold == -1)
! args->analyze_base_threshold = args->vacuum_base_threshold / 2;
! if (args->analyze_scaling_factor == -1)
! args->analyze_scaling_factor = args->vacuum_scaling_factor / 2;
}
! return args;
! }

! static void
! usage()
! {
! int i = 0;
! float f = 0;

! fprintf(stderr, "usage: pg_autovacuum \n");
! fprintf(stderr, " [-D] Daemonize (Detach from tty and run in the background)\n");
! i = AUTOVACUUM_DEBUG;
! fprintf(stderr, " [-d] debug (debug level=0,1,2,3; default=%i)\n", i);
!
! i = SLEEPBASEVALUE;
! fprintf(stderr, " [-s] sleep base value (default=%i)\n", i);
! f = SLEEPSCALINGFACTOR;
! fprintf(stderr, " [-S] sleep scaling factor (default=%f)\n", f);
!
! i = VACBASETHRESHOLD;
! fprintf(stderr, " [-v] vacuum base threshold (default=%i)\n", i);
! f = VACSCALINGFACTOR;
! fprintf(stderr, " [-V] vacuum scaling factor (default=%f)\n", f);
! i = i / 2;
! fprintf(stderr, " [-a] analyze base threshold (default=%i)\n", i);
! f = f / 2;
! fprintf(stderr, " [-A] analyze scaling factor (default=%f)\n", f);
!
! fprintf(stderr, " [-L] logfile (default=none)\n");
!
! fprintf(stderr, " [-U] username (libpq default)\n");
! fprintf(stderr, " [-P] password (libpq default)\n");
! fprintf(stderr, " [-H] host (libpq default)\n");
! fprintf(stderr, " [-p] port (libpq default)\n");

! fprintf(stderr, " [-h] help (Show this output)\n");
! }

! static void
! print_cmd_args()
! {
! sprintf(logbuffer, "Printing command_args");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->host=%s", (args->host) ? args->host : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->port=%s", (args->port) ? args->port : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->username=%s", (args->user) ? args->user : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->password=%s", (args->password) ? args->password : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->logfile=%s", (args->logfile) ? args->logfile : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->daemonize=%i", args->daemonize);
! log_entry(logbuffer);
!
! sprintf(logbuffer, " args->sleep_base_value=%i", args->sleep_base_value);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->sleep_scaling_factor=%f", args->sleep_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->vacuum_base_threshold=%i", args->vacuum_base_threshold);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->vacuum_scaling_factor=%f", args->vacuum_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->analyze_base_threshold=%i", args->analyze_base_threshold);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->analyze_scaling_factor=%f", args->analyze_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->debug=%i", args->debug);
! log_entry(logbuffer);

- fflush(LOGOUTPUT);
}

/* Beginning of AutoVacuum Main Program */
! int
! main(int argc, char *argv[])
{
char buf[256];
int j = 0,
loops = 0;
-
- /* int numInserts, numDeletes, */
int sleep_secs;
Dllist *db_list;
! Dlelem *db_elem,
! *tbl_elem;
db_info *dbs;
- tbl_info *tbl;
PGresult *res = NULL;
double diff;
struct timeval now,
then;
!
! args = get_cmd_args(argc, argv); /* Get Command Line Args and put
! * them in the args struct */
!
! /* Dameonize if requested */
! if (args->daemonize == 1)
! daemonize();
!
! if (args->logfile)
! {
! LOGOUTPUT = fopen(args->logfile, "a");
! if (!LOGOUTPUT)
! {
! fprintf(stderr, "Could not open log file - [%s]\n", args->logfile);
! exit(-1);
! }
! }
! else
! LOGOUTPUT = stderr;
! if (args->debug >= 2)
! print_cmd_args();
!
/* Init the db list with template1 */
db_list = init_db_list();
if (db_list == NULL)
! return 1;

- if (check_stats_enabled(((db_info *) DLE_VAL(DLGetHead(db_list)))) != 0)
- {
- log_entry("Error: GUC variable stats_row_level must be enabled.");
- log_entry(" Please fix the problems and try again.");
- fflush(LOGOUTPUT);
-
- exit(1);
- }
-
gettimeofday(&then, 0); /* for use later to caluculate sleep time */

while (1)
! { /* Main Loop */
db_elem = DLGetHead(db_list); /* Reset cur_db_node to the
* beginning of the db_list */
--- 624,768 ----
}	/* End of send_query() */
+ /* Get a password from the password file. */
static void
! GetAutovacuumUserAndPasswordFromFile()
{
! FILE *fp;
! #define LINELEN NAMEDATALEN*2
! char buf[LINELEN];
! char *pgpassfile,
! *t = buf,
! *username,
! *password;
! int len;
! struct stat stat_buf;
!
!
! pgpassfile = malloc(strlen(DataDir) + 1 + strlen(AUTOVACPASSFILE) + 1);
! if (!pgpassfile)
{
! printf("out of memory");
! return;
}
! elog(DEBUG3,"pg_autovacuum: autovac.passwd should be: %s/%s", DataDir, AUTOVACPASSFILE);

! sprintf(pgpassfile, "%s/%s", DataDir, AUTOVACPASSFILE);

! /* If password file cannot be opened, ignore it. */
! if (stat(pgpassfile, &stat_buf) == -1)
{
! elog(DEBUG3,"pg_autovacuum: %s cannot be opened",pgpassfile);
! free(pgpassfile);
! return;
! }

! #ifndef WIN32
! /* If password file is insecure, alert the user and ignore it. */
! if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))
! {
! ereport(WARNING,(errmsg("pg_autovacuum: Password file %s has world or group read access; permission should be u=rw (0600)",pgpassfile)));
! free(pgpassfile);
! return;
}
! #endif

! fp = fopen(pgpassfile, "r");
! free(pgpassfile);
! if (fp == NULL)
! return;

! fgets(buf, LINELEN - 1, fp);

! len = strlen(buf);
! if (len < 3)
! return;
!
! /* Remove trailing newline */
! if (buf[len - 1] == '\n')
! buf[len - 1] = 0;

! username = t; /* Password is always first */
! if(*username == ':')
! username = NULL;
!
! password = NULL;
! while(*t != NULL) /* Find : since it marks the beginning of the password */
! {
! if (*(t+1) == ':' )
! {
! *(t+1) = NULL;
! password = t+2;
! }
! t++;
! }
!
! ereport(DEBUG3,(errmsg("pg_autovacuum: username from autovac.passwd file is: %s ",username)));
! fclose(fp);
!
! /* Now if we have anything for usename and password we set the pg_user and pg_passwd globals */
! if((username != NULL) && (strlen(username) > 1) && (strlen(username) < NAMEDATALEN))
! {
! pg_user = malloc(strlen(username) + 1);
! strcpy(pg_user,username);
! }
! if((password != NULL) && (strlen(password) > 1) && (strlen(password) < NAMEDATALEN))
! {
! pg_user_password = malloc(strlen(password) + 1);
! strcpy(pg_user_password,password);
! }
! return;
!
! #undef LINELEN

}

+
/* Beginning of AutoVacuum Main Program */
! void AutoVacLoop(void)
{
char buf[256];
int j = 0,
loops = 0;
int sleep_secs;
Dllist *db_list;
! Dlelem *db_elem;
db_info *dbs;
PGresult *res = NULL;
double diff;
struct timeval now,
then;
!
! GetAutovacuumUserAndPasswordFromFile();
!
/* Init the db list with template1 */
db_list = init_db_list();
if (db_list == NULL)
! ereport(ERROR,(errmsg("pg_autovacuum: Error creating db_list, exiting.")));

gettimeofday(&then, 0); /* for use later to caluculate sleep time */

+ /* Main Loop */
while (1)
! {
! /*
! * Emergency bailout if postmaster has died. This is to avoid the
! * necessity for manual cleanup of all postmaster children.
! */
! if (!PostmasterIsAlive(true))
! exit(1);
!
! if (got_SIGHUP)
! {
! got_SIGHUP = false;
! ProcessConfigFile(PGC_SIGHUP);
! }
! if (shutdown_requested)
! {
! /* Normal exit from pg_autovacuum is here */
! proc_exit(0); /* done */
! }
!
db_elem = DLGetHead(db_list); /* Reset cur_db_node to the
* beginning of the db_list */

***************
*** 1061,1095 ****
if (dbs->conn == NULL)
{ /* Serious problem: We can't connect to
* template1 */
! log_entry("Error: Cannot connect to template1, exiting.");
! fflush(LOGOUTPUT);
! fclose(LOGOUTPUT);
! exit(1);
}
}
! 		if (loops % UPDATE_INTERVAL == 0)		/* Update the list if it's
! 												 * time */
! 			update_db_list(db_list);	/* Add and remove databases from
! 										 * the list */
! 
while (db_elem != NULL)
! 		{						/* Loop through databases in list */
dbs = ((db_info *) DLE_VAL(db_elem));		/* get pointer to
* cur_db's db_info
* struct */
if (dbs->conn == NULL)
dbs->conn = db_connect(dbs);
! 
if (dbs->conn != NULL)
{
- 				if (loops % UPDATE_INTERVAL == 0)		/* Update the list if
- 														 * it's time */
- 					update_table_list(dbs);		/* Add and remove tables
- 												 * from the list */
- 
if (xid_wraparound_check(dbs) == 0)
{
res = send_query(TABLE_STATS_QUERY, dbs);	/* Get an updated
* snapshot of this dbs
* table stats */
--- 774,806 ----
if (dbs->conn == NULL)
{					/* Serious problem: We can't connect to
* template1 */
! 				ereport(ERROR,(errmsg("pg_autovacuum: Cannot connect to template1, exiting.")));
}
}
! 		update_db_list(db_list);	/* Add and remove databases from
! 									 * the list */
! 		
! 		/* Loop through databases in list */
while (db_elem != NULL)
! 		{
dbs = ((db_info *) DLE_VAL(db_elem));		/* get pointer to
* cur_db's db_info
* struct */
if (dbs->conn == NULL)
dbs->conn = db_connect(dbs);
! 				
if (dbs->conn != NULL)
{
if (xid_wraparound_check(dbs) == 0)
{
+ 					long curr_vacuum_count, curr_analyze_count;
+ 					long cnt_at_last_vacuum, cnt_at_last_analyze;
+ 					float vacuum_threshold, analyze_threshold;
+ 					char table_name[512];
+ 					
+ 					update_pg_autovacuum_table(dbs); 
+ 						
res = send_query(TABLE_STATS_QUERY, dbs);	/* Get an updated
* snapshot of this dbs
* table stats */
***************
*** 1097,1174 ****
{
for (j = 0; j < PQntuples(res); j++)
{			/* loop through result set */
! 							tbl_elem = DLGetHead(dbs->table_list);	/* Reset tbl_elem to top
! 																	* of dbs->table_list */
! 							while (tbl_elem != NULL)
! 							{		/* Loop through tables in list */
! 								tbl = ((tbl_info *) DLE_VAL(tbl_elem));		/* set tbl_info =
! 																			* current_table */
! 								if (tbl->relid == atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))))
! 								{
! 									tbl->curr_analyze_count =
! 										(atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_ins"))) +
! 										atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_upd"))) +
! 										atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_del"))));
! 									tbl->curr_vacuum_count =
! 										(atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_del"))) +
! 										atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_upd"))));
! 	
! 									/*
! 									* Check numDeletes to see if we need to
! 									* vacuum, if so: Run vacuum analyze
! 									* (adding analyze is small so we might as
! 									* well) Update table thresholds and
! 									* related information if numDeletes is
! 									* not big enough for vacuum then check
! 									* numInserts for analyze
! 									*/
! 									if (tbl->curr_vacuum_count - tbl->CountAtLastVacuum >= tbl->vacuum_threshold)
! 									{
! 										/*
! 										* if relisshared = t and database !=
! 										* template1 then only do an analyze
! 										*/
! 										if (tbl->relisshared > 0 && strcmp("template1", dbs->dbname))
! 											snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
! 										else
! 											snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
! 										if (args->debug >= 1)
! 										{
! 											sprintf(logbuffer, "Performing: %s", buf);
! 											log_entry(logbuffer);
! 											fflush(LOGOUTPUT);
! 										}
! 										send_query(buf, dbs);
! 										update_table_thresholds(dbs, tbl, VACUUM_ANALYZE);
! 										if (args->debug >= 2)
! 											print_table_info(tbl);
! 									}
! 									else if (tbl->curr_analyze_count - tbl->CountAtLastAnalyze >= tbl->analyze_threshold)
! 									{
! 										snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
! 										if (args->debug >= 1)
! 										{
! 											sprintf(logbuffer, "Performing: %s", buf);
! 											log_entry(logbuffer);
! 											fflush(LOGOUTPUT);
! 										}
! 										send_query(buf, dbs);
! 										update_table_thresholds(dbs, tbl, ANALYZE_ONLY);
! 										if (args->debug >= 2)
! 											print_table_info(tbl);
! 									}
! 									
! 									break;	/* once we have found a match, no
! 											* need to keep checking. */
! 								}
! 								
/*
! 								* Advance the table pointers for the next
! 								* loop
*/
! 								tbl_elem = DLGetSucc(tbl_elem);
! 							}		/* end for table while loop */
}			/* end for j loop (tuples in PGresult) */
}			/* end if (res != NULL) */
}				/* close of if(xid_wraparound_check()) */
--- 808,877 ----
{
for (j = 0; j < PQntuples(res); j++)
{			/* loop through result set */
! 							/*
! 							* Check to see if we need to
! 							* vacuum analyze, or just analyze
! 							* if needed, do so, then update table thresholds and
! 							* related information
! 							*/
! 							curr_vacuum_count = (atol(PQgetvalue(res, j, PQfnumber(res, "curr_vacuum_count"))));
! 							curr_analyze_count = (atol(PQgetvalue(res, j, PQfnumber(res, "curr_analyze_count"))));
! 							cnt_at_last_vacuum = (atol(PQgetvalue(res, j, PQfnumber(res, "cnt_at_last_vacuum"))));
! 							cnt_at_last_analyze = (atol(PQgetvalue(res, j, PQfnumber(res, "cnt_at_last_analyze"))));
! 							vacuum_threshold = (atof(PQgetvalue(res, j, PQfnumber(res, "vacuum_threshold"))));
! 							analyze_threshold = (atof(PQgetvalue(res, j, PQfnumber(res, "analyze_threshold"))));
! 							strcpy(table_name,"\"");
! 							strcat(table_name, PQgetvalue(res,j, PQfnumber(res, "schemaname")));
! 							strcat(table_name,"\".\"");
! 							strcat(table_name, PQgetvalue(res,j, PQfnumber(res, "relname")));
! 							strcat(table_name,"\"");
! 							
! 							if ((curr_vacuum_count - cnt_at_last_vacuum) >= vacuum_threshold)
! 							{
/*
! 								* if relisshared = t and database !=
! 								* template1 then only do an analyze
*/
! 								if ((strcmp("t",PQgetvalue(res,j, PQfnumber(res, "relisshared"))) == 0 ) && (strcmp(dbs->dbname,"template1") == 0 ))
! 									snprintf(buf, sizeof(buf), "ANALYZE %s", table_name);
! 								else
! 									snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", table_name);
! 								
! 								ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
! 								
! 								send_query(buf, dbs);
! 								update_table_thresholds(dbs, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))), VACUUM_ANALYZE);
! 							}
! 							else if (curr_analyze_count - cnt_at_last_analyze >= analyze_threshold)
! 							{
! 								snprintf(buf, sizeof(buf), "ANALYZE %s", table_name);
! 								
! 								ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
! 								
! 								send_query(buf, dbs);
! 								update_table_thresholds(dbs, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))), ANALYZE_ONLY);
! 							}
! 							
! 							if (curr_vacuum_count < cnt_at_last_vacuum)
! 							{
! 								/*
! 								 * this should only happen if the stats system gets reset
! 								 */
! 								snprintf(buf, sizeof(buf), "update pg_autovacuum set cnt_at_last_vacuum = %li where table_oid = %u", curr_vacuum_count, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))));
! 								
! 								ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
! 								send_query(buf, dbs);
! 							}
! 							if (curr_analyze_count < cnt_at_last_analyze)
! 							{
! 								/*
! 								 * this should only happen if the stats system gets reset
! 								 */
! 								snprintf(buf, sizeof(buf), "update pg_autovacuum set cnt_at_last_analyze = %li where table_oid = %u", curr_analyze_count, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))));
! ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
! send_query(buf, dbs);
! }
} /* end for j loop (tuples in PGresult) */
} /* end if (res != NULL) */
} /* close of if(xid_wraparound_check()) */
***************
*** 1179,1212 ****
}
db_elem = DLGetSucc(db_elem); /* move on to next DB
* regardless */
! } /* end of db_list while loop */
!
/* Figure out how long to sleep etc ... */
gettimeofday(&now, 0);
diff = (int) (now.tv_sec - then.tv_sec) * 1000000.0 + (int) (now.tv_usec - then.tv_usec);
!
! sleep_secs = args->sleep_base_value + args->sleep_scaling_factor * diff / 1000000.0;
loops++;
! if (args->debug >= 2)
! {
! sprintf(logbuffer,
! "%i All DBs checked in: %.0f usec, will sleep for %i secs.",
! loops, diff, sleep_secs);
! log_entry(logbuffer);
! fflush(LOGOUTPUT);
! }
!
! sleep(sleep_secs); /* Larger Pause between outer loops */
!
gettimeofday(&then, 0); /* Reset time counter */
!
! } /* end of while loop */
/*
* program is exiting, this should never run, but is here to make
* compiler / valgrind happy
*/
free_db_list(db_list);
! 	free_cmd_args();
! 	return EXIT_SUCCESS;
}
--- 882,911 ----
}
db_elem = DLGetSucc(db_elem);		/* move on to next DB
* regardless */
! 		}	/* end of db_list while loop */
! 		
/* Figure out how long to sleep etc ... */
gettimeofday(&now, 0);
diff = (int) (now.tv_sec - then.tv_sec) * 1000000.0 + (int) (now.tv_usec - then.tv_usec);
! 		
! 		sleep_secs = SLEEPBASEVALUE + SLEEPSCALINGFACTOR * diff / 1000000.0;
loops++;
! 			
! 		ereport(DEBUG2,(errmsg("pg_autovacuum: loop %i; All DBs checked in: %.0f usec, will sleep for %i secs.",
! 				loops, diff, sleep_secs)));
! 		
! 		/* Larger Pause between outer loops */
! 		if (!(got_SIGHUP || shutdown_requested))
! 			pg_usleep((long)(sleep_secs * 1000000L));
! 		
gettimeofday(&then, 0); /* Reset time counter */
! 		
! 	}	/* end of while loop */
/*
* program is exiting, this should never run, but is here to make
* compiler / valgrind happy
*/
free_db_list(db_list);
! 	proc_exit(0);
}
*** ./src/backend/postmaster/Makefile.orig	2004-08-04 01:40:53.455982146 -0400
--- ./src/backend/postmaster/Makefile	2004-07-24 02:00:34.000000000 -0400
***************
*** 12,18 ****
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o

all: SUBSYS.o
--- 12,18 ----
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o autovacuum.o

all: SUBSYS.o
*** ./src/backend/postmaster/postmaster.c.orig	2004-08-04 01:40:40.707380679 -0400
--- ./src/backend/postmaster/postmaster.c	2004-08-03 21:55:54.578203673 -0400
***************
*** 55,66 ****
*		The Postmaster cleans up after backends if they have an emergency
*		exit and/or core dump.
*
-  * Error Reporting:
-  *		Use write_stderr() only for reporting "interactive" errors
-  *		(essentially, bogus arguments on the command line).  Once the
-  *		postmaster is launched, use ereport().  In particular, don't use
-  *		write_stderr() for anything that occurs after pmdaemonize.
-  *
*-------------------------------------------------------------------------
*/
--- 55,60 ----
***************
*** 198,203 ****
--- 192,198 ----
/* PIDs of special child processes; 0 when not running */
static pid_t StartupPID = 0,
BgWriterPID = 0,
+ 			AutoVacPID = 0,
PgArchPID = 0,
PgStatPID = 0;
***************
*** 268,273 ****
--- 263,272 ----
static int	CountChildren(void);
static bool CreateOptsFile(int argc, char *argv[], char *fullprogname);
static pid_t StartChildProcess(int xlop);
+ static void
+ postmaster_error(const char *fmt,...)
+ /* This lets gcc check the format string for consistency. */
+ __attribute__((format(printf, 1, 2)));
#ifdef EXEC_BACKEND
***************
*** 298,303 ****
--- 297,303 ----
#define StartupDataBase() StartChildProcess(BS_XLOG_STARTUP)
#define StartBackgroundWriter() StartChildProcess(BS_XLOG_BGWRITER)
+ #define StartAutoVac() StartChildProcess(BS_XLOG_AUTOVAC)
/*
***************
*** 384,390 ****
#ifdef USE_ASSERT_CHECKING
SetConfigOption("debug_assertions", optarg, PGC_POSTMASTER, PGC_S_ARGV);
#else
! 				write_stderr("%s: assert checking is not compiled in\n", progname);
#endif
break;
case 'a':
--- 384,390 ----
#ifdef USE_ASSERT_CHECKING
SetConfigOption("debug_assertions", optarg, PGC_POSTMASTER, PGC_S_ARGV);
#else
! 				postmaster_error("assert checking is not compiled in");
#endif
break;
case 'a':
***************
*** 508,515 ****
}
default:
! 				write_stderr("Try \"%s --help\" for more information.\n",
! 							 progname);
ExitPostmaster(1);
}
}
--- 508,516 ----
}
default:
! fprintf(stderr,
! gettext("Try \"%s --help\" for more information.\n"),
! progname);
ExitPostmaster(1);
}
}
***************
*** 519,528 ****
*/
if (optind < argc)
{
! write_stderr("%s: invalid argument: \"%s\"\n",
! progname, argv[optind]);
! write_stderr("Try \"%s --help\" for more information.\n",
! progname);
ExitPostmaster(1);
}
--- 520,529 ----
*/
if (optind < argc)
{
! 		postmaster_error("invalid argument: \"%s\"", argv[optind]);
! 		fprintf(stderr,
! 				gettext("Try \"%s --help\" for more information.\n"),
! 				progname);
ExitPostmaster(1);
}
***************
*** 593,605 ****
* for lack of buffers. The specific choices here are somewhat
* arbitrary.
*/
! write_stderr("%s: the number of buffers (-B) must be at least twice the number of allowed connections (-N) and at least 16\n", progname);
ExitPostmaster(1);
}

if (ReservedBackends >= MaxBackends)
{
! write_stderr("%s: superuser_reserved_connections must be less than max_connections\n", progname);
ExitPostmaster(1);
}
--- 594,606 ----
* for lack of buffers.  The specific choices here are somewhat
* arbitrary.
*/
! 		postmaster_error("the number of buffers (-B) must be at least twice the number of allowed connections (-N) and at least 16");
ExitPostmaster(1);
}
if (ReservedBackends >= MaxBackends)
{
! postmaster_error("superuser_reserved_connections must be less than max_connections");
ExitPostmaster(1);
}

***************
*** 608,614 ****
*/
if (!CheckDateTokenTables())
{
! write_stderr("%s: invalid datetoken tables, please fix\n", progname);
ExitPostmaster(1);
}
--- 609,615 ----
*/
if (!CheckDateTokenTables())
{
! 		postmaster_error("invalid datetoken tables, please fix");
ExitPostmaster(1);
}
***************
*** 827,832 ****
--- 828,835 ----
*
* CAUTION: when changing this list, check for side-effects on the signal
* handling setup of child processes.  See tcop/postgres.c,
+ 	 * bootstrap/bootstrap.c, postmaster/bgwriter.c, postmaster/pgstat.c,
+ 	 * and postmaster/autovacuum.c.
* bootstrap/bootstrap.c, postmaster/bgwriter.c, postmaster/pgarch.c,
* and postmaster/pgstat.c.
*/
***************
*** 977,986 ****
fp = AllocateFile(path, PG_BINARY_R);
if (fp == NULL)
{
! 		write_stderr("%s: could not find the database system\n"
! 					 "Expected to find it in the directory \"%s\",\n"
! 					 "but could not open file \"%s\": %s\n",
! 					 progname, checkdir, path, strerror(errno));
ExitPostmaster(2);
}
FreeFile(fp);
--- 980,990 ----
fp = AllocateFile(path, PG_BINARY_R);
if (fp == NULL)
{
! 		fprintf(stderr,
! 				gettext("%s: could not find the database system\n"
! 						"Expected to find it in the directory \"%s\",\n"
! 						"but could not open file \"%s\": %s\n"),
! 				progname, checkdir, path, strerror(errno));
ExitPostmaster(2);
}
FreeFile(fp);
***************
*** 1023,1030 ****
pid = fork();
if (pid == (pid_t) -1)
{
! 		write_stderr("%s: could not fork background process: %s\n",
! 					 progname, strerror(errno));
ExitPostmaster(1);
}
else if (pid)
--- 1027,1034 ----
pid = fork();
if (pid == (pid_t) -1)
{
! 		postmaster_error("could not fork background process: %s",
! 						 strerror(errno));
ExitPostmaster(1);
}
else if (pid)
***************
*** 1045,1052 ****
#ifdef HAVE_SETSID
if (setsid() < 0)
{
! 		write_stderr("%s: could not dissociate from controlling TTY: %s\n",
! 					 progname, strerror(errno));
ExitPostmaster(1);
}
#endif
--- 1049,1056 ----
#ifdef HAVE_SETSID
if (setsid() < 0)
{
! 		postmaster_error("could not dissociate from controlling TTY: %s",
! 						 strerror(errno));
ExitPostmaster(1);
}
#endif
***************
*** 1113,1121 ****
int			nSockets;
time_t		now,
last_touch_time;
! 	struct timeval earlier,
later;
! 	struct timezone tz;
gettimeofday(&earlier, &tz);
last_touch_time = time(NULL);
--- 1117,1125 ----
int			nSockets;
time_t		now,
last_touch_time;
! 	struct 		timeval earlier,
later;
! 	struct 		timezone tz;
gettimeofday(&earlier, &tz);
last_touch_time = time(NULL);
***************
*** 1220,1225 ****
--- 1224,1252 ----
kill(BgWriterPID, SIGUSR2);
}
+ 		/*
+ 		 * If no AutoVacuum process is running, and we are not in
+ 		 * a state that prevents it, start one.  It doesn't matter if this
+ 		 * fails, we'll just try again later.
+ 		 */
+ 		if (autovacuum_start_daemon)
+ 		{
+ 			if (pgstat_collect_tuplelevel)
+ 			{
+ 				if (AutoVacPID == 0 && StartupPID == 0 && !FatalError)
+ 				{
+ 					AutoVacPID = StartAutoVac();
+ 					
+ 					/* If shutdown is pending, set it going */
+ 					if (Shutdown > NoShutdown && AutoVacPID != 0)
+ 						kill(AutoVacPID, SIGUSR2);
+ 				}
+ 			}
+ 		}	
+ 		else if(AutoVacPID > 0)
+ 			kill(AutoVacPID, SIGUSR2);
+ 			
+ 		
/* If we have lost the archiver, try to start a new one */
if (XLogArchivingActive() && PgArchPID == 0 && 
StartupPID == 0 && !FatalError && Shutdown == NoShutdown)
***************
*** 1587,1592 ****
--- 1614,1634 ----
backendPID = (int) ntohl(canc->backendPID);
cancelAuthCode = (long) ntohl(canc->cancelAuthCode);
+ 	if (backendPID == BgWriterPID)
+ 	{
+ 		ereport(DEBUG2,
+ 				(errmsg_internal("ignoring cancel request for bgwriter process %d",
+ 								 backendPID)));
+ 		return;
+ 	}
+ 	if (backendPID == AutoVacPID)
+ 	{
+ 		ereport(DEBUG2,
+ 				(errmsg_internal("ignoring cancel request for autovacuum process %d",
+ 								 backendPID)));
+ 		return;
+ 	}
+ 
/*
* See if we have a matching backend.  In the EXEC_BACKEND case, we
* can no longer access the postmaster's own backend list, and must
***************
*** 1768,1773 ****
--- 1810,1817 ----
SignalChildren(SIGHUP);
if (BgWriterPID != 0)
kill(BgWriterPID, SIGHUP);
+ 		if (AutoVacPID != 0)
+ 			kill(AutoVacPID, SIGHUP);
if (PgArchPID != 0)
kill(PgArchPID, SIGHUP);
/* PgStatPID does not currently need SIGHUP */
***************
*** 1828,1833 ****
--- 1872,1881 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+ 			/* I don't think we need to Start the autovac process if not running */
+ 			/* And tell it to shut down */
+ 			if (AutoVacPID != 0)
+ 				kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 1875,1880 ****
--- 1923,1931 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+ 			/* And tell it to shut down */
+ 			if (AutoVacPID != 0)
+ 				kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 1896,1901 ****
--- 1947,1954 ----
kill(StartupPID, SIGQUIT);
if (BgWriterPID != 0)
kill(BgWriterPID, SIGQUIT);
+ 			if (AutoVacPID != 0)
+ 				kill(AutoVacPID, SIGQUIT);
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
if (PgStatPID != 0)
***************
*** 1984,1989 ****
--- 2037,2055 ----
BgWriterPID = StartBackgroundWriter();
/*
+ 			 *  We won't start autovac here since we are in no hurry to start it up
+ 			 *  But we will take this opportunity to make some noise about invalid
+ 			 *  combinations of options in postgresql.conf
+ 			 */
+ 			if (autovacuum_start_daemon)
+ 			{
+ 				if (!pgstat_collect_tuplelevel)
+ 					ereport(WARNING,(errmsg("pg_autovacuum: autovac is enabled, but requires stats_row_level which is not enabled")));
+ 				if (pgstat_collect_resetonpmstart)
+ 					ereport(WARNING,(errmsg("pg_autovacuum: stats_reset_on_server_start should be disabled for optimal performance")));
+ 			}	
+ 			 
+ 			/*
* Go to shutdown mode if a shutdown request was pending.
* Otherwise, try to start the archiver and stats collector too.
*/
***************
*** 1996,2001 ****
--- 2062,2073 ----
PgStatPID = pgstat_start();
}
+ 			/*
+ 			 * Shutdown autovac if a shutdown request was pending.
+ 			 */
+ 			if (Shutdown > NoShutdown && AutoVacPID != 0)
+ 				kill(AutoVacPID, SIGUSR2);
+ 			
continue;
}
***************
*** 2032,2037 ****
--- 2104,2129 ----
}
/*
+ 		 * Was it the autovac?
+ 		 */
+ 		if (AutoVacPID != 0 && pid == AutoVacPID)
+ 		{
+ 			AutoVacPID = 0;
+ 			if (exitstatus != 0)
+ 			{
+ 				/*
+ 				* Any unexpected exit of the autovacuum is treated as a crash.
+ 				* FIXME: This is useful for debugging autovac, but I think it should be 
+ 				* ripped out before final patch, autovac shouldn't crash the postmaster
+ 				*/
+ 				LogChildExit(LOG, gettext("pg_autovacuum process"),
+ 							pid, exitstatus);
+ 				HandleChildCrash(pid, exitstatus);
+ 				continue;
+ 			}
+ 		}
+ 		
+ 		/*
* Was it the archiver?  If so, just try to start a new
* one; no need to force reset of the rest of the system.  (If fail,
* we'll try again in future cycles of the main loop.)
***************
*** 2077,2083 ****
* StartupDataBase.  (We can ignore the archiver and stats processes
* here since they are not connected to shmem.)
*/
! 		if (DLGetHead(BackendList) || StartupPID != 0 || BgWriterPID != 0)
goto reaper_done;
ereport(LOG,
(errmsg("all server processes terminated; reinitializing")));
--- 2169,2175 ----
* StartupDataBase.  (We can ignore the archiver and stats processes
* here since they are not connected to shmem.)
*/
! 		if (DLGetHead(BackendList) || StartupPID != 0 || BgWriterPID != 0 || AutoVacPID != 0)
goto reaper_done;
ereport(LOG,
(errmsg("all server processes terminated; reinitializing")));
***************
*** 2100,2105 ****
--- 2192,2200 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+ 		/* Tell AutoVac to shut down */
+ 		if (AutoVacPID != 0)
+ 			kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 2265,2270 ****
--- 2360,2379 ----
}
FatalError = true;
+ 
+ 	/* Take care of the autovacuum too */
+ 	if (pid == AutoVacPID)
+ 		AutoVacPID = 0;
+ 	else if (AutoVacPID != 0 && !FatalError)
+ 	{
+ 		ereport(DEBUG2,
+ 				(errmsg_internal("sending %s to process %d",
+ 								 (SendStop ? "SIGSTOP" : "SIGQUIT"),
+ 								 (int) AutoVacPID)));
+ 		kill(AutoVacPID, (SendStop ? SIGSTOP : SIGQUIT));
+ 	}
+ 
+ 	FatalError = true;
}
/*
***************
*** 3256,3261 ****
--- 3365,3374 ----
ereport(LOG,
(errmsg("could not fork background writer process: %m")));
break;
+ 			case BS_XLOG_AUTOVAC:
+ 				ereport(LOG,
+ 						(errmsg("could not fork auto vacuum process: %m")));
+ 				break;
default:
ereport(LOG,
(errmsg("could not fork process: %m")));
***************
*** 3310,3315 ****
--- 3423,3446 ----
return true;
}
+ /*
+  * This should be used only for reporting "interactive" errors (essentially,
+  * bogus arguments on the command line).  Once the postmaster is launched,
+  * use ereport.  In particular, don't use this for anything that occurs
+  * after pmdaemonize.
+  */
+ static void
+ postmaster_error(const char *fmt,...)
+ {
+ 	va_list		ap;
+ 
+ 	fprintf(stderr, "%s: ", progname);
+ 	va_start(ap, fmt);
+ 	vfprintf(stderr, gettext(fmt), ap);
+ 	va_end(ap);
+ 	fprintf(stderr, "\n");
+ }
+ 
#ifdef EXEC_BACKEND
***************
*** 3749,3755 ****
if (r == WAIT_OBJECT_0)
pg_queue_signal(SIGCHLD);
else
! 		write_stderr("ERROR: failed to wait on child process handle: %d\n",
(int) GetLastError());
CloseHandle(procHandle);
return 0;
--- 3880,3886 ----
if (r == WAIT_OBJECT_0)
pg_queue_signal(SIGCHLD);
else
! 		fprintf(stderr, "ERROR: failed to wait on child process handle: %d\n",
(int) GetLastError());
CloseHandle(procHandle);
return 0;
*** ./src/backend/utils/misc/guc.c.orig	2004-08-04 01:41:20.000000000 -0400
--- ./src/backend/utils/misc/guc.c	2004-08-03 00:47:41.000000000 -0400
***************
*** 43,49 ****
--- 43,51 ----
#include "optimizer/prep.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+ #include "pgstat.h"
#include "postmaster/bgwriter.h"
+ #include "postmaster/autovacuum.h"
#include "postmaster/postmaster.h"
#include "storage/bufmgr.h"
#include "storage/fd.h"
***************
*** 55,61 ****
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/pg_locale.h"
- #include "pgstat.h"
char *guc_pgdata;
char *guc_hbafile;
--- 57,62 ----
***************
*** 642,647 ****
--- 643,656 ----
&pgstat_collect_blocklevel,
false, NULL, NULL
},
+ 	{
+ 		{"autovacuum", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Starts the auto vacuum subprocess."),
+ 			NULL
+ 		},
+ 		&autovacuum_start_daemon,
+ 		false, NULL, NULL	 
+ 	},
{
{"trace_notify", PGC_USERSET, DEVELOPER_OPTIONS,
***************
*** 1284,1289 ****
--- 1293,1322 ----
&block_size,
BLCKSZ, BLCKSZ, BLCKSZ, NULL, NULL
},
+ 	{
+ 		{"autovacuum_vacuum_threshold_base", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Minimum number of tuple updates or deletes prior to vacuum."),
+ 			NULL
+ 		},
+ 		&autovacuum_vacuum_base,
+ 		1000, 0, INT_MAX, NULL, NULL
+ 	},
+ 	{
+ 		{"autovacuum_analyze_threshold_base", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Minimum number of tuple updates or deletes prior to analyze."),
+ 			NULL
+ 		},
+ 		&autovacuum_analyze_base,
+ 		500, 0, INT_MAX, NULL, NULL
+ 	},
+ 	{
+ 		{"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Minimum number of tuple updates or deletes prior to analyze."),
+ 			NULL
+ 		},
+ 		&autovacuum_analyze_base,
+ 		500, 0, INT_MAX, NULL, NULL
+ 	},
/* End-of-list marker */
{
***************
*** 1364,1369 ****
--- 1397,1418 ----
&phony_random_seed,
0.5, 0.0, 1.0, assign_random_seed, show_random_seed
},
+ 	{
+ 		{"autovacuum_vacuum_threshold_sf", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Numer of tuple updates or deletes prior to vacuum as a factor of reltuples."),
+ 			NULL
+ 		},
+ 		&autovacuum_vacuum_scaling_factor,
+ 		2, 0, 100, NULL, NULL
+ 	},
+ 	{
+ 		{"autovacuum_analyze_threshold_sf", PGC_SIGHUP, AUTOVACUUM,
+ 			gettext_noop("Numer of tuple updates or deletes prior to analyze as a factor of reltuples."),
+ 			NULL
+ 		},
+ 		&autovacuum_analyze_scaling_factor,
+ 		1, 0, 100, NULL, NULL
+ 	},
/* End-of-list marker */
{
*** ./src/backend/utils/misc/postgresql.conf.sample.orig	2004-08-04 01:41:06.000000000 -0400
--- ./src/backend/utils/misc/postgresql.conf.sample	2004-07-22 23:50:32.000000000 -0400
***************
*** 238,243 ****
--- 238,253 ----
#---------------------------------------------------------------------------
+ # VACUUM DAEMON
+ #---------------------------------------------------------------------------
+ 
+ #autovacuum = false  # requires stats_row_level = true
+ #autovacuum_vacuum_threshold_base = 1000
+ #autovacuum_vacuum_threshold_sf = 2
+ #autovacuum_analyze_threshold_base = 500
+ #autovacuum_analyze_threshold_sf = 1
+ 
+ #---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
*** ./src/include/bootstrap/bootstrap.h.orig	2004-08-04 01:42:35.927740714 -0400
--- ./src/include/bootstrap/bootstrap.h	2004-07-22 23:50:32.000000000 -0400
***************
*** 59,63 ****
--- 59,64 ----
#define BS_XLOG_BOOTSTRAP	1
#define BS_XLOG_STARTUP		2
#define BS_XLOG_BGWRITER	3
+ #define BS_XLOG_AUTOVAC		4
#endif   /* BOOTSTRAP_H */
*** ./src/include/postmaster/autovacuum.h.orig	2004-06-29 09:29:39.000000000 -0400
--- ./src/include/postmaster/autovacuum.h	2004-08-04 00:46:59.293859961 -0400
***************
*** 1,42 ****
! /* pg_autovacuum.h
!  * Header file for pg_autovacuum.c
* (c) 2003 Matthew T. O'Connor
*/
#include "postgres_fe.h"

#include <unistd.h>
- #ifdef HAVE_GETOPT_H
- #include <getopt.h>
- #endif
#include <time.h>
#include <sys/time.h>

! /* These next two lines are correct when pg_autovaccum is compiled
! from within the postgresql source tree */
! #include "libpq-fe.h"
#include "lib/dllist.h"
! /* Had to change the last two lines to compile on
! Redhat outside of postgresql source tree */
! /*
! #include "/usr/include/libpq-fe.h"
! #include "/usr/include/pgsql/server/lib/dllist.h"
! */
!
! #define AUTOVACUUM_DEBUG 1
! #define VACBASETHRESHOLD 1000
! #define VACSCALINGFACTOR 2
! #define SLEEPBASEVALUE 300
#define SLEEPSCALINGFACTOR 2
- #define UPDATE_INTERVAL 2

/* these two constants are used to tell update_table_stats what operation we just perfomred */
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1

! #define TABLE_STATS_QUERY "select a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.oid=b.relid and a.relkind = 'r' and schemaname not like 'pg_temp_%'"
- #define FRONTEND
#define PAGES_QUERY "select oid,reltuples,relpages from pg_class where oid=%u"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
#define FROZENOID_QUERY2 "select oid,datname,age(datfrozenxid) from pg_database where datname!='template0'"
--- 1,35 ----
! /* autovacuum.h
!  * Header file for autovacuum.c
* (c) 2003 Matthew T. O'Connor
*/
+ #ifndef _AUTOVAC_H
+ #define _AUTOVAC_H
+ 
+ #define FRONTEND
#include "postgres_fe.h"
#include <unistd.h>
#include <time.h>
#include <sys/time.h>
+ #include <sys/stat.h>

! #include "../../interfaces/libpq/libpq-fe.h"
#include "lib/dllist.h"
!
! /* default settings defined here
! * These could be changed into GUC variables, but I'm not sure we need to. */
! #define SLEEPBASEVALUE 15
#define SLEEPSCALINGFACTOR 2

/* these two constants are used to tell update_table_stats what operation we just perfomred */
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1
+ #define AUTOVACPASSFILE "autovac.passwd"

! /* define the main queries */
! #define TABLE_STATS_QUERY "select a.oid, a.relname, a.relnamespace, a.relpages, a.relisshared, a.reltuples, b.schemaname, b.n_tup_ins, b.n_tup_upd, b.n_tup_del, c.cnt_at_last_analyze, c.cnt_at_last_vacuum, b.n_tup_ins + b.n_tup_upd as curr_analyze_count, b.n_tup_upd + b.n_tup_del as curr_vacuum_count, case when vacuum_threshold < 0 then c.vacuum_base_threshold + c.vacuum_scaling_factor * a.reltuples else c.vacuum_threshold end as vacuum_threshold, case when analyze_threshold < 0 then c.analyze_base_threshold + c.analyze_scaling_factor * a.reltuples else c.analyze_threshold end as analyze_threshold from pg_class a inner join pg_stat_all_tables b on a.oid=b.relid left outer join pg_autovacuum c on a.oid = c.table_oid where a.relkind = 'r' and schemaname not like 'pg_temp_%' and c.table_oid is not null"

#define PAGES_QUERY "select oid,reltuples,relpages from pg_class where oid=%u"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
#define FROZENOID_QUERY2 "select oid,datname,age(datfrozenxid) from pg_database where datname!='template0'"
***************
*** 44,70 ****
/* define atooid */
#define atooid(x) ((Oid) strtoul((x), NULL, 10))

- /* define cmd_args stucture */
- struct cmdargs
- {
- int vacuum_base_threshold,
- analyze_base_threshold,
- sleep_base_value,
- debug,
- daemonize;
- float vacuum_scaling_factor,
- analyze_scaling_factor,
- sleep_scaling_factor;
- char *user,
- *password,
- *host,
- *logfile,
- *port;
- };
- typedef struct cmdargs cmd_args;

! /* define cmd_args as global so we can get to them everywhere */
! cmd_args *args;
/* Might need to add a time value for last time the whold database was vacuumed.
I think we need to guarantee this happens approx every 1Million TX's  */
--- 37,53 ----
/* define atooid */
#define atooid(x)  ((Oid) strtoul((x), NULL, 10))
! /* ----------
! * GUC parameters
! * ----------
! */
! extern bool autovacuum_start_daemon;
! extern int autovacuum_vacuum_base;
! extern double autovacuum_vacuum_scaling_factor;
! extern int autovacuum_analyze_base;
! extern double autovacuum_analyze_scaling_factor;
!

/* Might need to add a time value for last time the whold database was vacuumed.
I think we need to guarantee this happens approx every 1Million TX's */
***************
*** 72,142 ****
{
Oid oid;
long age;
- long analyze_threshold,
- vacuum_threshold; /* Use these as defaults for table
- * thresholds */
PGconn *conn;
char *dbname,
*username,
*password;
- Dllist *table_list;
};
typedef struct dbinfo db_info;

- struct tableinfo
- {
- char *schema_name,
- *table_name;
- float reltuples;
- int relisshared;
- Oid relid,
- relpages;
- long analyze_threshold,
- vacuum_threshold;
- long CountAtLastAnalyze; /* equal to: inserts + updates as
- * of the last analyze or initial
- * values at startup */
- long CountAtLastVacuum; /* equal to: deletes + updates as
- * of the last vacuum or initial
- * values at startup */
- long curr_analyze_count,
- curr_vacuum_count; /* Latest values from stats system */
- db_info *dbi; /* pointer to the database that this table
- * belongs to */
- };
- typedef struct tableinfo tbl_info;
-
/* Functions for dealing with command line arguements */
! static cmd_args *get_cmd_args(int argc, char *argv[]);
! static void print_cmd_args(void);
! static void free_cmd_args(void);
! static void usage(void);

/* Functions for managing database lists */
static Dllist *init_db_list(void);
static db_info *init_dbinfo(char *dbname, Oid oid, long age);
! static void update_db_list(Dllist *db_list);
! static void remove_db_from_list(Dlelem *db_to_remove);
! static void print_db_info(db_info * dbi, int print_table_list);
! static void print_db_list(Dllist *db_list, int print_table_lists);
! static int xid_wraparound_check(db_info * dbi);
! static void free_db_list(Dllist *db_list);

/* Functions for managing table lists */
! static tbl_info *init_table_info(PGresult *conn, int row, db_info * dbi);
! static void update_table_list(db_info * dbi);
! static void remove_table_from_list(Dlelem *tbl_to_remove);
! static void print_table_list(Dllist *tbl_node);
! static void print_table_info(tbl_info * tbl);
! static void update_table_thresholds(db_info * dbi, tbl_info * tbl, int vacuum_type);
! static void free_tbl_list(Dllist *tbl_list);
/* A few database helper functions */
! static int	check_stats_enabled(db_info * dbi);
! static PGconn *db_connect(db_info * dbi);
! static void db_disconnect(db_info * dbi);
! static PGresult *send_query(const char *query, db_info * dbi);
! 
! /* Other Generally needed Functions */
! static void daemonize(void);
! static void log_entry(const char *logentry);
--- 55,88 ----
{
Oid			oid;
long		age;
PGconn	   *conn;
char	   *dbname,
*username,
*password;
};
typedef struct dbinfo db_info;
/* Functions for dealing with command line arguements */
! void AutoVacMain(void);
! void AutoVacLoop(void);

/* Functions for managing database lists */
static Dllist *init_db_list(void);
static db_info *init_dbinfo(char *dbname, Oid oid, long age);
! void update_db_list(Dllist *db_list);
! void remove_db_from_list(Dlelem *db_to_remove);
! void print_db_info(db_info * dbi, int print_table_list);
! void print_db_list(Dllist *db_list, int print_table_lists);
! int xid_wraparound_check(db_info * dbi);
! void free_db_list(Dllist *db_list);

/* Functions for managing table lists */
! void update_pg_autovacuum_table(db_info * dbi);
! void update_table_thresholds(db_info * dbi, Oid table_oid, int vacuum_type);
/* A few database helper functions */
! PGconn *db_connect(db_info * dbi);
! void db_disconnect(db_info * dbi);
! PGresult *send_query(const char *query, db_info * dbi);
! 
! #endif /* _AUTOVAC_H_ */
*** ./src/include/postmaster/postmaster.h.orig	2004-08-04 01:42:10.486531715 -0400
--- ./src/include/postmaster/postmaster.h	2004-07-22 23:50:32.000000000 -0400
***************
*** 29,34 ****
--- 29,35 ----
extern bool Log_connections;
extern bool log_hostname;
extern char *rendezvous_name;
+ extern bool autovacuum_start_daemon; 
#ifdef WIN32
extern HANDLE PostmasterHandle;
*** ./src/include/storage/proc.h.orig	2004-08-04 01:48:19.967030481 -0400
--- ./src/include/storage/proc.h	2004-08-03 00:47:45.000000000 -0400
***************
*** 109,115 ****
#define DUMMY_PROC_DEFAULT 0
#define DUMMY_PROC_BGWRITER 1
! #define NUM_DUMMY_PROCS 2
/* configurable options */
--- 109,116 ----
#define DUMMY_PROC_DEFAULT 0
#define DUMMY_PROC_BGWRITER 1
! #define DUMMY_PROC_AUTOVAC 2
! #define NUM_DUMMY_PROCS 3
/* configurable options */
*** ./src/include/utils/guc_tables.h.orig	2004-08-04 01:42:24.235023454 -0400
--- ./src/include/utils/guc_tables.h	2004-07-22 23:50:32.000000000 -0400
***************
*** 71,76 ****
--- 71,77 ----
COMPAT_OPTIONS_CLIENT,
DEVELOPER_OPTIONS,
COMPILE_OPTIONS,
+ 	AUTOVACUUM,
CUSTOM_OPTIONS
};
------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Matthew T. O'Connor

matthew@zeut.net

almost 22 years ago

In reply to: Matthew T. O'Connor (#2)

Re: Autovacuum Integration Patch Take 5

BTW, I should clarify that by a few days I mean, I'll be back on Sunday
evening and will be able to restart working on this then.

Matthew T. O'Connor wrote:

Show quoted text

Just to reply to my own post here, I spent a few hours last night
trying to improve the shutdown sequence for the Autovacuum patch. I
think I was headed in the right direction, but unfortunatly I wasn't
able to get anything to a high enough quality where I could submit the
patch. Basically I changed the postmaster shutdown code to signal the
autovacuum process before it signals any of it's normal child
processes, then inside the autovacuum signal handler I was issuing a
query cancel in the current database connection is there was one. For
some unknown reason, when I went to shut down the autovacuum process
by editing postgresql.conf and signaling the postmaster to reread it,
I would get one proc_exit(0) in the log followed by several hundred
proc_exit(1) and it was too late for me to track this down. I can
send in what I have so far, but it's not pretty.

Unfortunately, I am not going to be available to work on this for the
next few days. I don't know what to do.

Thanks,

Matthew O'Connor

Matthew T. O'Connor wrote:
Well I didn't get out of the office as early as I had hoped, and I have
stayed up longer than I had planned, but I have a patch that addresses
many of the issues raised by Tom. Please take a look at let me know if
I'm heading in the right direction.
Issues addressed:
* Add ability to read username and password from autovac.passwd file in
$PGDATA (format is username:password)
* Update patch, make sure it doesn't revert the recent write_stderr
changes in postmaster.c
* Remove static from functions defined in pg_autovacuum.h
* Remove if(sigsetjmp ...) code block
* Removed improper exit()'s, replaced with proc_exit() or ereport(ERROR)
* Change elog() calls to ereport()
* Change elog(WARNING, "pg_autovacuum: autovac is enabled, but requires
stats_row_level which is not enabled"); to only be called once on
postmaster startup
* Remove unneeded code that was cut and pasted from the bgwriter example

Issues not addressed in this patch:
* Dynamic linking of libpq: I need someone else to do this.
* Autovacuum Shutdown: I'm going to take a stab at this tonight
(Wednesday) we will see what I come up with.

To apply this patch:
1) Move pg_autovacuum.[ch] from contrib to
src/backend/postmaster/autovacuum.c and
src/include/postmaster/autovacuum.h respectively. 2) Place the
attached pg_autovacuum.h in src/include/catelog/
3) Apply the attached diff file

Thanks again,

Matthew O'Connor

------------------------------------------------------------------------

/*-------------------------------------------------------------------------

*
* pg_autovacuum.h
* definition of the system "autovacuum" relation (pg_autovacuum)
*
* NOTE: an object is identified by the OID of the row that primarily
* defines the object, plus the OID of the table that that row appears
in.
* For example, a function is identified by the OID of its pg_proc row
* plus the pg_class OID of table pg_proc. This allows unique
identification
* of objects without assuming that OIDs are unique across tables.
*
* Since attributes don't have OIDs of their own, we identify an
attribute
* comment by the objoid+classoid of its parent table, plus an "objsubid"
* giving the attribute column number. "objsubid" must be zero in a
comment
* for a table itself, so that it is distinct from any column comment.
* Currently, objsubid is unused and zero for all other kinds of objects,
* but perhaps it might be useful someday to associate comments with
* constituent elements of other kinds of objects (arguments of a
function,
* for example).
*
*
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
* $PostgreSQL: pgsql-server/src/include/catalog/pg_autovacuum.h,v
1.20 2003/11/29 22:40:58 pgsql Exp $
*
* NOTES
* the genbki.sh script reads this file and generates .bki
* information from the DATA() statements.
*
* XXX do NOT break up DATA() statements into multiple lines!
* the scripts are not as smart as you might think...
*
*-------------------------------------------------------------------------

*/
#ifndef PG_AUTOVACUUM_H
#define PG_AUTOVACUUM_H

/* ----------------
* postgres.h contains the system type definitions and the
* CATALOG(), BOOTSTRAP and DATA() sugar words so this file
* can be read by both genbki.sh and the C compiler.
* ----------------
*/

/* ----------------
* pg_autovacuum definition. cpp turns this into
* typedef struct FormData_pg_autovacuum
* ----------------
*/
CATALOG(pg_autovacuum) BKI_WITHOUT_OIDS
{
Oid table_oid; /* OID of table */
int4 analyze_base_threshold; /* Base Threshold value */
int4 vacuum_base_threshold; /* Base Threshold value */
float4 analyze_scaling_factor; /* Threshold of
ins/upd/del's before analyze */
float4 vacuum_scaling_factor; /* Threshold of
ins/upd/del's before vacuuum */
float4 analyze_threshold; /* Threshold of ins/upd/del's
before analyze */
float4 vacuum_threshold; /* Threshold of ins/upd/del's
before vacuuum */
float4 cnt_at_last_analyze; /* equal to: inserts +
updates as
* of the last analyze or
initial
* values at startup */
float4 cnt_at_last_vacuum; /* equal to: deletes +
updates as
* of the last vacuum or initial
* values at startup */
} FormData_pg_autovacuum;

/* ----------------
* Form_pg_autovacuum corresponds to a pointer to a tuple with
* the format of pg_autovacuum relation.
* ----------------
*/
typedef FormData_pg_autovacuum *Form_pg_autovacuum;

/* ----------------
* compiler constants for pg_autovacuum
* ----------------
*/
#define Natts_pg_autovacuum 9
#define Anum_pg_autovacuum_table_oid 1
#define Anum_pg_autovacuum_analyze_base_threshold 2
#define Anum_pg_autovacuum_vacuum_base_threshold 3
#define Anum_pg_autovacuum_analyze_scaling_factor 4
#define Anum_pg_autovacuum_vacuum_scaling_factor 5
#define Anum_pg_autovacuum_analyze_threshold 6
#define Anum_pg_autovacuum_vacuum_threshold 7
#define Anum_pg_autovacuum_cnt_at_last_analyze 8
#define Anum_pg_autovacuum_cnt_at_last_vacuum 9

/* ----------------
* initial contents of pg_autovacuum
* ----------------
*/

/*
* Because the contents of this table are taken from the other *.h
files,
* there is no initialization here. The initial contents are
extracted
* by genbki.sh and loaded during initdb.
*/

#endif /* PG_AUTOVACUUM_H */

------------------------------------------------------------------------
*** ./src/backend/bootstrap/bootstrap.c.orig    2004-08-04 
01:41:29.044078073 -0400
--- ./src/backend/bootstrap/bootstrap.c    2004-08-03 
00:47:35.000000000 -0400
***************
*** 33,38 ****
--- 33,39 ----
#include "libpq/pqsignal.h"
#include "miscadmin.h"
#include "postmaster/bgwriter.h"
+ #include "postmaster/autovacuum.h"
#include "storage/freespace.h"
#include "storage/ipc.h"
#include "storage/pg_shmem.h"
***************
*** 361,366 ****
--- 362,370 ----
case BS_XLOG_BGWRITER:
statmsg = "writer process";
break;
+             case BS_XLOG_AUTOVAC:
+                 statmsg = "auto vacuum process";
+                 break;
default:
statmsg = "??? process";
break;
***************
*** 397,402 ****
--- 401,409 ----
case BS_XLOG_BGWRITER:
InitDummyProcess(DUMMY_PROC_BGWRITER);
break;
+             case BS_XLOG_AUTOVAC:
+                 InitDummyProcess(DUMMY_PROC_AUTOVAC);
+                 break;
default:
InitDummyProcess(DUMMY_PROC_DEFAULT);
***************
*** 433,438 ****
--- 440,451 ----
BackgroundWriterMain();
proc_exit(1);        /* should never return */
+         case BS_XLOG_AUTOVAC:
+             /* don't set signals, autovac has its own agenda */
+             InitXLOGAccess();
+             AutoVacMain();
+             proc_exit(1);        /* should never return */
+        
default:
elog(PANIC, "unrecognized XLOG op: %d", xlogop);
proc_exit(1);
*** ./src/backend/catalog/Makefile.orig    2004-08-04 
01:41:52.546499796 -0400
--- ./src/backend/catalog/Makefile    2004-07-22 23:50:31.000000000 
-0400
***************
*** 32,38 ****
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h 
pg_cast.h \
pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h 
pg_group.h \
!     pg_tablespace.h pg_depend.h indexing.h \
)
pg_includes := $(sort -I$(top_srcdir)/src/include 
-I$(top_builddir)/src/include)
--- 32,38 ----
pg_language.h pg_largeobject.h pg_aggregate.h pg_statistic.h \
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h 
pg_cast.h \
pg_namespace.h pg_conversion.h pg_database.h pg_shadow.h 
pg_group.h \
!     pg_tablespace.h pg_depend.h pg_autovacuum.h indexing.h \
)
pg_includes := $(sort -I$(top_srcdir)/src/include 
-I$(top_builddir)/src/include)
*** ./src/backend/Makefile.orig    2004-08-04 01:41:41.458716157 -0400
--- ./src/backend/Makefile    2004-08-03 00:47:34.000000000 -0400
***************
*** 29,41 ****
##########################################################################

! all: submake-libpgport postgres $(POSTGRES_IMP)

ifneq ($(PORTNAME), cygwin)
ifneq ($(PORTNAME), win32)

postgres: $(OBJS)
! $(CC) $(CFLAGS) $(LDFLAGS) $(export_dynamic) $^ $(LIBS) -o $@
endif
endif
--- 29,41 ----
##########################################################################

! all: submake-libpgport submake-libpq postgres $(POSTGRES_IMP)

ifneq ($(PORTNAME), cygwin)
ifneq ($(PORTNAME), win32)

postgres: $(OBJS)
! $(CC) $(CFLAGS) $(LDFLAGS) -I $(libpq_srcdir) $(export_dynamic)
$^ $(LIBS) $(libpq) -o $@
endif
endif
*** ./src/backend/postmaster/autovacuum.c.orig    2004-06-29 
09:27:14.000000000 -0400
--- ./src/backend/postmaster/autovacuum.c    2004-08-04 
02:00:32.005810127 -0400
***************
*** 1,153 ****
! /* pg_autovacuum.c
* All the code for the pg_autovacuum program
* (c) 2003 Matthew T. O'Connor
* Revisions by Christopher B. Browne, Liberty RMS
*/
! #include "pg_autovacuum.h"

- FILE *LOGOUTPUT;
char logbuffer[4096];

! static void
! log_entry(const char *logentry)
! {
! time_t curtime;
! struct tm *loctime;
! char timebuffer[128];
! ! curtime = time(NULL);
! loctime = localtime(&curtime);
! strftime(timebuffer, sizeof(timebuffer), "%Y-%m-%d %H:%M:%S
%Z", loctime);
! fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry);
! }

/*
! * Function used to detach the pg_autovacuum daemon from the tty
and go into
! * the background.
*
! * This code is mostly ripped directly from pm_dameonize in
postmaster.c with
! * unneeded code removed.
*/
! static void
! daemonize()
{
! pid_t pid;

! pid = fork();
! if (pid == (pid_t) -1)
! {
! log_entry("Error: cannot disassociate from controlling TTY");
! fflush(LOGOUTPUT);
! _exit(1);
! }
! else if (pid)
! { /* parent */
! /* Parent should just exit, without doing any atexit
cleanup */
! _exit(0);
! }

! /* GH: If there's no setsid(), we hopefully don't need silent mode.
! * Until there's a better solution. */
! #ifdef HAVE_SETSID
! if (setsid() < 0)
! {
! log_entry("Error: cannot disassociate from controlling TTY");
! fflush(LOGOUTPUT);
! _exit(1);
! }
#endif

! }
! ! /* Create and return tbl_info struct with initialized to values
from row or res */
! static tbl_info *
! init_table_info(PGresult *res, int row, db_info * dbi)
! {
! tbl_info *new_tbl = (tbl_info *) malloc(sizeof(tbl_info));

! if (!new_tbl)
! {
! log_entry("init_table_info: Cannot get memory");
! fflush(LOGOUTPUT);
! return NULL;
! }

! if (res == NULL)
! return NULL;

- new_tbl->dbi = dbi; /* set pointer to db */

! new_tbl->schema_name = (char *)
! malloc(strlen(PQgetvalue(res, row, PQfnumber(res,
"schemaname"))) + 1);
! if (!new_tbl->schema_name)
! {
! log_entry("init_table_info: malloc failed on
new_tbl->schema_name");
! fflush(LOGOUTPUT);
! return NULL;
! }
! strcpy(new_tbl->schema_name,
! PQgetvalue(res, row, PQfnumber(res, "schemaname")));

! new_tbl->table_name = (char *)
! malloc(strlen(PQgetvalue(res, row, PQfnumber(res,
"relname"))) +
! strlen(new_tbl->schema_name) + 6);
! if (!new_tbl->table_name)
! {
! log_entry("init_table_info: malloc failed on
new_tbl->table_name");
! fflush(LOGOUTPUT);
! return NULL;
! }

/*
! * Put both the schema and table name in quotes so that we can
work
! * with mixed case table names
*/
! strcpy(new_tbl->table_name, "\"");
! strcat(new_tbl->table_name, new_tbl->schema_name);
! strcat(new_tbl->table_name, "\".\"");
! strcat(new_tbl->table_name, PQgetvalue(res, row, PQfnumber(res,
"relname")));
! strcat(new_tbl->table_name, "\"");
! ! new_tbl->CountAtLastAnalyze =
! (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_ins"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))));
! new_tbl->curr_analyze_count = new_tbl->CountAtLastAnalyze;
! ! new_tbl->CountAtLastVacuum =
! (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))) +
! atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))));
! new_tbl->curr_vacuum_count = new_tbl->CountAtLastVacuum;
! ! new_tbl->relid = atooid(PQgetvalue(res, row, PQfnumber(res,
"oid")));
! new_tbl->reltuples = atof(PQgetvalue(res, row, PQfnumber(res,
"reltuples")));
! new_tbl->relpages = atooid(PQgetvalue(res, row, PQfnumber(res,
"relpages")));
! ! if (strcmp("t", PQgetvalue(res, row, PQfnumber(res,
"relisshared"))))
! new_tbl->relisshared = 0;
! else
! new_tbl->relisshared = 1;
! ! new_tbl->analyze_threshold =
! args->analyze_base_threshold + args->analyze_scaling_factor
* new_tbl->reltuples;
! new_tbl->vacuum_threshold =
! args->vacuum_base_threshold + args->vacuum_scaling_factor *
new_tbl->reltuples;

! if (args->debug >= 2)
! print_table_info(new_tbl);

! return new_tbl;
}

/* Set thresholds = base_value + scaling_factor * reltuples
Should be called after a vacuum since vacuum updates values in
pg_class */
! static void
! update_table_thresholds(db_info * dbi, tbl_info * tbl, int
vacuum_type)
{
- PGresult *res = NULL;
int disconnect = 0;
! char query[128];
if (dbi->conn == NULL)
{
--- 1,188 ----
! 
/*------------------------------------------------------------------------- 
! *
! * pg_autovacuum.c
! *
! * The background autovacuum daemon was in 7.4 contribis but is
being newly ! * integrated into 7.5. It monitors database activity
using data from the ! * stats system (though at some point is should
also look at FSM data) so ! * as to perform vacuum commands on
specific tables when and only when ! * a sufficient amount activity
has been performed on that table.
! *
! * The autovacuum process is started by the postmaster on startup.
! * It remains alive until the postmaster commands it to terminate.
Normal ! * termination is by SIGUSR2, which instructs the autovacuum
process to proc_exit(). ! * Emergency termination is by SIGQUIT;
like any
! * backend, the autovacuum process will simply abort and proc_exit
on SIGQUIT.
! *
* All the code for the pg_autovacuum program
* (c) 2003 Matthew T. O'Connor
* Revisions by Christopher B. Browne, Liberty RMS
+
*-------------------------------------------------------------------------
*/
+ #include "postgres.h"
+ + #include <signal.h>
+ #include <time.h>
! #include "access/xlog.h"
! ! #include "libpq/pqsignal.h"
! #include "miscadmin.h"
! #include "storage/bufmgr.h"
! #include "storage/freespace.h"
! #include "storage/ipc.h"
! #include "storage/pmsignal.h"
! #include "storage/smgr.h"
! #include "tcop/tcopprot.h"
! #include "utils/guc.h"
! #include "postmaster/autovacuum.h"

char logbuffer[4096];

! /*
! * GUC parameters
! */
! bool autovacuum_start_daemon = false;
! int autovacuum_vacuum_base = 1000;
! double autovacuum_vacuum_scaling_factor = 2;
! int autovacuum_analyze_base = 500;
! double autovacuum_analyze_scaling_factor = 1;
! int PostPortNumber = 5432;
! ! /* ! * These used to be taken from command lines args ! * and
might need to move to GUC, but I'm not sure.
! */
! char *pg_user = NULL,
! *pg_user_password = NULL;
! ! /*
! * Flags set by interrupt handlers for later service in the main loop.
! */
! static volatile sig_atomic_t got_SIGHUP = false;
! static volatile sig_atomic_t shutdown_requested = false;

/*
! * Private state
! */
! static bool am_autovac = false;
! ! ! static void autovac_quickdie(SIGNAL_ARGS);
! static void AutoVacSigHupHandler(SIGNAL_ARGS);
! static void ReqShutdownHandler(SIGNAL_ARGS);
! ! ! /*
! * Main entry point for autovacuum sub-process
*
! * This is invoked from BootstrapMain, which has already created
the basic
! * execution environment, but not enabled signals yet.
*/
! void
! AutoVacMain(void)
{
! am_autovac = true;

! /*
! * Properly accept or ignore signals the postmaster might send us
! *
! * Note: we deliberately ignore SIGTERM, because during a
standard Unix
! * system shutdown cycle, init will SIGTERM all processes at
once. We
! * want to wait for the backends to proc_exit, whereupon the
postmaster will
! * tell us it's okay to shut down (via SIGUSR2).
! *
! * SIGUSR1 is presently unused; keep it spare in case someday
we want
! * this process to participate in sinval messaging.
! */
! pqsignal(SIGHUP, AutoVacSigHupHandler); /* set flag to read
config file */
! pqsignal(SIGTERM, SIG_IGN); /* ignore SIGTERM */
! pqsignal(SIGQUIT, autovac_quickdie); /* hard crash time */
! pqsignal(SIGALRM, SIG_IGN);
! pqsignal(SIGPIPE, SIG_IGN);
! pqsignal(SIGUSR1, SIG_IGN); /* reserve for sinval */
! pqsignal(SIGUSR2, ReqShutdownHandler); /* request
shutdown */

! /*
! * Reset some signals that are accepted by postmaster but not here
! */
! pqsignal(SIGCHLD, SIG_DFL);
! pqsignal(SIGTTIN, SIG_DFL);
! pqsignal(SIGTTOU, SIG_DFL);
! pqsignal(SIGCONT, SIG_DFL);
! pqsignal(SIGWINCH, SIG_DFL);
! ! /* We allow SIGQUIT (quickdie) at all times */
! #ifdef HAVE_SIGPROCMASK
! sigdelset(&BlockSig, SIGQUIT);
! #else
! BlockSig &= ~(sigmask(SIGQUIT));
#endif

! /*
! * If an exception is encountered, processing resumes here.
! */

! /*
! * Unblock signals (they were blocked when the postmaster
forked us)
! */
! PG_SETMASK(&UnBlockSig);

! AutoVacLoop();
! }

! /* --------------------------------
! * signal handler routines
! * --------------------------------
! */

! /*
! * autovac_quickdie() occurs when signalled SIGQUIT by the postmaster.
! *
! * Some backend has bought the farm,
! * so we need to stop what we're doing and proc_exit.
! */
! static void
! autovac_quickdie(SIGNAL_ARGS)
! {
! PG_SETMASK(&BlockSig);

/*
! * DO NOT proc_exit() -- we're here because shared memory may be
! * corrupted, so we don't want to try to clean up our transaction.
! * Just nail the windows shut and get out of town.
! *
! * Note we do exit(1) not exit(0). This is to force the
postmaster into
! * a system reset cycle if some idiot DBA sends a manual
SIGQUIT to a
! * random backend. This is necessary precisely because we
don't clean
! * up our shared memory state.
*/
! exit(1);
! }

! /* SIGHUP: set flag to re-read config file at next convenient time */
! static void
! AutoVacSigHupHandler(SIGNAL_ARGS)
! {
! got_SIGHUP = true;
! }

! /* SIGUSR2: set flag to run a shutdown checkpoint and exit */
! static void
! ReqShutdownHandler(SIGNAL_ARGS)
! {
! shutdown_requested = true;
}

+ /* Set thresholds = base_value + scaling_factor * reltuples
Should be called after a vacuum since vacuum updates values in
pg_class */
! void
! update_table_thresholds(db_info * dbi, Oid table_oid, int vacuum_type)
{
int disconnect = 0;
! char query[255];

if (dbi->conn == NULL)
{
***************
*** 157,215 ****

if (dbi->conn != NULL)
{
! snprintf(query, sizeof(query), PAGES_QUERY, tbl->relid);
! res = send_query(query, dbi);
! if (res != NULL)
{
! tbl->reltuples =
! atof(PQgetvalue(res, 0, PQfnumber(res, "reltuples")));
! tbl->relpages = atooid(PQgetvalue(res, 0,
PQfnumber(res, "relpages")));
! ! /*
! * update vacuum thresholds only of we just did a vacuum
! * analyze
! */
! if (vacuum_type == VACUUM_ANALYZE)
! {
! tbl->vacuum_threshold =
! (args->vacuum_base_threshold +
args->vacuum_scaling_factor * tbl->reltuples);
! tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
! }
! ! /* update analyze thresholds */
! tbl->analyze_threshold =
! (args->analyze_base_threshold +
args->analyze_scaling_factor * tbl->reltuples);
! tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
! ! PQclear(res);
! ! /*
! * If the stats collector is reporting fewer updates
then we
! * have on record then the stats were probably reset,
so we
! * need to reset also
! */
! if ((tbl->curr_analyze_count < tbl->CountAtLastAnalyze) ||
! (tbl->curr_vacuum_count < tbl->CountAtLastVacuum))
! {
! tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
! tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
! }
}
}
if (disconnect)
db_disconnect(dbi);
}

! static void
! update_table_list(db_info * dbi)
{
int disconnect = 0;
! PGresult *res = NULL;
! tbl_info *tbl = NULL;
! Dlelem *tbl_elem = DLGetHead(dbi->table_list);
! int i = 0,
! t = 0,
! found_match = 0;
if (dbi->conn == NULL)
{
--- 192,244 ----
if (dbi->conn != NULL)
{
!         /*
!         * update vacuum and analyze thresholds if !         * we 
did a vacuum analyze
!         */
!         if (vacuum_type == VACUUM_ANALYZE)
{
!             sprintf(query, "update pg_autovacuum set 
cnt_at_last_analyze = n_tup_ins + n_tup_upd, cnt_at_last_vacuum = 
n_tup_upd + n_tup_del from pg_stat_all_tables where table_oid = relid 
and relid = %u", table_oid);
!             send_query(query, dbi);
}
+         /* +             * update only the analyze thresholds  if
+             * we only did an analyze
+             */
+         else
+         {
+             sprintf(query, "update pg_autovacuum set 
cnt_at_last_analyze = n_tup_ins + n_tup_upd from pg_stat_all_tables 
where table_oid = relid and relid = %u", table_oid);
+             send_query(query, dbi);
+         }
+            
+         /* FIXME: Need to think about this and pg_stat roll over 
issues */
+         /*
+          * If the stats collector is reporting fewer updates then we
+          * have on record then the stats were probably reset, or 
overflowed, +          * so we need to reset also our numbers also
+          */
+ /*        if ((tbl->curr_analyze_count < tbl->CountAtLastAnalyze) ||
+             (tbl->curr_vacuum_count < tbl->CountAtLastVacuum))
+         {
+             tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
+             tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
+         }*/
}
if (disconnect)
db_disconnect(dbi);
}
! void
! update_pg_autovacuum_table(db_info *dbi)
{
+     /*
+      * This function will update the pg_autovacuum system table in 
two steps:
+      * 1) Delete entries that are no longer in pg_class an
+      * 2) Add zero'd out entries to the pg_autovacuum table for new 
tables that exist in pg_class not in pg_autovacuum
+      */
+           int            disconnect = 0;
!     char        query[4096];
if (dbi->conn == NULL)
{
***************
*** 219,394 ****

if (dbi->conn != NULL)
{
/*
! * Get a result set that has all the information we will
need to
! * both remove tables from the list that no longer exist
and add
! * tables to the list that are new
*/
! res = send_query((char *) TABLE_STATS_QUERY, dbi);
! if (res != NULL)
! {
! t = PQntuples(res);
!
! /*
! * First: use the tbl_list as the outer loop and the
result set as
! * the inner loop, this will determine what tables
should be
! * removed
! */
! while (tbl_elem != NULL)
! {
! tbl = ((tbl_info *) DLE_VAL(tbl_elem));
! found_match = 0;
!
! for (i = 0; i < t; i++)
! { /* loop through result set
looking for a
! * match */
! if (tbl->relid == atooid(PQgetvalue(res, i,
PQfnumber(res, "oid"))))
! {
! found_match = 1;
! break;
! }
! }
! if (found_match == 0)
! { /* then we didn't find this
tbl_elem in
! * the result set */
! Dlelem *elem_to_remove = tbl_elem;
!
! tbl_elem = DLGetSucc(tbl_elem);
! remove_table_from_list(elem_to_remove);
! }
! else
! tbl_elem = DLGetSucc(tbl_elem);
! } /* Done removing dropped
tables from the
! * table_list */
!
! /*
! * Then loop use result set as outer loop and tbl_list
as the
! * inner loop to determine what tables are new
! */
! for (i = 0; i < t; i++)
! {
! tbl_elem = DLGetHead(dbi->table_list);
! found_match = 0;
! while (tbl_elem != NULL)
! {
! tbl = ((tbl_info *) DLE_VAL(tbl_elem));
! if (tbl->relid == atooid(PQgetvalue(res, i,
PQfnumber(res, "oid"))))
! {
! found_match = 1;
! break;
! }
! tbl_elem = DLGetSucc(tbl_elem);
! }
! if (found_match == 0) /* then we didn't find
this result
! * now in the tbl_list */
! {
! DLAddTail(dbi->table_list,
DLNewElem(init_table_info(res, i, dbi)));
! if (args->debug >= 1)
! {
! sprintf(logbuffer, "added table: %s.%s",
dbi->dbname,
! ((tbl_info *)
DLE_VAL(DLGetTail(dbi->table_list)))->table_name);
! log_entry(logbuffer);
! }
! }
! } /* end of for loop that adds
tables */
! }
! fflush(LOGOUTPUT);
! PQclear(res);
! res = NULL;
! if (args->debug >= 3)
! print_table_list(dbi->table_list);
! if (disconnect)
! db_disconnect(dbi);
}
}

- /* Free memory, and remove the node from the list */
- static void
- remove_table_from_list(Dlelem *tbl_to_remove)
- {
- tbl_info *tbl = ((tbl_info *) DLE_VAL(tbl_to_remove));
- - if (args->debug >= 1)
- {
- sprintf(logbuffer, "Removing table: %s from list.",
tbl->table_name);
- log_entry(logbuffer);
- fflush(LOGOUTPUT);
- }
- DLRemove(tbl_to_remove);
- - if (tbl->schema_name)
- {
- free(tbl->schema_name);
- tbl->schema_name = NULL;
- }
- if (tbl->table_name)
- {
- free(tbl->table_name);
- tbl->table_name = NULL;
- }
- if (tbl)
- {
- free(tbl);
- tbl = NULL;
- }
- DLFreeElem(tbl_to_remove);
- }
- - /* Free the entire table list */
- static void
- free_tbl_list(Dllist *tbl_list)
- {
- Dlelem *tbl_elem = DLGetHead(tbl_list);
- Dlelem *tbl_elem_to_remove = NULL;
- - while (tbl_elem != NULL)
- {
- tbl_elem_to_remove = tbl_elem;
- tbl_elem = DLGetSucc(tbl_elem);
- remove_table_from_list(tbl_elem_to_remove);
- }
- DLFreeList(tbl_list);
- }
- - static void
- print_table_list(Dllist *table_list)
- {
- Dlelem *table_elem = DLGetHead(table_list);
- - while (table_elem != NULL)
- {
- print_table_info(((tbl_info *) DLE_VAL(table_elem)));
- table_elem = DLGetSucc(table_elem);
- }
- }
- - static void
- print_table_info(tbl_info * tbl)
- {
- sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname,
tbl->table_name);
- log_entry(logbuffer);
- sprintf(logbuffer, " relid: %u; relisshared: %i",
tbl->relid, tbl->relisshared);
- log_entry(logbuffer);
- sprintf(logbuffer, " reltuples: %f; relpages: %u",
tbl->reltuples, tbl->relpages);
- log_entry(logbuffer);
- sprintf(logbuffer, " curr_analyze_count: %li;
curr_vacuum_count: %li",
- tbl->curr_analyze_count, tbl->curr_vacuum_count);
- log_entry(logbuffer);
- sprintf(logbuffer, " last_analyze_count: %li;
last_vacuum_count: %li",
- tbl->CountAtLastAnalyze, tbl->CountAtLastVacuum);
- log_entry(logbuffer);
- sprintf(logbuffer, " analyze_threshold: %li;
vacuum_threshold: %li",
- tbl->analyze_threshold, tbl->vacuum_threshold);
- log_entry(logbuffer);
- fflush(LOGOUTPUT);
- }

/* End of table Management Functions */

/* Beginning of DB Management Functions */
/* init_db_list() creates the db_list and initalizes template1 */
! static Dllist *
init_db_list()
{
Dllist       *db_list = DLNewList();
--- 248,276 ----
if (dbi->conn != NULL)
{
+         /* +          * Delete entries in pg_autovacuum that are no 
longer in pg_class +          */
+         send_query("DELETE from pg_autovacuum where table_oid not 
in (select relid from pg_stat_all_tables )",dbi);
+        
/*
!          * Insert entires into pg_autovacuum for new tables (ones 
that exist in pg_class / pg_stat.., but not in pg_autovacuum) 
!          * and fill in defaut values for these new tables.
!          * then add them to the table list.
*/
!         sprintf(query, "insert into pg_autovacuum (table_oid, 
analyze_base_threshold, vacuum_base_threshold, 
analyze_scaling_factor, vacuum_scaling_factor, analyze_threshold, 
vacuum_threshold, cnt_at_last_analyze, cnt_at_last_vacuum)   select 
a.oid, current_setting('autovacuum_analyze_threshold_base')::int, 
current_setting('autovacuum_vacuum_threshold_base')::int, 
current_setting('autovacuum_analyze_threshold_sf')::float, 
current_setting('autovacuum_vacuum_threshold_sf')::float, -1, -1, 0, 
0 from pg_class a inner join pg_stat_all_tables b on a.oid=b.relid 
left outer join pg_autovacuum c on a.oid = c.table_oid where 
a.relkind = 'r' and schemaname not like 'pg_temp_%%' and a.oid not in 
(select distinct table_oid from pg_autovacuum)"); !         
elog(DEBUG5, query);
!         send_query(query,dbi);
}
}
/* End of table Management Functions */

/* Beginning of DB Management Functions */

/* init_db_list() creates the db_list and initalizes template1 */
! Dllist *
init_db_list()
{
Dllist *db_list = DLNewList();
***************
*** 398,405 ****
DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1",
0, 0)));
if (DLGetHead(db_list) == NULL)
{ /* Make sure init_dbinfo was
successful */
! log_entry("init_db_list(): Error creating db_list for db:
template1.");
! fflush(LOGOUTPUT);
return NULL;
}
--- 280,287 ----
DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1", 
0, 0)));
if (DLGetHead(db_list) == NULL)
{                            /* Make sure init_dbinfo was 
successful */
!         ereport(ERROR,(errmsg("pg_autovacuum: Error initializing 
db_list")));
!        
return NULL;
}
***************
*** 419,427 ****
dbs->age = atol(PQgetvalue(res, 0, PQfnumber(res, "age")));
if (res)
PQclear(res);
-    
-             if (args->debug >= 2)
-                 print_db_list(db_list, 0);
}
else
return NULL;
--- 301,306 ----
***************
*** 431,470 ****
/* Simple function to create an instance of the dbinfo struct
Initalizes all the pointers and connects to the database */
! static db_info *
init_dbinfo(char *dbname, Oid oid, long age)
{
db_info *newdbinfo = (db_info *) malloc(sizeof(db_info));

- newdbinfo->analyze_threshold = args->vacuum_base_threshold;
- newdbinfo->vacuum_threshold = args->analyze_base_threshold;
newdbinfo->dbname = (char *) malloc(strlen(dbname) + 1);
strcpy(newdbinfo->dbname, dbname);
newdbinfo->username = NULL;
! if (args->user != NULL)
{
! newdbinfo->username = (char *) malloc(strlen(args->user) + 1);
! strcpy(newdbinfo->username, args->user);
}
newdbinfo->password = NULL;
! if (args->password != NULL)
{
! newdbinfo->password = (char *)
malloc(strlen(args->password) + 1);
! strcpy(newdbinfo->password, args->password);
}
newdbinfo->oid = oid;
newdbinfo->age = age;
- newdbinfo->table_list = DLNewList();
newdbinfo->conn = NULL;

- if (args->debug >= 2)
- print_table_list(newdbinfo->table_list);
- return newdbinfo;
}
/* Function adds and removes databases from the db_list as 
appropriate */
! static void
update_db_list(Dllist *db_list)
{
int            disconnect = 0;
--- 310,345 ----
/* Simple function to create an instance of the dbinfo struct
Initalizes all the pointers and connects to the database */
! db_info *
init_dbinfo(char *dbname, Oid oid, long age)
{
db_info *newdbinfo = (db_info *) malloc(sizeof(db_info));

newdbinfo->dbname = (char *) malloc(strlen(dbname) + 1);
strcpy(newdbinfo->dbname, dbname);
newdbinfo->username = NULL;
! if (pg_user != NULL)
{
! newdbinfo->username = (char *) malloc(strlen(pg_user) + 1);
! strcpy(newdbinfo->username, pg_user);
}
+ newdbinfo->password = NULL;
! if (pg_user_password != NULL)
{
! newdbinfo->password = (char *)
malloc(strlen(pg_user_password) + 1);
! strcpy(newdbinfo->password, pg_user_password);
}
+
newdbinfo->oid = oid;
newdbinfo->age = age;
newdbinfo->conn = NULL;

return newdbinfo;
}

/* Function adds and removes databases from the db_list as
appropriate */
! void
update_db_list(Dllist *db_list)
{
int disconnect = 0;
***************
*** 476,486 ****
t = 0,
found_match = 0;

! if (args->debug >= 2)
! {
! log_entry("updating the database list");
! fflush(LOGOUTPUT);
! }
if (dbi_template1->conn == NULL)
{
--- 351,357 ----
t = 0,
found_match = 0;
! ereport(DEBUG2,(errmsg("pg_autovacuum: updating the database
list")));
if (dbi_template1->conn == NULL)
{
***************
*** 563,581 ****
(PQgetvalue(res, i, PQfnumber(res, 
"datname")),
atooid(PQgetvalue(res, i, PQfnumber(res, 
"oid"))),
atol(PQgetvalue(res, i, PQfnumber(res, 
"age"))))));
!                     if (args->debug >= 1)
!                     {
!                         sprintf(logbuffer, "added database: %s", 
((db_info *) DLE_VAL(DLGetTail(db_list)))->dbname);
!                         log_entry(logbuffer);
!                     }
}
!             }                        /* end of for loop that adds 
tables */
}
-         fflush(LOGOUTPUT);
PQclear(res);
res = NULL;
!         if (args->debug >= 3)
!             print_db_list(db_list, 0);
if (disconnect)
db_disconnect(dbi_template1);
}
--- 434,446 ----
(PQgetvalue(res, i, PQfnumber(res, 
"datname")),
atooid(PQgetvalue(res, i, PQfnumber(res, 
"oid"))),
atol(PQgetvalue(res, i, PQfnumber(res, 
"age"))))));
!                     ereport(DEBUG1,(errmsg("pg_autovacuum: added 
database %s", ((db_info *) DLE_VAL(DLGetTail(db_list)))->dbname)));
}
!             }    /* end of for loop that adds tables */
}
PQclear(res);
res = NULL;
!         print_db_list(db_list, 0);
if (disconnect)
db_disconnect(dbi_template1);
}
***************
*** 595,601 ****
return 0 if nothing happened,
return 1 if the database needed a database wide vacuum
*/
! static int
xid_wraparound_check(db_info * dbi)
{
/*
--- 460,466 ----
return 0 if nothing happened,
return 1 if the database needed a database wide vacuum
*/
! int
xid_wraparound_check(db_info * dbi)
{
/*
***************
*** 620,636 ****
}
/* Close DB connection, free memory, and remove the node from the
list */
! static void
remove_db_from_list(Dlelem *db_to_remove)
{
db_info *dbi = ((db_info *) DLE_VAL(db_to_remove));
!     if (args->debug >= 1)
!     {
!         sprintf(logbuffer, "Removing db: %s from list.", dbi->dbname);
!         log_entry(logbuffer);
!         fflush(LOGOUTPUT);
!     }
DLRemove(db_to_remove);
if (dbi->conn)
db_disconnect(dbi);
--- 485,497 ----
}
/* Close DB connection, free memory, and remove the node from the
list */
! void
remove_db_from_list(Dlelem *db_to_remove)
{
db_info *dbi = ((db_info *) DLE_VAL(db_to_remove));
!     ereport(DEBUG1,(errmsg("pg_autovacuum: Removing db: %s from 
list.", dbi->dbname)));
!    
DLRemove(db_to_remove);
if (dbi->conn)
db_disconnect(dbi);
***************
*** 649,659 ****
free(dbi->password);
dbi->password = NULL;
}
-     if (dbi->table_list)
-     {
-         free_tbl_list(dbi->table_list);
-         dbi->table_list = NULL;
-     }
if (dbi)
{
free(dbi);
--- 510,515 ----
***************
*** 664,670 ****
/* Function is called before program exit to free all memory
mostly it's just to keep valgrind happy */
! static void
free_db_list(Dllist *db_list)
{
Dlelem       *db_elem = DLGetHead(db_list);
--- 520,526 ----
/* Function is called before program exit to free all memory
mostly it's just to keep valgrind happy */
! void
free_db_list(Dllist *db_list)
{
Dlelem *db_elem = DLGetHead(db_list);
***************
*** 680,686 ****
DLFreeList(db_list);
}
! static void
print_db_list(Dllist *db_list, int print_table_lists)
{
Dlelem       *db_elem = DLGetHead(db_list);
--- 536,542 ----
DLFreeList(db_list);
}
! void
print_db_list(Dllist *db_list, int print_table_lists)
{
Dlelem *db_elem = DLGetHead(db_list);
***************
*** 692,726 ****
}
}

! static void
print_db_info(db_info * dbi, int print_tbl_list)
{
! sprintf(logbuffer, "dbname: %s", (dbi->dbname) ? dbi->dbname :
"(null)");
! log_entry(logbuffer);
!
! sprintf(logbuffer, " oid: %u", dbi->oid);
! log_entry(logbuffer);
!
! sprintf(logbuffer, " username: %s", (dbi->username) ?
dbi->username : "(null)");
! log_entry(logbuffer);
!
! sprintf(logbuffer, " password: %s", (dbi->password) ?
dbi->password : "(null)");
! log_entry(logbuffer);

if (dbi->conn != NULL)
! log_entry(" conn is valid, (connected)");
else
! log_entry(" conn is null, (not connected)");
! ! sprintf(logbuffer, " default_analyze_threshold: %li",
dbi->analyze_threshold);
! log_entry(logbuffer);
!
! sprintf(logbuffer, " default_vacuum_threshold: %li",
dbi->vacuum_threshold);
! log_entry(logbuffer);
!
! fflush(LOGOUTPUT);
! if (print_tbl_list > 0)
! print_table_list(dbi->table_list);
}
/* End of DB List Management Function */
--- 548,565 ----
}
}
! void
print_db_info(db_info * dbi, int print_tbl_list)
{
! ereport(DEBUG3,(errmsg("pg_autovacuum: dbname = %s",
(dbi->dbname) ? dbi->dbname : "(null)")));
! ereport(DEBUG3,(errmsg(" oid: %u", dbi->oid)));
! ereport(DEBUG3,(errmsg(" username: %s", (dbi->username) ?
dbi->username : "(null)")));
! ereport(DEBUG3,(errmsg(" password: %s", (dbi->password) ?
dbi->password : "(null)")));

if (dbi->conn != NULL)
! ereport(DEBUG3,(errmsg(" conn is valid, (connected)")));
else
! ereport(DEBUG3,(errmsg(" conn is null, (not connected)")));
}

/* End of DB List Management Function */
***************
*** 728,753 ****
/* Beginning of misc Functions */

/* Perhaps add some test to this function to make sure that the
stats we need are available */
! static PGconn *
db_connect(db_info * dbi)
{
PGconn *db_conn =
! PQsetdbLogin(args->host, args->port, NULL, NULL, dbi->dbname,
dbi->username, dbi->password);

if (PQstatus(db_conn) != CONNECTION_OK)
{
! sprintf(logbuffer, "Failed connection to database %s with
error: %s.",
! dbi->dbname, PQerrorMessage(db_conn));
! log_entry(logbuffer);
! fflush(LOGOUTPUT);
PQfinish(db_conn);
db_conn = NULL;
}
return db_conn;
} /* end of db_connect() */
! static void
db_disconnect(db_info * dbi)
{
if (dbi->conn != NULL)
--- 567,590 ----
/* Beginning of misc Functions */
/* Perhaps add some test to this function to make sure that the
stats we need are available */
! PGconn *
db_connect(db_info * dbi)
{
PGconn *db_conn =
! PQsetdbLogin(NULL,NULL, NULL, NULL, dbi->dbname,
dbi->username, dbi->password);

if (PQstatus(db_conn) != CONNECTION_OK)
{
! ereport(LOG,(errmsg("pg_autovacuum: Failed connection to
database %s with error: %s.",
! dbi->dbname, PQerrorMessage(db_conn))));
PQfinish(db_conn);
db_conn = NULL;
}
return db_conn;
} /* end of db_connect() */

! void
db_disconnect(db_info * dbi)
{
if (dbi->conn != NULL)
***************
*** 757,778 ****
}
}
! static int
! check_stats_enabled(db_info * dbi)
! {
!     PGresult   *res;
!     int            ret = 0;
! !     res = send_query("SHOW stats_row_level", dbi);
!     if (res != NULL)
!     {
!         ret = strcmp("on", PQgetvalue(res, 0, PQfnumber(res, 
"stats_row_level")));
!         PQclear(res);
!     }
!     return ret;
! }
! ! static PGresult *
send_query(const char *query, db_info * dbi)
{
PGresult   *res;
--- 594,600 ----
}
}
! PGresult *
send_query(const char *query, db_info * dbi)
{
PGresult *res;
***************
*** 780,811 ****
if (dbi->conn == NULL)
return NULL;

! if (args->debug >= 4)
! log_entry(query);

res = PQexec(dbi->conn, query);
if (!res)
{
!         sprintf(logbuffer,
!            "Fatal error occured while sending query (%s) to 
database %s",
!                 query, dbi->dbname);
!         log_entry(logbuffer);
!         sprintf(logbuffer, "The error is [%s]", 
PQresultErrorMessage(res));
!         log_entry(logbuffer);
!         fflush(LOGOUTPUT);
return NULL;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK &&
PQresultStatus(res) != PGRES_COMMAND_OK)
{
!         sprintf(logbuffer,
!           "Can not refresh statistics information from the database 
%s.",
!                 dbi->dbname);
!         log_entry(logbuffer);
!         sprintf(logbuffer, "The error is [%s]", 
PQresultErrorMessage(res));
!         log_entry(logbuffer);
!         fflush(LOGOUTPUT);
PQclear(res);
return NULL;
}
--- 602,622 ----
if (dbi->conn == NULL)
return NULL;
! elog(DEBUG5, query);

res = PQexec(dbi->conn, query);

if (!res)
{
! ereport(ERROR,(errmsg("pg_autovacuum: Fatal error occured
while sending query (%s) to database %s; The error is [%s]",
! query, dbi->dbname, PQresultErrorMessage(res))));
return NULL;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK &&
PQresultStatus(res) != PGRES_COMMAND_OK)
{
! ereport(ERROR,(errmsg("pg_autovacuum: Fatal error occured
while sending query (%s) to database %s; The error is [%s]",
! query, dbi->dbname, PQresultErrorMessage(res))));
PQclear(res);
return NULL;
}
***************
*** 813,1055 ****
} /* End of send_query() */

static void
! free_cmd_args()
{
! if (args != NULL)
{
! if (args->user != NULL)
! free(args->user);
! if (args->password != NULL)
! free(args->password);
! free(args);
}
! }
! ! static cmd_args *
! get_cmd_args(int argc, char *argv[])
! {
! int c;

! args = (cmd_args *) malloc(sizeof(cmd_args));
! args->sleep_base_value = SLEEPBASEVALUE;
! args->sleep_scaling_factor = SLEEPSCALINGFACTOR;
! args->vacuum_base_threshold = VACBASETHRESHOLD;
! args->vacuum_scaling_factor = VACSCALINGFACTOR;
! args->analyze_base_threshold = -1;
! args->analyze_scaling_factor = -1;
! args->debug = AUTOVACUUM_DEBUG;
! args->daemonize = 0;
! args->user = 0;
! args->password = 0;
! args->host = 0;
! args->logfile = 0;
! args->port = 0;

! /*
! * Fixme: Should add some sanity checking such as positive integer
! * values etc
! */
! while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hD"))
!= -1)
{
! switch (c)
! {
! case 's':
! args->sleep_base_value = atoi(optarg);
! break;
! case 'S':
! args->sleep_scaling_factor = atof(optarg);
! break;
! case 'v':
! args->vacuum_base_threshold = atoi(optarg);
! break;
! case 'V':
! args->vacuum_scaling_factor = atof(optarg);
! break;
! case 'a':
! args->analyze_base_threshold = atoi(optarg);
! break;
! case 'A':
! args->analyze_scaling_factor = atof(optarg);
! break;
! case 'D':
! args->daemonize++;
! break;
! case 'd':
! args->debug = atoi(optarg);
! break;
! case 'U':
! args->user = optarg;
! break;
! case 'P':
! args->password = optarg;
! break;
! case 'H':
! args->host = optarg;
! break;
! case 'L':
! args->logfile = optarg;
! break;
! case 'p':
! args->port = optarg;
! break;
! case 'h':
! usage();
! exit(0);
! default:
! ! /*
! * It's here that we know that things are
invalid... It is
! * not forcibly an error to call usage
! */
! fprintf(stderr, "Error: Invalid Command Line
Options.\n");
! usage();
! exit(1);
! break;
! }

! /*
! * if values for insert thresholds are not specified, then
they
! * default to 1/2 of the delete values
! */
! if (args->analyze_base_threshold == -1)
! args->analyze_base_threshold =
args->vacuum_base_threshold / 2;
! if (args->analyze_scaling_factor == -1)
! args->analyze_scaling_factor =
args->vacuum_scaling_factor / 2;
}
! return args;
! }

! static void
! usage()
! {
! int i = 0;
! float f = 0;

! fprintf(stderr, "usage: pg_autovacuum \n");
! fprintf(stderr, " [-D] Daemonize (Detach from tty and run in
the background)\n");
! i = AUTOVACUUM_DEBUG;
! fprintf(stderr, " [-d] debug (debug level=0,1,2,3;
default=%i)\n", i);
! ! i = SLEEPBASEVALUE;
! fprintf(stderr, " [-s] sleep base value (default=%i)\n", i);
! f = SLEEPSCALINGFACTOR;
! fprintf(stderr, " [-S] sleep scaling factor (default=%f)\n", f);
! ! i = VACBASETHRESHOLD;
! fprintf(stderr, " [-v] vacuum base threshold (default=%i)\n",
i);
! f = VACSCALINGFACTOR;
! fprintf(stderr, " [-V] vacuum scaling factor (default=%f)\n",
f);
! i = i / 2;
! fprintf(stderr, " [-a] analyze base threshold
(default=%i)\n", i);
! f = f / 2;
! fprintf(stderr, " [-A] analyze scaling factor
(default=%f)\n", f);
! ! fprintf(stderr, " [-L] logfile (default=none)\n");
! ! fprintf(stderr, " [-U] username (libpq default)\n");
! fprintf(stderr, " [-P] password (libpq default)\n");
! fprintf(stderr, " [-H] host (libpq default)\n");
! fprintf(stderr, " [-p] port (libpq default)\n");

! fprintf(stderr, " [-h] help (Show this output)\n");
! }

! static void
! print_cmd_args()
! {
! sprintf(logbuffer, "Printing command_args");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->host=%s", (args->host) ? args->host
: "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->port=%s", (args->port) ? args->port
: "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->username=%s", (args->user) ?
args->user : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->password=%s", (args->password) ?
args->password : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->logfile=%s", (args->logfile) ?
args->logfile : "(null)");
! log_entry(logbuffer);
! sprintf(logbuffer, " args->daemonize=%i", args->daemonize);
! log_entry(logbuffer);
! ! sprintf(logbuffer, " args->sleep_base_value=%i",
args->sleep_base_value);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->sleep_scaling_factor=%f",
args->sleep_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->vacuum_base_threshold=%i",
args->vacuum_base_threshold);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->vacuum_scaling_factor=%f",
args->vacuum_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->analyze_base_threshold=%i",
args->analyze_base_threshold);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->analyze_scaling_factor=%f",
args->analyze_scaling_factor);
! log_entry(logbuffer);
! sprintf(logbuffer, " args->debug=%i", args->debug);
! log_entry(logbuffer);

- fflush(LOGOUTPUT);
}

/* Beginning of AutoVacuum Main Program */
! int
! main(int argc, char *argv[])
{
char buf[256];
int j = 0,
loops = 0;
- - /* int numInserts, numDeletes, */
int sleep_secs;
Dllist *db_list;
! Dlelem *db_elem,
! *tbl_elem;
db_info *dbs;
- tbl_info *tbl;
PGresult *res = NULL;
double diff;
struct timeval now,
then;
! ! args = get_cmd_args(argc, argv); /* Get Command Line Args
and put
! * them in the args struct */
! ! /* Dameonize if requested */
! if (args->daemonize == 1)
! daemonize();
! ! if (args->logfile)
! {
! LOGOUTPUT = fopen(args->logfile, "a");
! if (!LOGOUTPUT)
! {
! fprintf(stderr, "Could not open log file - [%s]\n",
args->logfile);
! exit(-1);
! }
! }
! else
! LOGOUTPUT = stderr;
! if (args->debug >= 2)
! print_cmd_args();
! /* Init the db list with template1 */
db_list = init_db_list();
if (db_list == NULL)
! return 1;

- if (check_stats_enabled(((db_info *)
DLE_VAL(DLGetHead(db_list)))) != 0)
- {
- log_entry("Error: GUC variable stats_row_level must be
enabled.");
- log_entry(" Please fix the problems and try again.");
- fflush(LOGOUTPUT);
- - exit(1);
- }
- gettimeofday(&then, 0); /* for use later to caluculate
sleep time */

while (1)
! { /* Main Loop */
db_elem = DLGetHead(db_list); /* Reset cur_db_node to the
* beginning of the db_list */
--- 624,768 ----
}    /* End of send_query() */
+ /* Get a password from the password file. */
static void
! GetAutovacuumUserAndPasswordFromFile()
{
! FILE *fp;
! #define LINELEN NAMEDATALEN*2
! char buf[LINELEN];
! char *pgpassfile,
! *t = buf,
! *username,
! *password;
! int len;
! struct stat stat_buf;
! !
! pgpassfile = malloc(strlen(DataDir) + 1 +
strlen(AUTOVACPASSFILE) + 1);
! if (!pgpassfile)
{
! printf("out of memory");
! return;
}
! elog(DEBUG3,"pg_autovacuum: autovac.passwd should be: %s/%s",
DataDir, AUTOVACPASSFILE);

! sprintf(pgpassfile, "%s/%s", DataDir, AUTOVACPASSFILE);

! /* If password file cannot be opened, ignore it. */
! if (stat(pgpassfile, &stat_buf) == -1)
{
! elog(DEBUG3,"pg_autovacuum: %s cannot be opened",pgpassfile);
! free(pgpassfile);
! return;
! }

! #ifndef WIN32
! /* If password file is insecure, alert the user and ignore it. */
! if (stat_buf.st_mode & (S_IRWXG | S_IRWXO))
! {
! ereport(WARNING,(errmsg("pg_autovacuum: Password file %s
has world or group read access; permission should be u=rw
(0600)",pgpassfile)));
! free(pgpassfile);
! return;
}
! #endif

! fp = fopen(pgpassfile, "r");
! free(pgpassfile);
! if (fp == NULL)
! return;

! fgets(buf, LINELEN - 1, fp);

! len = strlen(buf);
! if (len < 3)
! return;
!
! /* Remove trailing newline */
! if (buf[len - 1] == '\n')
! buf[len - 1] = 0;

! username = t; /* Password is always first */
! if(*username == ':')
! username = NULL;
!
! password = NULL;
! while(*t != NULL) /* Find : since it marks the beginning of
the password */
! {
! if (*(t+1) == ':' )
! {
! *(t+1) = NULL;
! password = t+2;
! }
! t++;
! }
!
! ereport(DEBUG3,(errmsg("pg_autovacuum: username from
autovac.passwd file is: %s ",username)));
! fclose(fp);
!
! /* Now if we have anything for usename and password we set the
pg_user and pg_passwd globals */
! if((username != NULL) && (strlen(username) > 1) &&
(strlen(username) < NAMEDATALEN))
! {
! pg_user = malloc(strlen(username) + 1);
! strcpy(pg_user,username);
! }
! if((password != NULL) && (strlen(password) > 1) &&
(strlen(password) < NAMEDATALEN))
! {
! pg_user_password = malloc(strlen(password) + 1);
! strcpy(pg_user_password,password);
! }
! return;
! ! #undef LINELEN

}

+ /* Beginning of AutoVacuum Main Program */
! void AutoVacLoop(void)
{
char buf[256];
int j = 0,
loops = 0;
int sleep_secs;
Dllist *db_list;
! Dlelem *db_elem;
db_info *dbs;
PGresult *res = NULL;
double diff;
struct timeval now,
then;
!
! GetAutovacuumUserAndPasswordFromFile();
!
/* Init the db list with template1 */
db_list = init_db_list();
if (db_list == NULL)
! ereport(ERROR,(errmsg("pg_autovacuum: Error creating
db_list, exiting.")));

gettimeofday(&then, 0); /* for use later to caluculate
sleep time */

+ /* Main Loop */
while (1)
! {
! /*
! * Emergency bailout if postmaster has died. This is to
avoid the
! * necessity for manual cleanup of all postmaster children.
! */
! if (!PostmasterIsAlive(true))
! exit(1);
! ! if (got_SIGHUP)
! {
! got_SIGHUP = false;
! ProcessConfigFile(PGC_SIGHUP);
! }
! if (shutdown_requested)
! {
! /* Normal exit from pg_autovacuum is here */
! proc_exit(0); /* done */
! }
! db_elem = DLGetHead(db_list); /* Reset cur_db_node to the
* beginning of the db_list */

***************
*** 1061,1095 ****
if (dbs->conn == NULL)
{ /* Serious problem: We can't
connect to
* template1 */
! log_entry("Error: Cannot connect to template1,
exiting.");
! fflush(LOGOUTPUT);
! fclose(LOGOUTPUT);
! exit(1);
}
}
!         if (loops % UPDATE_INTERVAL == 0)        /* Update the list 
if it's
!                                                  * time */
!             update_db_list(db_list);    /* Add and remove databases 
from
!                                          * the list */
!          while (db_elem != NULL)
!         {                        /* Loop through databases in list */
dbs = ((db_info *) DLE_VAL(db_elem));        /* get 
pointer to
* cur_db's 
db_info
* struct */
if (dbs->conn == NULL)
dbs->conn = db_connect(dbs);
!              if (dbs->conn != NULL)
{
-                 if (loops % UPDATE_INTERVAL == 0)        /* Update 
the list if
-                                                          * it's 
time */
-                     update_table_list(dbs);        /* Add and 
remove tables
-                                                  * from the list */
-                  if (xid_wraparound_check(dbs) == 0)
{
res = send_query(TABLE_STATS_QUERY, dbs);    /* 
Get an updated
* 
snapshot of this dbs
* 
table stats */
--- 774,806 ----
if (dbs->conn == NULL)
{                    /* Serious problem: We can't 
connect to
* template1 */
!                 ereport(ERROR,(errmsg("pg_autovacuum: Cannot 
connect to template1, exiting.")));
}
}
!         update_db_list(db_list);    /* Add and remove databases from
!                                      * the list */
!        
!         /* Loop through databases in list */
while (db_elem != NULL)
!         {
dbs = ((db_info *) DLE_VAL(db_elem));        /* get 
pointer to
* cur_db's 
db_info
* struct */
if (dbs->conn == NULL)
dbs->conn = db_connect(dbs);
!                
if (dbs->conn != NULL)
{
if (xid_wraparound_check(dbs) == 0)
{
+                     long curr_vacuum_count, curr_analyze_count;
+                     long cnt_at_last_vacuum, cnt_at_last_analyze;
+                     float vacuum_threshold, analyze_threshold;
+                     char table_name[512];
+                    
+                     update_pg_autovacuum_table(dbs); 
+                        
res = send_query(TABLE_STATS_QUERY, dbs);    /* 
Get an updated
* 
snapshot of this dbs
* 
table stats */
***************
*** 1097,1174 ****
{
for (j = 0; j < PQntuples(res); j++)
{            /* loop through result set */
!                             tbl_elem = 
DLGetHead(dbs->table_list);    /* Reset tbl_elem to top
!                                                                     
* of dbs->table_list */
!                             while (tbl_elem != NULL)
!                             {        /* Loop through tables in list */
!                                 tbl = ((tbl_info *) 
DLE_VAL(tbl_elem));        /* set tbl_info =
!                                                                             
* current_table */
!                                 if (tbl->relid == 
atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))))
!                                 {
!                                     tbl->curr_analyze_count =
!                                         (atol(PQgetvalue(res, j, 
PQfnumber(res, "n_tup_ins"))) +
!                                         atol(PQgetvalue(res, j, 
PQfnumber(res, "n_tup_upd"))) +
!                                         atol(PQgetvalue(res, j, 
PQfnumber(res, "n_tup_del"))));
!                                     tbl->curr_vacuum_count =
!                                         (atol(PQgetvalue(res, j, 
PQfnumber(res, "n_tup_del"))) +
!                                         atol(PQgetvalue(res, j, 
PQfnumber(res, "n_tup_upd"))));
!    
!                                     /*
!                                     * Check numDeletes to see if we 
need to
!                                     * vacuum, if so: Run vacuum 
analyze
!                                     * (adding analyze is small so 
we might as
!                                     * well) Update table thresholds 
and
!                                     * related information if 
numDeletes is
!                                     * not big enough for vacuum 
then check
!                                     * numInserts for analyze
!                                     */
!                                     if (tbl->curr_vacuum_count - 
tbl->CountAtLastVacuum >= tbl->vacuum_threshold)
!                                     {
!                                         /*
!                                         * if relisshared = t and 
database !=
!                                         * template1 then only do an 
analyze
!                                         */
!                                         if (tbl->relisshared > 0 && 
strcmp("template1", dbs->dbname))
!                                             snprintf(buf, 
sizeof(buf), "ANALYZE %s", tbl->table_name);
!                                         else
!                                             snprintf(buf, 
sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
!                                         if (args->debug >= 1)
!                                         {
!                                             sprintf(logbuffer, 
"Performing: %s", buf);
!                                             log_entry(logbuffer);
!                                             fflush(LOGOUTPUT);
!                                         }
!                                         send_query(buf, dbs);
!                                         
update_table_thresholds(dbs, tbl, VACUUM_ANALYZE);
!                                         if (args->debug >= 2)
!                                             print_table_info(tbl);
!                                     }
!                                     else if 
(tbl->curr_analyze_count - tbl->CountAtLastAnalyze >= 
tbl->analyze_threshold)
!                                     {
!                                         snprintf(buf, sizeof(buf), 
"ANALYZE %s", tbl->table_name);
!                                         if (args->debug >= 1)
!                                         {
!                                             sprintf(logbuffer, 
"Performing: %s", buf);
!                                             log_entry(logbuffer);
!                                             fflush(LOGOUTPUT);
!                                         }
!                                         send_query(buf, dbs);
!                                         
update_table_thresholds(dbs, tbl, ANALYZE_ONLY);
!                                         if (args->debug >= 2)
!                                             print_table_info(tbl);
!                                     }
!                                    
!                                     break;    /* once we have found 
a match, no
!                                             * need to keep 
checking. */
!                                 }
!                                
/*
!                                 * Advance the table pointers for 
the next
!                                 * loop
*/
!                                 tbl_elem = DLGetSucc(tbl_elem);
!                             }        /* end for table while loop */
}            /* end for j loop (tuples in 
PGresult) */
}            /* end if (res != NULL) */
}                /* close of 
if(xid_wraparound_check()) */
--- 808,877 ----
{
for (j = 0; j < PQntuples(res); j++)
{            /* loop through result set */
!                             /*
!                             * Check to see if we need to
!                             * vacuum analyze, or just analyze
!                             * if needed, do so, then update table 
thresholds and
!                             * related information
!                             */
!                             curr_vacuum_count = 
(atol(PQgetvalue(res, j, PQfnumber(res, "curr_vacuum_count"))));
!                             curr_analyze_count = 
(atol(PQgetvalue(res, j, PQfnumber(res, "curr_analyze_count"))));
!                             cnt_at_last_vacuum = 
(atol(PQgetvalue(res, j, PQfnumber(res, "cnt_at_last_vacuum"))));
!                             cnt_at_last_analyze = 
(atol(PQgetvalue(res, j, PQfnumber(res, "cnt_at_last_analyze"))));
!                             vacuum_threshold = 
(atof(PQgetvalue(res, j, PQfnumber(res, "vacuum_threshold"))));
!                             analyze_threshold = 
(atof(PQgetvalue(res, j, PQfnumber(res, "analyze_threshold"))));
!                             strcpy(table_name,"\"");
!                             strcat(table_name, PQgetvalue(res,j, 
PQfnumber(res, "schemaname")));
!                             strcat(table_name,"\".\"");
!                             strcat(table_name, PQgetvalue(res,j, 
PQfnumber(res, "relname")));
!                             strcat(table_name,"\"");
!                            
!                             if ((curr_vacuum_count - 
cnt_at_last_vacuum) >= vacuum_threshold)
!                             {
/*
!                                 * if relisshared = t and database !=
!                                 * template1 then only do an analyze
*/
!                                 if ((strcmp("t",PQgetvalue(res,j, 
PQfnumber(res, "relisshared"))) == 0 ) && 
(strcmp(dbs->dbname,"template1") == 0 ))
!                                     snprintf(buf, sizeof(buf), 
"ANALYZE %s", table_name);
!                                 else
!                                     snprintf(buf, sizeof(buf), 
"VACUUM ANALYZE %s", table_name);
!                                
!                                 
ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
!                                
!                                 send_query(buf, dbs);
!                                 update_table_thresholds(dbs, 
atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))), VACUUM_ANALYZE);
!                             }
!                             else if (curr_analyze_count - 
cnt_at_last_analyze >= analyze_threshold)
!                             {
!                                 snprintf(buf, sizeof(buf), "ANALYZE 
%s", table_name);
!                                
!                                 
ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
!                                
!                                 send_query(buf, dbs);
!                                 update_table_thresholds(dbs, 
atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))), ANALYZE_ONLY);
!                             }
!                            
!                             if (curr_vacuum_count < 
cnt_at_last_vacuum)
!                             {
!                                 /*
!                                  * this should only happen if the 
stats system gets reset
!                                  */
!                                 snprintf(buf, sizeof(buf), "update 
pg_autovacuum set cnt_at_last_vacuum = %li where table_oid = %u", 
curr_vacuum_count, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))));
!                                
!                                 
ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
!                                 send_query(buf, dbs);
!                             }
!                             if (curr_analyze_count < 
cnt_at_last_analyze)
!                             {
!                                 /*
!                                  * this should only happen if the 
stats system gets reset
!                                  */
!                                 snprintf(buf, sizeof(buf), "update 
pg_autovacuum set cnt_at_last_analyze = %li where table_oid = %u", 
curr_analyze_count, atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))));
!
ereport(DEBUG1,(errmsg("pg_autovacuum: Performing: %s", buf)));
! send_query(buf, dbs);
! }
} /* end for j loop (tuples in
PGresult) */
} /* end if (res != NULL) */
} /* close of
if(xid_wraparound_check()) */
***************
*** 1179,1212 ****
}
db_elem = DLGetSucc(db_elem); /* move on to next DB
* regardless */
! } /* end of db_list while loop */
! /* Figure out how long to sleep etc ... */
gettimeofday(&now, 0);
diff = (int) (now.tv_sec - then.tv_sec) * 1000000.0 + (int)
(now.tv_usec - then.tv_usec);
! ! sleep_secs = args->sleep_base_value +
args->sleep_scaling_factor * diff / 1000000.0;
loops++;
! if (args->debug >= 2)
! {
! sprintf(logbuffer,
! "%i All DBs checked in: %.0f usec, will sleep for %i
secs.",
! loops, diff, sleep_secs);
! log_entry(logbuffer);
! fflush(LOGOUTPUT);
! }
! ! sleep(sleep_secs); /* Larger Pause between outer
loops */
! gettimeofday(&then, 0); /* Reset time counter */
! ! } /* end of while loop */
/*
* program is exiting, this should never run, but is here to make
* compiler / valgrind happy
*/
free_db_list(db_list);
!     free_cmd_args();
!     return EXIT_SUCCESS;
}
--- 882,911 ----
}
db_elem = DLGetSucc(db_elem);        /* move on to next DB
* regardless */
!         }    /* end of db_list while loop */
!        
/* Figure out how long to sleep etc ... */
gettimeofday(&now, 0);
diff = (int) (now.tv_sec - then.tv_sec) * 1000000.0 + (int) 
(now.tv_usec - then.tv_usec);
!        
!         sleep_secs = SLEEPBASEVALUE + SLEEPSCALINGFACTOR * diff / 
1000000.0;
loops++;
!            
!         ereport(DEBUG2,(errmsg("pg_autovacuum: loop %i; All DBs 
checked in: %.0f usec, will sleep for %i secs.",
!                 loops, diff, sleep_secs)));
!        
!         /* Larger Pause between outer loops */
!         if (!(got_SIGHUP || shutdown_requested))
!             pg_usleep((long)(sleep_secs * 1000000L));
!        
gettimeofday(&then, 0); /* Reset time counter */
!        
!     }    /* end of while loop */
/*
* program is exiting, this should never run, but is here to make
* compiler / valgrind happy
*/
free_db_list(db_list);
!     proc_exit(0);
}
*** ./src/backend/postmaster/Makefile.orig    2004-08-04 
01:40:53.455982146 -0400
--- ./src/backend/postmaster/Makefile    2004-07-24 
02:00:34.000000000 -0400
***************
*** 12,18 ****
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o

all: SUBSYS.o
--- 12,18 ----
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o autovacuum.o

all: SUBSYS.o
*** ./src/backend/postmaster/postmaster.c.orig    2004-08-04 
01:40:40.707380679 -0400
--- ./src/backend/postmaster/postmaster.c    2004-08-03 
21:55:54.578203673 -0400
***************
*** 55,66 ****
*        The Postmaster cleans up after backends if they have an 
emergency
*        exit and/or core dump.
*
-  * Error Reporting:
-  *        Use write_stderr() only for reporting "interactive" errors
-  *        (essentially, bogus arguments on the command line).  Once 
the
-  *        postmaster is launched, use ereport().  In particular, 
don't use
-  *        write_stderr() for anything that occurs after pmdaemonize.
-  *
*-------------------------------------------------------------------------

*/
--- 55,60 ----
***************
*** 198,203 ****
--- 192,198 ----
/* PIDs of special child processes; 0 when not running */
static pid_t StartupPID = 0,
BgWriterPID = 0,
+             AutoVacPID = 0,
PgArchPID = 0,
PgStatPID = 0;
***************
*** 268,273 ****
--- 263,272 ----
static int    CountChildren(void);
static bool CreateOptsFile(int argc, char *argv[], char *fullprogname);
static pid_t StartChildProcess(int xlop);
+ static void
+ postmaster_error(const char *fmt,...)
+ /* This lets gcc check the format string for consistency. */
+ __attribute__((format(printf, 1, 2)));
#ifdef EXEC_BACKEND
***************
*** 298,303 ****
--- 297,303 ----
#define StartupDataBase() StartChildProcess(BS_XLOG_STARTUP)
#define StartBackgroundWriter() StartChildProcess(BS_XLOG_BGWRITER)
+ #define StartAutoVac() StartChildProcess(BS_XLOG_AUTOVAC)
/*
***************
*** 384,390 ****
#ifdef USE_ASSERT_CHECKING
SetConfigOption("debug_assertions", optarg, 
PGC_POSTMASTER, PGC_S_ARGV);
#else
!                 write_stderr("%s: assert checking is not compiled 
in\n", progname);
#endif
break;
case 'a':
--- 384,390 ----
#ifdef USE_ASSERT_CHECKING
SetConfigOption("debug_assertions", optarg, 
PGC_POSTMASTER, PGC_S_ARGV);
#else
!                 postmaster_error("assert checking is not compiled 
in");
#endif
break;
case 'a':
***************
*** 508,515 ****
}
default:
!                 write_stderr("Try \"%s --help\" for more 
information.\n",
!                              progname);
ExitPostmaster(1);
}
}
--- 508,516 ----
}
default:
! fprintf(stderr,
! gettext("Try \"%s --help\" for more
information.\n"),
! progname);
ExitPostmaster(1);
}
}
***************
*** 519,528 ****
*/
if (optind < argc)
{
! write_stderr("%s: invalid argument: \"%s\"\n",
! progname, argv[optind]);
! write_stderr("Try \"%s --help\" for more information.\n",
! progname);
ExitPostmaster(1);
}
--- 520,529 ----
*/
if (optind < argc)
{
!         postmaster_error("invalid argument: \"%s\"", argv[optind]);
!         fprintf(stderr,
!                 gettext("Try \"%s --help\" for more information.\n"),
!                 progname);
ExitPostmaster(1);
}
***************
*** 593,605 ****
* for lack of buffers. The specific choices here are somewhat
* arbitrary.
*/
! write_stderr("%s: the number of buffers (-B) must be at
least twice the number of allowed connections (-N) and at least
16\n", progname);
ExitPostmaster(1);
}

if (ReservedBackends >= MaxBackends)
{
! write_stderr("%s: superuser_reserved_connections must be
less than max_connections\n", progname);
ExitPostmaster(1);
}
--- 594,606 ----
* for lack of buffers.  The specific choices here are somewhat
* arbitrary.
*/
!         postmaster_error("the number of buffers (-B) must be at 
least twice the number of allowed connections (-N) and at least 16");
ExitPostmaster(1);
}
if (ReservedBackends >= MaxBackends)
{
! postmaster_error("superuser_reserved_connections must be
less than max_connections");
ExitPostmaster(1);
}

***************
*** 608,614 ****
*/
if (!CheckDateTokenTables())
{
! write_stderr("%s: invalid datetoken tables, please fix\n",
progname);
ExitPostmaster(1);
}
--- 609,615 ----
*/
if (!CheckDateTokenTables())
{
!         postmaster_error("invalid datetoken tables, please fix");
ExitPostmaster(1);
}
***************
*** 827,832 ****
--- 828,835 ----
*
* CAUTION: when changing this list, check for side-effects on 
the signal
* handling setup of child processes.  See tcop/postgres.c,
+      * bootstrap/bootstrap.c, postmaster/bgwriter.c, 
postmaster/pgstat.c,
+      * and postmaster/autovacuum.c.
* bootstrap/bootstrap.c, postmaster/bgwriter.c, 
postmaster/pgarch.c,
* and postmaster/pgstat.c.
*/
***************
*** 977,986 ****
fp = AllocateFile(path, PG_BINARY_R);
if (fp == NULL)
{
!         write_stderr("%s: could not find the database system\n"
!                      "Expected to find it in the directory \"%s\",\n"
!                      "but could not open file \"%s\": %s\n",
!                      progname, checkdir, path, strerror(errno));
ExitPostmaster(2);
}
FreeFile(fp);
--- 980,990 ----
fp = AllocateFile(path, PG_BINARY_R);
if (fp == NULL)
{
!         fprintf(stderr,
!                 gettext("%s: could not find the database system\n"
!                         "Expected to find it in the directory 
\"%s\",\n"
!                         "but could not open file \"%s\": %s\n"),
!                 progname, checkdir, path, strerror(errno));
ExitPostmaster(2);
}
FreeFile(fp);
***************
*** 1023,1030 ****
pid = fork();
if (pid == (pid_t) -1)
{
!         write_stderr("%s: could not fork background process: %s\n",
!                      progname, strerror(errno));
ExitPostmaster(1);
}
else if (pid)
--- 1027,1034 ----
pid = fork();
if (pid == (pid_t) -1)
{
!         postmaster_error("could not fork background process: %s",
!                          strerror(errno));
ExitPostmaster(1);
}
else if (pid)
***************
*** 1045,1052 ****
#ifdef HAVE_SETSID
if (setsid() < 0)
{
!         write_stderr("%s: could not dissociate from controlling 
TTY: %s\n",
!                      progname, strerror(errno));
ExitPostmaster(1);
}
#endif
--- 1049,1056 ----
#ifdef HAVE_SETSID
if (setsid() < 0)
{
!         postmaster_error("could not dissociate from controlling 
TTY: %s",
!                          strerror(errno));
ExitPostmaster(1);
}
#endif
***************
*** 1113,1121 ****
int            nSockets;
time_t        now,
last_touch_time;
!     struct timeval earlier,
later;
!     struct timezone tz;
gettimeofday(&earlier, &tz);
last_touch_time = time(NULL);
--- 1117,1125 ----
int            nSockets;
time_t        now,
last_touch_time;
!     struct         timeval earlier,
later;
!     struct         timezone tz;
gettimeofday(&earlier, &tz);
last_touch_time = time(NULL);
***************
*** 1220,1225 ****
--- 1224,1252 ----
kill(BgWriterPID, SIGUSR2);
}
+         /*
+          * If no AutoVacuum process is running, and we are not in
+          * a state that prevents it, start one.  It doesn't matter 
if this
+          * fails, we'll just try again later.
+          */
+         if (autovacuum_start_daemon)
+         {
+             if (pgstat_collect_tuplelevel)
+             {
+                 if (AutoVacPID == 0 && StartupPID == 0 && !FatalError)
+                 {
+                     AutoVacPID = StartAutoVac();
+                    
+                     /* If shutdown is pending, set it going */
+                     if (Shutdown > NoShutdown && AutoVacPID != 0)
+                         kill(AutoVacPID, SIGUSR2);
+                 }
+             }
+         }   
+         else if(AutoVacPID > 0)
+             kill(AutoVacPID, SIGUSR2);
+            
+        
/* If we have lost the archiver, try to start a new one */
if (XLogArchivingActive() && PgArchPID == 0 &&              
StartupPID == 0 && !FatalError && Shutdown == NoShutdown)
***************
*** 1587,1592 ****
--- 1614,1634 ----
backendPID = (int) ntohl(canc->backendPID);
cancelAuthCode = (long) ntohl(canc->cancelAuthCode);
+     if (backendPID == BgWriterPID)
+     {
+         ereport(DEBUG2,
+                 (errmsg_internal("ignoring cancel request for 
bgwriter process %d",
+                                  backendPID)));
+         return;
+     }
+     if (backendPID == AutoVacPID)
+     {
+         ereport(DEBUG2,
+                 (errmsg_internal("ignoring cancel request for 
autovacuum process %d",
+                                  backendPID)));
+         return;
+     }
+      /*
* See if we have a matching backend.  In the EXEC_BACKEND case, we
* can no longer access the postmaster's own backend list, and must
***************
*** 1768,1773 ****
--- 1810,1817 ----
SignalChildren(SIGHUP);
if (BgWriterPID != 0)
kill(BgWriterPID, SIGHUP);
+         if (AutoVacPID != 0)
+             kill(AutoVacPID, SIGHUP);
if (PgArchPID != 0)
kill(PgArchPID, SIGHUP);
/* PgStatPID does not currently need SIGHUP */
***************
*** 1828,1833 ****
--- 1872,1881 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+             /* I don't think we need to Start the autovac process 
if not running */
+             /* And tell it to shut down */
+             if (AutoVacPID != 0)
+                 kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to 
do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 1875,1880 ****
--- 1923,1931 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+             /* And tell it to shut down */
+             if (AutoVacPID != 0)
+                 kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to 
do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 1896,1901 ****
--- 1947,1954 ----
kill(StartupPID, SIGQUIT);
if (BgWriterPID != 0)
kill(BgWriterPID, SIGQUIT);
+             if (AutoVacPID != 0)
+                 kill(AutoVacPID, SIGQUIT);
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
if (PgStatPID != 0)
***************
*** 1984,1989 ****
--- 2037,2055 ----
BgWriterPID = StartBackgroundWriter();
/*
+              *  We won't start autovac here since we are in no 
hurry to start it up
+              *  But we will take this opportunity to make some 
noise about invalid
+              *  combinations of options in postgresql.conf
+              */
+             if (autovacuum_start_daemon)
+             {
+                 if (!pgstat_collect_tuplelevel)
+                     ereport(WARNING,(errmsg("pg_autovacuum: autovac 
is enabled, but requires stats_row_level which is not enabled")));
+                 if (pgstat_collect_resetonpmstart)
+                     ereport(WARNING,(errmsg("pg_autovacuum: 
stats_reset_on_server_start should be disabled for optimal 
performance")));
+             }   
+              +             /*
* Go to shutdown mode if a shutdown request was pending.
* Otherwise, try to start the archiver and stats 
collector too.
*/
***************
*** 1996,2001 ****
--- 2062,2073 ----
PgStatPID = pgstat_start();
}
+             /*
+              * Shutdown autovac if a shutdown request was pending.
+              */
+             if (Shutdown > NoShutdown && AutoVacPID != 0)
+                 kill(AutoVacPID, SIGUSR2);
+            
continue;
}
***************
*** 2032,2037 ****
--- 2104,2129 ----
}
/*
+          * Was it the autovac?
+          */
+         if (AutoVacPID != 0 && pid == AutoVacPID)
+         {
+             AutoVacPID = 0;
+             if (exitstatus != 0)
+             {
+                 /*
+                 * Any unexpected exit of the autovacuum is treated 
as a crash.
+                 * FIXME: This is useful for debugging autovac, but 
I think it should be +                 * ripped out before final 
patch, autovac shouldn't crash the postmaster
+                 */
+                 LogChildExit(LOG, gettext("pg_autovacuum process"),
+                             pid, exitstatus);
+                 HandleChildCrash(pid, exitstatus);
+                 continue;
+             }
+         }
+        
+         /*
* Was it the archiver?  If so, just try to start a new
* one; no need to force reset of the rest of the system.  
(If fail,
* we'll try again in future cycles of the main loop.)
***************
*** 2077,2083 ****
* StartupDataBase.  (We can ignore the archiver and stats 
processes
* here since they are not connected to shmem.)
*/
!         if (DLGetHead(BackendList) || StartupPID != 0 || 
BgWriterPID != 0)
goto reaper_done;
ereport(LOG,
(errmsg("all server processes terminated; 
reinitializing")));
--- 2169,2175 ----
* StartupDataBase.  (We can ignore the archiver and stats 
processes
* here since they are not connected to shmem.)
*/
!         if (DLGetHead(BackendList) || StartupPID != 0 || 
BgWriterPID != 0 || AutoVacPID != 0)
goto reaper_done;
ereport(LOG,
(errmsg("all server processes terminated; 
reinitializing")));
***************
*** 2100,2105 ****
--- 2192,2200 ----
/* And tell it to shut down */
if (BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
+         /* Tell AutoVac to shut down */
+         if (AutoVacPID != 0)
+             kill(AutoVacPID, SIGUSR2);
/* Tell pgarch to shut down too; nothing left for it to do */
if (PgArchPID != 0)
kill(PgArchPID, SIGQUIT);
***************
*** 2265,2270 ****
--- 2360,2379 ----
}
FatalError = true;
+ +     /* Take care of the autovacuum too */
+     if (pid == AutoVacPID)
+         AutoVacPID = 0;
+     else if (AutoVacPID != 0 && !FatalError)
+     {
+         ereport(DEBUG2,
+                 (errmsg_internal("sending %s to process %d",
+                                  (SendStop ? "SIGSTOP" : "SIGQUIT"),
+                                  (int) AutoVacPID)));
+         kill(AutoVacPID, (SendStop ? SIGSTOP : SIGQUIT));
+     }
+ +     FatalError = true;
}
/*
***************
*** 3256,3261 ****
--- 3365,3374 ----
ereport(LOG,
(errmsg("could not fork background writer 
process: %m")));
break;
+             case BS_XLOG_AUTOVAC:
+                 ereport(LOG,
+                         (errmsg("could not fork auto vacuum 
process: %m")));
+                 break;
default:
ereport(LOG,
(errmsg("could not fork process: %m")));
***************
*** 3310,3315 ****
--- 3423,3446 ----
return true;
}
+ /*
+  * This should be used only for reporting "interactive" errors 
(essentially,
+  * bogus arguments on the command line).  Once the postmaster is 
launched,
+  * use ereport.  In particular, don't use this for anything that 
occurs
+  * after pmdaemonize.
+  */
+ static void
+ postmaster_error(const char *fmt,...)
+ {
+     va_list        ap;
+ +     fprintf(stderr, "%s: ", progname);
+     va_start(ap, fmt);
+     vfprintf(stderr, gettext(fmt), ap);
+     va_end(ap);
+     fprintf(stderr, "\n");
+ }
+  
#ifdef EXEC_BACKEND
***************
*** 3749,3755 ****
if (r == WAIT_OBJECT_0)
pg_queue_signal(SIGCHLD);
else
!         write_stderr("ERROR: failed to wait on child process 
handle: %d\n",
(int) GetLastError());
CloseHandle(procHandle);
return 0;
--- 3880,3886 ----
if (r == WAIT_OBJECT_0)
pg_queue_signal(SIGCHLD);
else
!         fprintf(stderr, "ERROR: failed to wait on child process 
handle: %d\n",
(int) GetLastError());
CloseHandle(procHandle);
return 0;
*** ./src/backend/utils/misc/guc.c.orig    2004-08-04 
01:41:20.000000000 -0400
--- ./src/backend/utils/misc/guc.c    2004-08-03 00:47:41.000000000 
-0400
***************
*** 43,49 ****
--- 43,51 ----
#include "optimizer/prep.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+ #include "pgstat.h"
#include "postmaster/bgwriter.h"
+ #include "postmaster/autovacuum.h"
#include "postmaster/postmaster.h"
#include "storage/bufmgr.h"
#include "storage/fd.h"
***************
*** 55,61 ****
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/pg_locale.h"
- #include "pgstat.h"
char *guc_pgdata;
char *guc_hbafile;
--- 57,62 ----
***************
*** 642,647 ****
--- 643,656 ----
&pgstat_collect_blocklevel,
false, NULL, NULL
},
+     {
+         {"autovacuum", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Starts the auto vacuum subprocess."),
+             NULL
+         },
+         &autovacuum_start_daemon,
+         false, NULL, NULL     +     },
{
{"trace_notify", PGC_USERSET, DEVELOPER_OPTIONS,
***************
*** 1284,1289 ****
--- 1293,1322 ----
&block_size,
BLCKSZ, BLCKSZ, BLCKSZ, NULL, NULL
},
+     {
+         {"autovacuum_vacuum_threshold_base", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Minimum number of tuple updates or 
deletes prior to vacuum."),
+             NULL
+         },
+         &autovacuum_vacuum_base,
+         1000, 0, INT_MAX, NULL, NULL
+     },
+     {
+         {"autovacuum_analyze_threshold_base", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Minimum number of tuple updates or 
deletes prior to analyze."),
+             NULL
+         },
+         &autovacuum_analyze_base,
+         500, 0, INT_MAX, NULL, NULL
+     },
+     {
+         {"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Minimum number of tuple updates or 
deletes prior to analyze."),
+             NULL
+         },
+         &autovacuum_analyze_base,
+         500, 0, INT_MAX, NULL, NULL
+     },
/* End-of-list marker */
{
***************
*** 1364,1369 ****
--- 1397,1418 ----
&phony_random_seed,
0.5, 0.0, 1.0, assign_random_seed, show_random_seed
},
+     {
+         {"autovacuum_vacuum_threshold_sf", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Numer of tuple updates or deletes prior 
to vacuum as a factor of reltuples."),
+             NULL
+         },
+         &autovacuum_vacuum_scaling_factor,
+         2, 0, 100, NULL, NULL
+     },
+     {
+         {"autovacuum_analyze_threshold_sf", PGC_SIGHUP, AUTOVACUUM,
+             gettext_noop("Numer of tuple updates or deletes prior 
to analyze as a factor of reltuples."),
+             NULL
+         },
+         &autovacuum_analyze_scaling_factor,
+         1, 0, 100, NULL, NULL
+     },
/* End-of-list marker */
{
*** ./src/backend/utils/misc/postgresql.conf.sample.orig    
2004-08-04 01:41:06.000000000 -0400
--- ./src/backend/utils/misc/postgresql.conf.sample    2004-07-22 
23:50:32.000000000 -0400
***************
*** 238,243 ****
--- 238,253 ----
#---------------------------------------------------------------------------
+ # VACUUM DAEMON
+ 
#--------------------------------------------------------------------------- 
+ + #autovacuum = false  # requires stats_row_level = true
+ #autovacuum_vacuum_threshold_base = 1000
+ #autovacuum_vacuum_threshold_sf = 2
+ #autovacuum_analyze_threshold_base = 500
+ #autovacuum_analyze_threshold_sf = 1
+ + 
#--------------------------------------------------------------------------- 
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
*** ./src/include/bootstrap/bootstrap.h.orig    2004-08-04 
01:42:35.927740714 -0400
--- ./src/include/bootstrap/bootstrap.h    2004-07-22 
23:50:32.000000000 -0400
***************
*** 59,63 ****
--- 59,64 ----
#define BS_XLOG_BOOTSTRAP    1
#define BS_XLOG_STARTUP        2
#define BS_XLOG_BGWRITER    3
+ #define BS_XLOG_AUTOVAC        4
#endif   /* BOOTSTRAP_H */
*** ./src/include/postmaster/autovacuum.h.orig    2004-06-29 
09:29:39.000000000 -0400
--- ./src/include/postmaster/autovacuum.h    2004-08-04 
00:46:59.293859961 -0400
***************
*** 1,42 ****
! /* pg_autovacuum.h
!  * Header file for pg_autovacuum.c
* (c) 2003 Matthew T. O'Connor
*/
#include "postgres_fe.h"

#include <unistd.h>
- #ifdef HAVE_GETOPT_H
- #include <getopt.h>
- #endif
#include <time.h>
#include <sys/time.h>

! /* These next two lines are correct when pg_autovaccum is compiled
! from within the postgresql source tree */
! #include "libpq-fe.h"
#include "lib/dllist.h"
! /* Had to change the last two lines to compile on
! Redhat outside of postgresql source tree */
! /*
! #include "/usr/include/libpq-fe.h"
! #include "/usr/include/pgsql/server/lib/dllist.h"
! */
! ! #define AUTOVACUUM_DEBUG 1
! #define VACBASETHRESHOLD 1000
! #define VACSCALINGFACTOR 2
! #define SLEEPBASEVALUE 300
#define SLEEPSCALINGFACTOR 2
- #define UPDATE_INTERVAL 2

/* these two constants are used to tell update_table_stats what
operation we just perfomred */
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1

! #define TABLE_STATS_QUERY "select
a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
from pg_class a, pg_stat_all_tables b where a.oid=b.relid and
a.relkind = 'r' and schemaname not like 'pg_temp_%'"
- #define FRONTEND
#define PAGES_QUERY "select oid,reltuples,relpages from pg_class 
where oid=%u"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from 
pg_database where datname = 'template1'"
#define FROZENOID_QUERY2 "select oid,datname,age(datfrozenxid) from 
pg_database where datname!='template0'"
--- 1,35 ----
! /* autovacuum.h
!  * Header file for autovacuum.c
* (c) 2003 Matthew T. O'Connor
*/
+ #ifndef _AUTOVAC_H
+ #define _AUTOVAC_H
+ + #define FRONTEND
#include "postgres_fe.h"
#include <unistd.h>
#include <time.h>
#include <sys/time.h>
+ #include <sys/stat.h>

! #include "../../interfaces/libpq/libpq-fe.h"
#include "lib/dllist.h"
! ! /* default settings defined here
! * These could be changed into GUC variables, but I'm not sure we
need to. */
! #define SLEEPBASEVALUE 15
#define SLEEPSCALINGFACTOR 2

/* these two constants are used to tell update_table_stats what
operation we just perfomred */
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1
+ #define AUTOVACPASSFILE "autovac.passwd"

! /* define the main queries */
! #define TABLE_STATS_QUERY "select a.oid, a.relname, a.relnamespace,
a.relpages, a.relisshared, a.reltuples, b.schemaname, b.n_tup_ins,
b.n_tup_upd, b.n_tup_del, c.cnt_at_last_analyze,
c.cnt_at_last_vacuum, b.n_tup_ins + b.n_tup_upd as
curr_analyze_count, b.n_tup_upd + b.n_tup_del as curr_vacuum_count,
case when vacuum_threshold < 0 then c.vacuum_base_threshold +
c.vacuum_scaling_factor * a.reltuples else c.vacuum_threshold end as
vacuum_threshold, case when analyze_threshold < 0 then
c.analyze_base_threshold + c.analyze_scaling_factor * a.reltuples
else c.analyze_threshold end as analyze_threshold from pg_class a
inner join pg_stat_all_tables b on a.oid=b.relid left outer join
pg_autovacuum c on a.oid = c.table_oid where a.relkind = 'r' and
schemaname not like 'pg_temp_%' and c.table_oid is not null"

#define PAGES_QUERY "select oid,reltuples,relpages from pg_class
where oid=%u"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from
pg_database where datname = 'template1'"
#define FROZENOID_QUERY2 "select oid,datname,age(datfrozenxid) from
pg_database where datname!='template0'"
***************
*** 44,70 ****
/* define atooid */
#define atooid(x) ((Oid) strtoul((x), NULL, 10))

- /* define cmd_args stucture */
- struct cmdargs
- {
- int vacuum_base_threshold,
- analyze_base_threshold,
- sleep_base_value,
- debug,
- daemonize;
- float vacuum_scaling_factor,
- analyze_scaling_factor,
- sleep_scaling_factor;
- char *user,
- *password,
- *host,
- *logfile,
- *port;
- };
- typedef struct cmdargs cmd_args;

! /* define cmd_args as global so we can get to them everywhere */
! cmd_args *args;
/* Might need to add a time value for last time the whold database 
was vacuumed.
I think we need to guarantee this happens approx every 1Million 
TX's  */
--- 37,53 ----
/* define atooid */
#define atooid(x)  ((Oid) strtoul((x), NULL, 10))
! /* ----------
! * GUC parameters
! * ----------
! */
! extern bool autovacuum_start_daemon; ! extern int
autovacuum_vacuum_base;
! extern double autovacuum_vacuum_scaling_factor;
! extern int autovacuum_analyze_base;
! extern double autovacuum_analyze_scaling_factor;
!
/* Might need to add a time value for last time the whold database
was vacuumed.
I think we need to guarantee this happens approx every 1Million
TX's */
***************
*** 72,142 ****
{
Oid oid;
long age;
- long analyze_threshold,
- vacuum_threshold; /* Use these as defaults
for table
- * thresholds */
PGconn *conn;
char *dbname,
*username,
*password;
- Dllist *table_list;
};
typedef struct dbinfo db_info;

- struct tableinfo
- {
- char *schema_name,
- *table_name;
- float reltuples;
- int relisshared;
- Oid relid,
- relpages;
- long analyze_threshold,
- vacuum_threshold;
- long CountAtLastAnalyze; /* equal to: inserts +
updates as
- * of the last analyze or
initial
- * values at startup */
- long CountAtLastVacuum; /* equal to: deletes +
updates as
- * of the last vacuum or
initial
- * values at startup */
- long curr_analyze_count,
- curr_vacuum_count; /* Latest values from
stats system */
- db_info *dbi; /* pointer to the database that
this table
- * belongs to */
- };
- typedef struct tableinfo tbl_info;
- /* Functions for dealing with command line arguements */
! static cmd_args *get_cmd_args(int argc, char *argv[]);
! static void print_cmd_args(void);
! static void free_cmd_args(void);
! static void usage(void);

/* Functions for managing database lists */
static Dllist *init_db_list(void);
static db_info *init_dbinfo(char *dbname, Oid oid, long age);
! static void update_db_list(Dllist *db_list);
! static void remove_db_from_list(Dlelem *db_to_remove);
! static void print_db_info(db_info * dbi, int print_table_list);
! static void print_db_list(Dllist *db_list, int print_table_lists);
! static int xid_wraparound_check(db_info * dbi);
! static void free_db_list(Dllist *db_list);

/* Functions for managing table lists */
! static tbl_info *init_table_info(PGresult *conn, int row, db_info *
dbi);
! static void update_table_list(db_info * dbi);
! static void remove_table_from_list(Dlelem *tbl_to_remove);
! static void print_table_list(Dllist *tbl_node);
! static void print_table_info(tbl_info * tbl);
! static void update_table_thresholds(db_info * dbi, tbl_info * tbl,
int vacuum_type);
! static void free_tbl_list(Dllist *tbl_list);
/* A few database helper functions */
! static int    check_stats_enabled(db_info * dbi);
! static PGconn *db_connect(db_info * dbi);
! static void db_disconnect(db_info * dbi);
! static PGresult *send_query(const char *query, db_info * dbi);
! ! /* Other Generally needed Functions */
! static void daemonize(void);
! static void log_entry(const char *logentry);
--- 55,88 ----
{
Oid            oid;
long        age;
PGconn       *conn;
char       *dbname,
*username,
*password;
};
typedef struct dbinfo db_info;
/* Functions for dealing with command line arguements */
! void AutoVacMain(void);
! void AutoVacLoop(void);

/* Functions for managing database lists */
static Dllist *init_db_list(void);
static db_info *init_dbinfo(char *dbname, Oid oid, long age);
! void update_db_list(Dllist *db_list);
! void remove_db_from_list(Dlelem *db_to_remove);
! void print_db_info(db_info * dbi, int print_table_list);
! void print_db_list(Dllist *db_list, int print_table_lists);
! int xid_wraparound_check(db_info * dbi);
! void free_db_list(Dllist *db_list);

/* Functions for managing table lists */
! void update_pg_autovacuum_table(db_info * dbi);
! void update_table_thresholds(db_info * dbi, Oid table_oid, int
vacuum_type);
/* A few database helper functions */
! PGconn *db_connect(db_info * dbi);
! void db_disconnect(db_info * dbi);
! PGresult *send_query(const char *query, db_info * dbi);
! ! #endif /* _AUTOVAC_H_ */
*** ./src/include/postmaster/postmaster.h.orig    2004-08-04 
01:42:10.486531715 -0400
--- ./src/include/postmaster/postmaster.h    2004-07-22 
23:50:32.000000000 -0400
***************
*** 29,34 ****
--- 29,35 ----
extern bool Log_connections;
extern bool log_hostname;
extern char *rendezvous_name;
+ extern bool autovacuum_start_daemon;  
#ifdef WIN32
extern HANDLE PostmasterHandle;
*** ./src/include/storage/proc.h.orig    2004-08-04 
01:48:19.967030481 -0400
--- ./src/include/storage/proc.h    2004-08-03 00:47:45.000000000 -0400
***************
*** 109,115 ****
#define DUMMY_PROC_DEFAULT 0
#define DUMMY_PROC_BGWRITER 1
! #define NUM_DUMMY_PROCS 2
/* configurable options */
--- 109,116 ----
#define DUMMY_PROC_DEFAULT 0
#define DUMMY_PROC_BGWRITER 1
! #define DUMMY_PROC_AUTOVAC 2
! #define NUM_DUMMY_PROCS 3
/* configurable options */
*** ./src/include/utils/guc_tables.h.orig    2004-08-04 
01:42:24.235023454 -0400
--- ./src/include/utils/guc_tables.h    2004-07-22 23:50:32.000000000 
-0400
***************
*** 71,76 ****
--- 71,77 ----
COMPAT_OPTIONS_CLIENT,
DEVELOPER_OPTIONS,
COMPILE_OPTIONS,
+     AUTOVACUUM,
CUSTOM_OPTIONS
};
------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Matthew T. O'Connor (#1)

Re: Autovacuum Integration Patch Take 5

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Well I didn't get out of the office as early as I had hoped, and I have
stayed up longer than I had planned, but I have a patch that addresses
many of the issues raised by Tom. Please take a look at let me know if
I'm heading in the right direction.

You're headed in the right direction, but I'm afraid we're running out
of time. The core committee has chewed this over and agreed that we
can't postpone beta for the amount of time we think it will take to make
this patch committable. So we're going to hold it over for the 8.1
release cycle.

I have to make a personal apology to you for the fact that things worked
out this way. I really should have looked at your patch much earlier
and given you some feedback that might have allowed you to resolve the
issues in time. I did not because (a) I felt that the other patches
I was working on were more important features (a judgment I still stand
by) and (b) I thought your patch was in good enough shape that we could
apply it with little effort. That judgment was badly off, and again I
must apologize for it. I hope you won't get discouraged, and will
continue to work on an integrated autovacuum for 8.1.

FWIW, core has also agreed that we want to shoot for a much shorter
release cycle for 8.1 than we have had in the past couple of releases.
It seems likely that as the new 8.0 features are shaken out, 8.1 will
be mostly a mop-up development cycle, and that we will want to push it
out relatively soon (we're thinking of perhaps 3-4 months in
development, with a total release cycle of 6-7 months).

regards, tom lane

Matthew T. O'Connor

matthew@zeut.net

almost 22 years ago

In reply to: Tom Lane (#4)

Re: Autovacuum Integration Patch Take 5

Tom Lane wrote:

You're headed in the right direction, but I'm afraid we're running out
of time. The core committee has chewed this over and agreed that we
can't postpone beta for the amount of time we think it will take to make
this patch committable. So we're going to hold it over for the 8.1
release cycle.

I have to make a personal apology to you for the fact that things worked
out this way. I really should have looked at your patch much earlier
and given you some feedback that might have allowed you to resolve the
issues in time. I did not because (a) I felt that the other patches
I was working on were more important features (a judgment I still stand
by) and (b) I thought your patch was in good enough shape that we could
apply it with little effort. That judgment was badly off, and again I
must apologize for it. I hope you won't get discouraged, and will
continue to work on an integrated autovacuum for 8.1.

ARRRRGGGHHHHHH!!!!!
This is very frustrating. I saw this coming weeks and weeks ago and
tried to get people's attention so that this wouldn't happen. Aside
from my personal frustration, I will say that autovacuum is a high
priority for lots of users of autovacuum and there are already lots of
users looking forward to it being in 8.0. FWIW, I tried to clean up as
much stuff as I could the other night and submit and updated patch, I
would guess that it wouldn't take you very long to clean up the shutdown
issues.

BTW, I choose to try to integrate it into the backend on the
recomendation of several people on the hackers list despite my warnings
that I would probably need help with the backend code issues. I could
have instead put my time towards an improved version in contrib, now the
end-users will have to go another release cycle without any of the
feature improvements I had hoped for.

FWIW, core has also agreed that we want to shoot for a much shorter
release cycle for 8.1 than we have had in the past couple of releases.
It seems likely that as the new 8.0 features are shaken out, 8.1 will
be mostly a mop-up development cycle, and that we will want to push it
out relatively soon (we're thinking of perhaps 3-4 months in
development, with a total release cycle of 6-7 months).

I think we have all heard this before....

Matthew T. O'Connor

matthew@zeut.net

almost 22 years ago

In reply to: Tom Lane (#4)

Re: Autovacuum Integration Patch Take 5

Tom Lane wrote:

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Well I didn't get out of the office as early as I had hoped, and I have
stayed up longer than I had planned, but I have a patch that addresses
many of the issues raised by Tom. Please take a look at let me know if
I'm heading in the right direction.

You're headed in the right direction, but I'm afraid we're running out
of time. The core committee has chewed this over and agreed that we
can't postpone beta for the amount of time we think it will take to make
this patch committable. So we're going to hold it over for the 8.1
release cycle.

BTW, I know people are eager for 8.0, but given that our release cycle
is so long, and that by everyones estimates we are at least 3 months
away from a release, what is the hurry for beta? A few more days to get
this feature in wouldn't hurt, it was submittted before feature freeze,
and I have been waiting weeks on end to get feedback.

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Matthew T. O'Connor (#6)

Re: Autovacuum Integration Patch Take 5

"Matthew T. O'Connor" <matthew@zeut.net> writes:

BTW, I know people are eager for 8.0, but given that our release cycle
is so long, and that by everyones estimates we are at least 3 months
away from a release, what is the hurry for beta?

If I thought we were just a day or two away from having a committable
patch, I'd lobby for more delay, but I don't really think that (and
now that I know you'll be gone over the next couple days, the odds of
that have clearly dropped to zero). We have already slipped beta six
weeks from the original plan, and we cannot keep slipping it
indefinitely.

Again, I do have to apologize for not having found some time to look at
your patch earlier. Hindsight is always 20-20 :-(

regards, tom lane

Dave Page

dpage@pgadmin.org

almost 22 years ago

In reply to: Tom Lane (#7)

Re: Autovacuum Integration Patch Take 5

-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 06 August 2004 00:42
To: Matthew T. O'Connor
Cc: PostgreSQL Patches
Subject: Re: [PATCHES] Autovacuum Integration Patch Take 5

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Well I didn't get out of the office as early as I had hoped, and I
have stayed up longer than I had planned, but I have a patch that
addresses many of the issues raised by Tom. Please take a

look at let

me know if I'm heading in the right direction.

You're headed in the right direction, but I'm afraid we're
running out of time. The core committee has chewed this over
and agreed that we can't postpone beta for the amount of time
we think it will take to make this patch committable. So
we're going to hold it over for the 8.1 release cycle.

Without wishing to sound insensitive to Matthew's frustration at this,
can I therefore request that my win32 service patch gets applied to the
/contrib version so we can look into integrating autovacuum with the
Win32 installer please?

Regards, Dave.

Import Notes

Resolved by subject fallback

Andreas Pflug

pgadmin@pse-consulting.de

almost 22 years ago

In reply to: Tom Lane (#4)

Re: Autovacuum Integration Patch Take 5

Tom Lane wrote:

"Matthew T. O'Connor" <matthew@zeut.net> writes:

You're headed in the right direction, but I'm afraid we're running out
of time. The core committee has chewed this over and agreed that we
can't postpone beta for the amount of time we think it will take to make
this patch committable. So we're going to hold it over for the 8.1
release cycle.

IMHO releasing 8.0 without autovacuum will lead to vastly increased
traffic on support lists, because many (most?) win32 users will fail to
setup a vacuum for themselves; they are much less used to have some
maintenance tasks on a server than linux users. This might become a
nightmare for the community.

I'd opt for including integrated autovacuum in Beta2, despite the
maximum violation of feature freeze/beta release policies.

Regards,
Andreas

#10

Bruce Momjian

bruce@momjian.us

almost 22 years ago

In reply to: Matthew T. O'Connor (#5)

Re: Autovacuum Integration Patch Take 5

Matthew, your reply was exactly the type of reply I would have made in
your situation. Your arguments are clear and indisputable.

Due to the many large patches the we had to process during this release,
we serialized their review. However, I made promises to developers that
their patches would get the same consideration if they were reviewed
early or late. Obviously this wasn't true of your patch. We found more
issues than we thought and didn't give you time to address them.
Frankly we are lucky autovacuum was the only item that didn't make it
because several features were in similar need of major work. Of course
that is no consolation to you and people looking for autovacuum in 8.0.

Not sure what I can do about it at this point. I am going to write up a
whole documentation section on 3rd party tools and interfaces and
pg_autovacuum would have a big mention there.

There is the issue of Win32 and the need for pg_autovacuum to start
easily.

---------------------------------------------------------------------------

Matthew T. O'Connor wrote:

Tom Lane wrote:

You're headed in the right direction, but I'm afraid we're running out
of time. The core committee has chewed this over and agreed that we
can't postpone beta for the amount of time we think it will take to make
this patch committable. So we're going to hold it over for the 8.1
release cycle.

I have to make a personal apology to you for the fact that things worked
out this way. I really should have looked at your patch much earlier
and given you some feedback that might have allowed you to resolve the
issues in time. I did not because (a) I felt that the other patches
I was working on were more important features (a judgment I still stand
by) and (b) I thought your patch was in good enough shape that we could
apply it with little effort. That judgment was badly off, and again I
must apologize for it. I hope you won't get discouraged, and will
continue to work on an integrated autovacuum for 8.1.

ARRRRGGGHHHHHH!!!!!
This is very frustrating. I saw this coming weeks and weeks ago and
tried to get people's attention so that this wouldn't happen. Aside
from my personal frustration, I will say that autovacuum is a high
priority for lots of users of autovacuum and there are already lots of
users looking forward to it being in 8.0. FWIW, I tried to clean up as
much stuff as I could the other night and submit and updated patch, I
would guess that it wouldn't take you very long to clean up the shutdown
issues.

BTW, I choose to try to integrate it into the backend on the
recomendation of several people on the hackers list despite my warnings
that I would probably need help with the backend code issues. I could
have instead put my time towards an improved version in contrib, now the
end-users will have to go another release cycle without any of the
feature improvements I had hoped for.

FWIW, core has also agreed that we want to shoot for a much shorter
release cycle for 8.1 than we have had in the past couple of releases.
It seems likely that as the new 8.0 features are shaken out, 8.1 will
be mostly a mop-up development cycle, and that we will want to push it
out relatively soon (we're thinking of perhaps 3-4 months in
development, with a total release cycle of 6-7 months).

I think we have all heard this before....

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Autovacuum Integration Patch Take 5

Attachments: