Re: Autovacuum Integration Patch Take 5

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

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

Thanks,

Matthew O'Connor

Matthew T. O'Connor wrote:

>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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>