*** ./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 + #include ! #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 - #ifdef HAVE_GETOPT_H - #include - #endif #include #include ! /* 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 #include #include + #include ! #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 };