Thread: Autovacuum Integration Patch Take 5
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
Attachment
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_tableswhere 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
ona.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
beu=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)
andat 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)
andat 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
isnot enabled")));
>+ if (pgstat_collect_resetonpmstart)
>+ ereport(WARNING,(errmsg("pg_autovacuum: stats_reset_on_server_start should be disabled for
optimalperformance")));
>+ }
>+
>+ /*
> * 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
frompg_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_updas 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
whenanalyze_threshold < 0 then c.analyze_base_threshold + c.analyze_scaling_factor * a.reltuples else
c.analyze_thresholdend as analyze_threshold from pg_class a inner join pg_stat_all_tables b on a.oid=b.relid left outer
joinpg_autovacuum c on a.oid = c.table_oid where a.relkind = 'r' and schemaname not like 'pg_temp_%' and c.table_oid is
notnull"
>
> #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
>
>
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:
> 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
>
"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
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....
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.
"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
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
> -----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.
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