Re: Autovacuum Integration Patch Take 5

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

Matthew T. O'Connor wrote:

> 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
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>