granicus.if.org Git - postgresql/blob - contrib/pg_autovacuum/pg_autovacuum.c

   1 /* pg_autovacuum.c
   2  * All the code for the pg_autovacuum program
   3  * (c) 2003 Matthew T. O'Connor
   4  * Revisions by Christopher B. Browne, Liberty RMS
   5  */
   6
   7 #include "pg_autovacuum.h"
   8 #define TIMEBUFF 256
   9 FILE       *LOGOUTPUT;
  10 char            timebuffer[TIMEBUFF];
  11 char            logbuffer[4096];
  12
  13 void
  14 log_entry(const char *logentry)
  15 {
  16         time_t          curtime;
  17         struct tm  *loctime;
  18
  19         curtime = time(NULL);
  20         loctime = localtime(&curtime);
  21         strftime(timebuffer, TIMEBUFF, "%Y-%m-%d %r", loctime);         /* cbb - %F is not
  22                                                                                                                                  * always available */
  23         fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry);
  24 }
  25
  26 /* Function used to detatch the pg_autovacuum daemon from the tty and go into the background *
  27 *         This code is mostly ripped directly from pm_dameonize in postmaster.c                           *
  28 *         with unneeded code removed.                                                                                                             */
  29 void
  30 daemonize()
  31 {
  32         pid_t           pid;
  33
  34         pid = fork();
  35         if (pid == (pid_t) -1)
  36         {
  37                 log_entry("Error: cannot disassociate from controlling TTY");
  38                 fflush(LOGOUTPUT);
  39                 _exit(1);
  40         }
  41         else if (pid)
  42         {                                                       /* parent */
  43                 /* Parent should just exit, without doing any atexit cleanup */
  44                 _exit(0);
  45         }
  46
  47 /* GH: If there's no setsid(), we hopefully don't need silent mode.
  48  * Until there's a better solution.  */
  49 #ifdef HAVE_SETSID
  50         if (setsid() < 0)
  51         {
  52                 log_entry("Error: cannot disassociate from controlling TTY");
  53                 fflush(LOGOUTPUT);
  54                 _exit(1);
  55         }
  56 #endif
  57
  58 }
  59
  60 /* Create and return tbl_info struct with initialized to values from row or res */
  61 tbl_info *
  62 init_table_info(PGresult *res, int row, db_info * dbi)
  63 {
  64         tbl_info   *new_tbl = (tbl_info *) malloc(sizeof(tbl_info));
  65
  66         if (!new_tbl)
  67         {
  68                 log_entry("init_table_info: Cannot get memory");
  69                 fflush(LOGOUTPUT);
  70                 return NULL;
  71         }
  72
  73         if (res == NULL)
  74                 return NULL;
  75
  76         new_tbl->dbi = dbi;                     /* set pointer to db */
  77
  78         new_tbl->schema_name = (char *)
  79                 malloc(strlen(PQgetvalue(res, row, PQfnumber(res, "schemaname"))) + 1);
  80         if (!new_tbl->schema_name)
  81         {
  82                 log_entry("init_table_info: malloc failed on new_tbl->schema_name");
  83                 fflush(LOGOUTPUT);
  84                 return NULL;
  85         }
  86         strcpy(new_tbl->schema_name,
  87                    PQgetvalue(res, row, PQfnumber(res, "schemaname")));
  88
  89         new_tbl->table_name = (char *)
  90                 malloc(strlen(PQgetvalue(res, row, PQfnumber(res, "relname"))) +
  91                            strlen(new_tbl->schema_name) + 6);
  92         if (!new_tbl->table_name)
  93         {
  94                 log_entry("init_table_info: malloc failed on new_tbl->table_name");
  95                 fflush(LOGOUTPUT);
  96                 return NULL;
  97         }
  98
  99         /*
 100          * Put both the schema and table name in quotes so that we can work
 101          * with mixed case table names
 102          */
 103         strcpy(new_tbl->table_name, "\"");
 104         strcat(new_tbl->table_name, new_tbl->schema_name);
 105         strcat(new_tbl->table_name, "\".\"");
 106         strcat(new_tbl->table_name, PQgetvalue(res, row, PQfnumber(res, "relname")));
 107         strcat(new_tbl->table_name, "\"");
 108
 109         new_tbl->CountAtLastAnalyze =
 110                 (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_ins"))) +
 111                  atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))) +
 112                  atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))));
 113         new_tbl->curr_analyze_count = new_tbl->CountAtLastAnalyze;
 114
 115         new_tbl->CountAtLastVacuum =
 116                 (atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_del"))) +
 117                  atol(PQgetvalue(res, row, PQfnumber(res, "n_tup_upd"))));
 118         new_tbl->curr_vacuum_count = new_tbl->CountAtLastVacuum;
 119
 120         new_tbl->relid = atooid(PQgetvalue(res, row, PQfnumber(res, "oid")));
 121         new_tbl->reltuples = atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
 122         new_tbl->relpages = atooid(PQgetvalue(res, row, PQfnumber(res, "relpages")));
 123
 124         if (strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
 125                 new_tbl->relisshared = 0;
 126         else
 127                 new_tbl->relisshared = 1;
 128
 129         new_tbl->analyze_threshold =
 130                 args->analyze_base_threshold + args->analyze_scaling_factor * new_tbl->reltuples;
 131         new_tbl->vacuum_threshold =
 132                 args->vacuum_base_threshold + args->vacuum_scaling_factor * new_tbl->reltuples;
 133
 134         if (args->debug >= 2)
 135                 print_table_info(new_tbl);
 136
 137         return new_tbl;
 138 }
 139
 140 /* Set thresholds = base_value + scaling_factor * reltuples
 141    Should be called after a vacuum since vacuum updates values in pg_class */
 142 void
 143 update_table_thresholds(db_info * dbi, tbl_info * tbl, int vacuum_type)
 144 {
 145         PGresult   *res = NULL;
 146         int                     disconnect = 0;
 147         char            query[128];
 148
 149         if (dbi->conn == NULL)
 150         {
 151                 dbi->conn = db_connect(dbi);
 152                 disconnect = 1;
 153         }
 154
 155         if (dbi->conn != NULL)
 156         {
 157                 snprintf(query, sizeof(query), PAGES_QUERY, tbl->relid);
 158                 res = send_query(query, dbi);
 159                 if (res != NULL)
 160                 {
 161                         tbl->reltuples =
 162                                 atof(PQgetvalue(res, 0, PQfnumber(res, "reltuples")));
 163                         tbl->relpages = atooid(PQgetvalue(res, 0, PQfnumber(res, "relpages")));
 164
 165                         /*
 166                          * update vacuum thresholds only of we just did a vacuum
 167                          * analyze
 168                          */
 169                         if (vacuum_type == VACUUM_ANALYZE)
 170                         {
 171                                 tbl->vacuum_threshold =
 172                                         (args->vacuum_base_threshold + args->vacuum_scaling_factor * tbl->reltuples);
 173                                 tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
 174                         }
 175
 176                         /* update analyze thresholds */
 177                         tbl->analyze_threshold =
 178                                 (args->analyze_base_threshold + args->analyze_scaling_factor * tbl->reltuples);
 179                         tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
 180
 181                         PQclear(res);
 182
 183                         /*
 184                          * If the stats collector is reporting fewer updates then we
 185                          * have on record then the stats were probably reset, so we
 186                          * need to reset also
 187                          */
 188                         if ((tbl->curr_analyze_count < tbl->CountAtLastAnalyze) ||
 189                                 (tbl->curr_vacuum_count < tbl->CountAtLastVacuum))
 190                         {
 191                                 tbl->CountAtLastAnalyze = tbl->curr_analyze_count;
 192                                 tbl->CountAtLastVacuum = tbl->curr_vacuum_count;
 193                         }
 194                 }
 195         }
 196         if (disconnect)
 197                 db_disconnect(dbi);
 198 }
 199
 200 void
 201 update_table_list(db_info * dbi)
 202 {
 203         int                     disconnect = 0;
 204         PGresult   *res = NULL;
 205         tbl_info   *tbl = NULL;
 206         Dlelem     *tbl_elem = DLGetHead(dbi->table_list);
 207         int                     i = 0,
 208                                 t = 0,
 209                                 found_match = 0;
 210
 211         if (dbi->conn == NULL)
 212         {
 213                 dbi->conn = db_connect(dbi);
 214                 disconnect = 1;
 215         }
 216
 217         if (dbi->conn != NULL)
 218         {
 219                 /*
 220                  * Get a result set that has all the information we will need to
 221                  * both remove tables from the list that no longer exist and add
 222                  * tables to the list that are new
 223                  */
 224                 res = send_query((char *) TABLE_STATS_QUERY, dbi);
 225                 t = PQntuples(res);
 226
 227                 /*
 228                  * First: use the tbl_list as the outer loop and the result set as
 229                  * the inner loop, this will determine what tables should be
 230                  * removed
 231                  */
 232                 while (tbl_elem != NULL)
 233                 {
 234                         tbl = ((tbl_info *) DLE_VAL(tbl_elem));
 235                         found_match = 0;
 236
 237                         for (i = 0; i < t; i++)
 238                         {                                       /* loop through result set looking for a
 239                                                                  * match */
 240                                 if (tbl->relid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
 241                                 {
 242                                         found_match = 1;
 243                                         break;
 244                                 }
 245                         }
 246                         if (found_match == 0)
 247                         {                                       /* then we didn't find this tbl_elem in
 248                                                                  * the result set */
 249                                 Dlelem     *elem_to_remove = tbl_elem;
 250
 251                                 tbl_elem = DLGetSucc(tbl_elem);
 252                                 remove_table_from_list(elem_to_remove);
 253                         }
 254                         else
 255                                 tbl_elem = DLGetSucc(tbl_elem);
 256                 }                                               /* Done removing dropped tables from the
 257                                                                  * table_list */
 258
 259                 /*
 260                  * Then loop use result set as outer loop and tbl_list as the
 261                  * inner loop to determine what tables are new
 262                  */
 263                 for (i = 0; i < t; i++)
 264                 {
 265                         tbl_elem = DLGetHead(dbi->table_list);
 266                         found_match = 0;
 267                         while (tbl_elem != NULL)
 268                         {
 269                                 tbl = ((tbl_info *) DLE_VAL(tbl_elem));
 270                                 if (tbl->relid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
 271                                 {
 272                                         found_match = 1;
 273                                         break;
 274                                 }
 275                                 tbl_elem = DLGetSucc(tbl_elem);
 276                         }
 277                         if (found_match == 0)           /* then we didn't find this result
 278                                                                                  * now in the tbl_list */
 279                         {
 280                                 DLAddTail(dbi->table_list, DLNewElem(init_table_info(res, i, dbi)));
 281                                 if (args->debug >= 1)
 282                                 {
 283                                         sprintf(logbuffer, "added table: %s.%s", dbi->dbname,
 284                                                         ((tbl_info *) DLE_VAL(DLGetTail(dbi->table_list)))->table_name);
 285                                         log_entry(logbuffer);
 286                                 }
 287                         }
 288                 }                                               /* end of for loop that adds tables */
 289                 fflush(LOGOUTPUT);
 290                 PQclear(res);
 291                 res = NULL;
 292                 if (args->debug >= 3)
 293                         print_table_list(dbi->table_list);
 294                 if (disconnect)
 295                         db_disconnect(dbi);
 296         }
 297 }
 298
 299 /* Free memory, and remove the node from the list */
 300 void
 301 remove_table_from_list(Dlelem *tbl_to_remove)
 302 {
 303         tbl_info   *tbl = ((tbl_info *) DLE_VAL(tbl_to_remove));
 304
 305         if (args->debug >= 1)
 306         {
 307                 sprintf(logbuffer, "Removing table: %s from list.", tbl->table_name);
 308                 log_entry(logbuffer);
 309                 fflush(LOGOUTPUT);
 310         }
 311         DLRemove(tbl_to_remove);
 312
 313         if (tbl->schema_name)
 314         {
 315                 free(tbl->schema_name);
 316                 tbl->schema_name = NULL;
 317         }
 318         if (tbl->table_name)
 319         {
 320                 free(tbl->table_name);
 321                 tbl->table_name = NULL;
 322         }
 323         if (tbl)
 324         {
 325                 free(tbl);
 326                 tbl = NULL;
 327         }
 328         DLFreeElem(tbl_to_remove);
 329 }
 330
 331 /* Free the entire table list */
 332 void
 333 free_tbl_list(Dllist *tbl_list)
 334 {
 335         Dlelem     *tbl_elem = DLGetHead(tbl_list);
 336         Dlelem     *tbl_elem_to_remove = NULL;
 337
 338         while (tbl_elem != NULL)
 339         {
 340                 tbl_elem_to_remove = tbl_elem;
 341                 tbl_elem = DLGetSucc(tbl_elem);
 342                 remove_table_from_list(tbl_elem_to_remove);
 343         }
 344         DLFreeList(tbl_list);
 345 }
 346
 347 void
 348 print_table_list(Dllist *table_list)
 349 {
 350         Dlelem     *table_elem = DLGetHead(table_list);
 351
 352         while (table_elem != NULL)
 353         {
 354                 print_table_info(((tbl_info *) DLE_VAL(table_elem)));
 355                 table_elem = DLGetSucc(table_elem);
 356         }
 357 }
 358
 359 void
 360 print_table_info(tbl_info * tbl)
 361 {
 362         sprintf(logbuffer, "  table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
 363         log_entry(logbuffer);
 364         sprintf(logbuffer, "     relid: %u;   relisshared: %i", tbl->relid, tbl->relisshared);
 365         log_entry(logbuffer);
 366         sprintf(logbuffer, "     reltuples: %f;  relpages: %u", tbl->reltuples, tbl->relpages);
 367         log_entry(logbuffer);
 368         sprintf(logbuffer, "     curr_analyze_count: %li; curr_vacuum_count: %li",
 369                         tbl->curr_analyze_count, tbl->curr_vacuum_count);
 370         log_entry(logbuffer);
 371         sprintf(logbuffer, "     last_analyze_count: %li; last_vacuum_count: %li",
 372                         tbl->CountAtLastAnalyze, tbl->CountAtLastVacuum);
 373         log_entry(logbuffer);
 374         sprintf(logbuffer, "     analyze_threshold: %li; vacuum_threshold: %li",
 375                         tbl->analyze_threshold, tbl->vacuum_threshold);
 376         log_entry(logbuffer);
 377         fflush(LOGOUTPUT);
 378 }
 379
 380 /* End of table Management Functions */
 381
 382 /* Beginning of DB Management Functions */
 383
 384 /* init_db_list() creates the db_list and initalizes template1 */
 385 Dllist *
 386 init_db_list()
 387 {
 388         Dllist     *db_list = DLNewList();
 389         db_info    *dbs = NULL;
 390         PGresult   *res = NULL;
 391
 392         DLAddHead(db_list, DLNewElem(init_dbinfo((char *) "template1", 0, 0)));
 393         if (DLGetHead(db_list) == NULL)
 394         {                                                       /* Make sure init_dbinfo was successful */
 395                 log_entry("init_db_list(): Error creating db_list for db: template1.");
 396                 fflush(LOGOUTPUT);
 397                 return NULL;
 398         }
 399
 400         /*
 401          * We do this just so we can set the proper oid for the template1
 402          * database
 403          */
 404         dbs = ((db_info *) DLE_VAL(DLGetHead(db_list)));
 405         dbs->conn = db_connect(dbs);
 406
 407         if (dbs->conn != NULL)
 408         {
 409                 res = send_query(FROZENOID_QUERY, dbs);
 410                 dbs->oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
 411                 dbs->age = atol(PQgetvalue(res, 0, PQfnumber(res, "age")));
 412                 if (res)
 413                         PQclear(res);
 414
 415                 if (args->debug >= 2)
 416                         print_db_list(db_list, 0);
 417         }
 418         return db_list;
 419 }
 420
 421 /* Simple function to create an instance of the dbinfo struct
 422         Initalizes all the pointers and connects to the database  */
 423 db_info *
 424 init_dbinfo(char *dbname, Oid oid, long age)
 425 {
 426         db_info    *newdbinfo = (db_info *) malloc(sizeof(db_info));
 427
 428         newdbinfo->analyze_threshold = args->vacuum_base_threshold;
 429         newdbinfo->vacuum_threshold = args->analyze_base_threshold;
 430         newdbinfo->dbname = (char *) malloc(strlen(dbname) + 1);
 431         strcpy(newdbinfo->dbname, dbname);
 432         newdbinfo->username = NULL;
 433         if (args->user != NULL)
 434         {
 435                 newdbinfo->username = (char *) malloc(strlen(args->user) + 1);
 436                 strcpy(newdbinfo->username, args->user);
 437         }
 438         newdbinfo->password = NULL;
 439         if (args->password != NULL)
 440         {
 441                 newdbinfo->password = (char *) malloc(strlen(args->password) + 1);
 442                 strcpy(newdbinfo->password, args->password);
 443         }
 444         newdbinfo->oid = oid;
 445         newdbinfo->age = age;
 446         newdbinfo->table_list = DLNewList();
 447         newdbinfo->conn = NULL;
 448
 449         if (args->debug >= 2)
 450                 print_table_list(newdbinfo->table_list);
 451
 452         return newdbinfo;
 453 }
 454
 455 /* Function adds and removes databases from the db_list as appropriate */
 456 void
 457 update_db_list(Dllist *db_list)
 458 {
 459         int                     disconnect = 0;
 460         PGresult   *res = NULL;
 461         Dlelem     *db_elem = DLGetHead(db_list);
 462         db_info    *dbi = NULL;
 463         db_info    *dbi_template1 = DLE_VAL(db_elem);
 464         int                     i = 0,
 465                                 t = 0,
 466                                 found_match = 0;
 467
 468         if (args->debug >= 2)
 469         {
 470                 log_entry("updating the database list");
 471                 fflush(LOGOUTPUT);
 472         }
 473
 474         if (dbi_template1->conn == NULL)
 475         {
 476                 dbi_template1->conn = db_connect(dbi_template1);
 477                 disconnect = 1;
 478         }
 479
 480         if (dbi_template1->conn != NULL)
 481         {
 482                 /*
 483                  * Get a result set that has all the information we will need to
 484                  * both remove databasews from the list that no longer exist and
 485                  * add databases to the list that are new
 486                  */
 487                 res = send_query(FROZENOID_QUERY2, dbi_template1);
 488                 t = PQntuples(res);
 489
 490                 /*
 491                  * First: use the db_list as the outer loop and the result set as
 492                  * the inner loop, this will determine what databases should be
 493                  * removed
 494                  */
 495                 while (db_elem != NULL)
 496                 {
 497                         dbi = ((db_info *) DLE_VAL(db_elem));
 498                         found_match = 0;
 499
 500                         for (i = 0; i < t; i++)
 501                         {                                       /* loop through result set looking for a
 502                                                                  * match */
 503                                 if (dbi->oid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
 504                                 {
 505                                         found_match = 1;
 506
 507                                         /*
 508                                          * update the dbi->age so that we ensure
 509                                          * xid_wraparound won't happen
 510                                          */
 511                                         dbi->age = atol(PQgetvalue(res, i, PQfnumber(res, "age")));
 512                                         break;
 513                                 }
 514                         }
 515                         if (found_match == 0)
 516                         {                                       /* then we didn't find this db_elem in the
 517                                                                  * result set */
 518                                 Dlelem     *elem_to_remove = db_elem;
 519
 520                                 db_elem = DLGetSucc(db_elem);
 521                                 remove_db_from_list(elem_to_remove);
 522                         }
 523                         else
 524                                 db_elem = DLGetSucc(db_elem);
 525                 }                                               /* Done removing dropped databases from
 526                                                                  * the table_list */
 527
 528                 /*
 529                  * Then loop use result set as outer loop and db_list as the inner
 530                  * loop to determine what databases are new
 531                  */
 532                 for (i = 0; i < t; i++)
 533                 {
 534                         db_elem = DLGetHead(db_list);
 535                         found_match = 0;
 536                         while (db_elem != NULL)
 537                         {
 538                                 dbi = ((db_info *) DLE_VAL(db_elem));
 539                                 if (dbi->oid == atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))))
 540                                 {
 541                                         found_match = 1;
 542                                         break;
 543                                 }
 544                                 db_elem = DLGetSucc(db_elem);
 545                         }
 546                         if (found_match == 0)           /* then we didn't find this result
 547                                                                                  * now in the tbl_list */
 548                         {
 549                                 DLAddTail(db_list, DLNewElem(init_dbinfo
 550                                                   (PQgetvalue(res, i, PQfnumber(res, "datname")),
 551                                                  atooid(PQgetvalue(res, i, PQfnumber(res, "oid"))),
 552                                           atol(PQgetvalue(res, i, PQfnumber(res, "age"))))));
 553                                 if (args->debug >= 1)
 554                                 {
 555                                         sprintf(logbuffer, "added database: %s", ((db_info *) DLE_VAL(DLGetTail(db_list)))->dbname);
 556                                         log_entry(logbuffer);
 557                                 }
 558                         }
 559                 }                                               /* end of for loop that adds tables */
 560                 fflush(LOGOUTPUT);
 561                 PQclear(res);
 562                 res = NULL;
 563                 if (args->debug >= 3)
 564                         print_db_list(db_list, 0);
 565                 if (disconnect)
 566                         db_disconnect(dbi_template1);
 567         }
 568 }
 569
 570 /* xid_wraparound_check
 571
 572 From the docs:
 573
 574 With the standard freezing policy, the age column will start at one billion for a
 575 freshly-vacuumed database. When the age approaches two billion, the database must
 576 be vacuumed again to avoid risk of wraparound failures. Recommended practice is
 577 to vacuum each database at least once every half-a-billion (500 million) transactions,
 578 so as to provide plenty of safety margin.
 579
 580 So we do a full database vacuum if age > 1.5billion
 581 return 0 if nothing happened,
 582 return 1 if the database needed a database wide vacuum
 583 */
 584 int
 585 xid_wraparound_check(db_info * dbi)
 586 {
 587         /*
 588          * FIXME: should probably do something better here so that we don't
 589          * vacuum all the databases on the server at the same time.  We have
 590          * 500million xacts to work with so we should be able to spread the
 591          * load of full database vacuums a bit
 592          */
 593         if (dbi->age > 1500000000 )
 594         {
 595                 PGresult   *res = NULL;
 596
 597                 res = send_query("VACUUM", dbi);
 598                 /* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
 599                 PQclear(res);
 600                 return 1;
 601         }
 602         return 0;
 603 }
 604
 605 /* Close DB connection, free memory, and remove the node from the list */
 606 void
 607 remove_db_from_list(Dlelem *db_to_remove)
 608 {
 609         db_info    *dbi = ((db_info *) DLE_VAL(db_to_remove));
 610
 611         if (args->debug >= 1)
 612         {
 613                 sprintf(logbuffer, "Removing db: %s from list.", dbi->dbname);
 614                 log_entry(logbuffer);
 615                 fflush(LOGOUTPUT);
 616         }
 617         DLRemove(db_to_remove);
 618         if (dbi->conn)
 619                 db_disconnect(dbi);
 620         if (dbi->dbname)
 621         {
 622                 free(dbi->dbname);
 623                 dbi->dbname = NULL;
 624         }
 625         if (dbi->username)
 626         {
 627                 free(dbi->username);
 628                 dbi->username = NULL;
 629         }
 630         if (dbi->password)
 631         {
 632                 free(dbi->password);
 633                 dbi->password = NULL;
 634         }
 635         if (dbi->table_list)
 636         {
 637                 free_tbl_list(dbi->table_list);
 638                 dbi->table_list = NULL;
 639         }
 640         if (dbi)
 641         {
 642                 free(dbi);
 643                 dbi = NULL;
 644         }
 645         DLFreeElem(db_to_remove);
 646 }
 647
 648 /* Function is called before program exit to free all memory
 649                 mostly it's just to keep valgrind happy */
 650 void
 651 free_db_list(Dllist *db_list)
 652 {
 653         Dlelem     *db_elem = DLGetHead(db_list);
 654         Dlelem     *db_elem_to_remove = NULL;
 655
 656         while (db_elem != NULL)
 657         {
 658                 db_elem_to_remove = db_elem;
 659                 db_elem = DLGetSucc(db_elem);
 660                 remove_db_from_list(db_elem_to_remove);
 661                 db_elem_to_remove = NULL;
 662         }
 663         DLFreeList(db_list);
 664 }
 665
 666 void
 667 print_db_list(Dllist *db_list, int print_table_lists)
 668 {
 669         Dlelem     *db_elem = DLGetHead(db_list);
 670
 671         while (db_elem != NULL)
 672         {
 673                 print_db_info(((db_info *) DLE_VAL(db_elem)), print_table_lists);
 674                 db_elem = DLGetSucc(db_elem);
 675         }
 676 }
 677
 678 void
 679 print_db_info(db_info * dbi, int print_tbl_list)
 680 {
 681         sprintf(logbuffer, "dbname: %s", (dbi->dbname) ? dbi->dbname : "(null)");
 682         log_entry(logbuffer);
 683
 684         sprintf(logbuffer, "  oid: %u", dbi->oid);
 685         log_entry(logbuffer);
 686
 687         sprintf(logbuffer, "  username: %s", (dbi->username) ? dbi->username : "(null)");
 688         log_entry(logbuffer);
 689
 690         sprintf(logbuffer, "  password: %s", (dbi->password) ? dbi->password : "(null)");
 691         log_entry(logbuffer);
 692
 693         if (dbi->conn != NULL)
 694                 log_entry("  conn is valid, (connected)");
 695         else
 696                 log_entry("  conn is null, (not connected)");
 697
 698         sprintf(logbuffer, "  default_analyze_threshold: %li", dbi->analyze_threshold);
 699         log_entry(logbuffer);
 700
 701         sprintf(logbuffer, "  default_vacuum_threshold: %li", dbi->vacuum_threshold);
 702         log_entry(logbuffer);
 703
 704         fflush(LOGOUTPUT);
 705         if (print_tbl_list > 0)
 706                 print_table_list(dbi->table_list);
 707 }
 708
 709 /* End of DB List Management Function */
 710
 711 /* Beginning of misc Functions */
 712
 713 /* Perhaps add some test to this function to make sure that the stats we need are available */
 714 PGconn *
 715 db_connect(db_info * dbi)
 716 {
 717         PGconn     *db_conn =
 718         PQsetdbLogin(args->host, args->port, NULL, NULL, dbi->dbname,
 719                                  dbi->username, dbi->password);
 720
 721         if (PQstatus(db_conn) != CONNECTION_OK)
 722         {
 723                 sprintf(logbuffer, "Failed connection to database %s with error: %s.",
 724                                 dbi->dbname, PQerrorMessage(db_conn));
 725                 log_entry(logbuffer);
 726                 fflush(LOGOUTPUT);
 727                 PQfinish(db_conn);
 728                 db_conn = NULL;
 729         }
 730         return db_conn;
 731 }       /* end of db_connect() */
 732
 733 void
 734 db_disconnect(db_info * dbi)
 735 {
 736         if (dbi->conn != NULL)
 737         {
 738                 PQfinish(dbi->conn);
 739                 dbi->conn = NULL;
 740         }
 741 }
 742
 743 int
 744 check_stats_enabled(db_info * dbi)
 745 {
 746         PGresult   *res;
 747         int                     ret = 0;
 748
 749         res = send_query("SHOW stats_row_level", dbi);
 750         if (res)
 751         {
 752                 ret = strcmp("on", PQgetvalue(res, 0, PQfnumber(res, "stats_row_level")));
 753                 PQclear(res);
 754         }
 755         return ret;
 756 }
 757
 758 PGresult *
 759 send_query(const char *query, db_info * dbi)
 760 {
 761         PGresult   *res;
 762
 763         if (dbi->conn == NULL)
 764                 return NULL;
 765
 766         if (args->debug >= 4)
 767                 log_entry(query);
 768
 769         res = PQexec(dbi->conn, query);
 770
 771         if (!res)
 772         {
 773                 sprintf(logbuffer,
 774                    "Fatal error occured while sending query (%s) to database %s",
 775                                 query, dbi->dbname);
 776                 log_entry(logbuffer);
 777                 sprintf(logbuffer, "The error is [%s]", PQresultErrorMessage(res));
 778                 log_entry(logbuffer);
 779                 fflush(LOGOUTPUT);
 780                 return NULL;
 781         }
 782         if (PQresultStatus(res) != PGRES_TUPLES_OK &&
 783                 PQresultStatus(res) != PGRES_COMMAND_OK)
 784         {
 785                 sprintf(logbuffer,
 786                   "Can not refresh statistics information from the database %s.",
 787                                 dbi->dbname);
 788                 log_entry(logbuffer);
 789                 sprintf(logbuffer, "The error is [%s]", PQresultErrorMessage(res));
 790                 log_entry(logbuffer);
 791                 fflush(LOGOUTPUT);
 792                 PQclear(res);
 793                 return NULL;
 794         }
 795         return res;
 796 }       /* End of send_query() */
 797
 798
 799 void
 800 free_cmd_args()
 801 {
 802         if (args != NULL)
 803         {
 804                 if (args->user != NULL)
 805                         free(args->user);
 806                 if (args->password != NULL)
 807                         free(args->password);
 808                 free(args);
 809         }
 810 }
 811
 812 cmd_args *
 813 get_cmd_args(int argc, char *argv[])
 814 {
 815         int                     c;
 816
 817         args = (cmd_args *) malloc(sizeof(cmd_args));
 818         args->sleep_base_value = SLEEPBASEVALUE;
 819         args->sleep_scaling_factor = SLEEPSCALINGFACTOR;
 820         args->vacuum_base_threshold = VACBASETHRESHOLD;
 821         args->vacuum_scaling_factor = VACSCALINGFACTOR;
 822         args->analyze_base_threshold = -1;
 823         args->analyze_scaling_factor = -1;
 824         args->debug = AUTOVACUUM_DEBUG;
 825         args->daemonize = 0;
 826         args->user = 0;
 827         args->password = 0;
 828         args->host = 0;
 829         args->logfile = 0;
 830         args->port = 0;
 831
 832         /*
 833          * Fixme: Should add some sanity checking such as positive integer
 834          * values etc
 835          */
 836         while ((c = getopt(argc, argv, "s:S:v:V:a:A:d:U:P:H:L:p:hD")) != -1)
 837         {
 838                 switch (c)
 839                 {
 840                         case 's':
 841                                 args->sleep_base_value = atoi(optarg);
 842                                 break;
 843                         case 'S':
 844                                 args->sleep_scaling_factor = atof(optarg);
 845                                 break;
 846                         case 'v':
 847                                 args->vacuum_base_threshold = atoi(optarg);
 848                                 break;
 849                         case 'V':
 850                                 args->vacuum_scaling_factor = atof(optarg);
 851                                 break;
 852                         case 'a':
 853                                 args->analyze_base_threshold = atoi(optarg);
 854                                 break;
 855                         case 'A':
 856                                 args->analyze_scaling_factor = atof(optarg);
 857                                 break;
 858                         case 'D':
 859                                 args->daemonize++;
 860                                 break;
 861                         case 'd':
 862                                 args->debug = atoi(optarg);
 863                                 break;
 864                         case 'U':
 865                                 args->user = optarg;
 866                                 break;
 867                         case 'P':
 868                                 args->password = optarg;
 869                                 break;
 870                         case 'H':
 871                                 args->host = optarg;
 872                                 break;
 873                         case 'L':
 874                                 args->logfile = optarg;
 875                                 break;
 876                         case 'p':
 877                                 args->port = optarg;
 878                                 break;
 879                         case 'h':
 880                                 usage();
 881                                 exit(0);
 882                         default:
 883
 884                                 /*
 885                                  * It's here that we know that things are invalid... It is
 886                                  * not forcibly an error to call usage
 887                                  */
 888                                 fprintf(stderr, "Error: Invalid Command Line Options.\n");
 889                                 usage();
 890                                 exit(1);
 891                                 break;
 892                 }
 893
 894                 /*
 895                  * if values for insert thresholds are not specified, then they
 896                  * default to 1/2 of the delete values
 897                  */
 898                 if (args->analyze_base_threshold == -1)
 899                         args->analyze_base_threshold = args->vacuum_base_threshold / 2;
 900                 if (args->analyze_scaling_factor == -1)
 901                         args->analyze_scaling_factor = args->vacuum_scaling_factor / 2;
 902         }
 903         return args;
 904 }
 905
 906 void
 907 usage()
 908 {
 909         int                     i = 0;
 910         float           f = 0;
 911
 912         fprintf(stderr, "usage: pg_autovacuum \n");
 913         fprintf(stderr, "   [-D] Daemonize (Detach from tty and run in the background)\n");
 914         i = AUTOVACUUM_DEBUG;
 915         fprintf(stderr, "   [-d] debug (debug level=0,1,2,3; default=%i)\n", i);
 916
 917         i = SLEEPBASEVALUE;
 918         fprintf(stderr, "   [-s] sleep base value (default=%i)\n", i);
 919         f = SLEEPSCALINGFACTOR;
 920         fprintf(stderr, "   [-S] sleep scaling factor (default=%f)\n", f);
 921
 922         i = VACBASETHRESHOLD;
 923         fprintf(stderr, "   [-v] vacuum base threshold (default=%i)\n", i);
 924         f = VACSCALINGFACTOR;
 925         fprintf(stderr, "   [-V] vacuum scaling factor (default=%f)\n", f);
 926         i = i / 2;
 927         fprintf(stderr, "   [-a] analyze base threshold (default=%i)\n", i);
 928         f = f / 2;
 929         fprintf(stderr, "   [-A] analyze scaling factor (default=%f)\n", f);
 930
 931         fprintf(stderr, "   [-L] logfile (default=none)\n");
 932
 933         fprintf(stderr, "   [-U] username (libpq default)\n");
 934         fprintf(stderr, "   [-P] password (libpq default)\n");
 935         fprintf(stderr, "   [-H] host (libpq default)\n");
 936         fprintf(stderr, "   [-p] port (libpq default)\n");
 937
 938         fprintf(stderr, "   [-h] help (Show this output)\n");
 939 }
 940
 941 void
 942 print_cmd_args()
 943 {
 944         sprintf(logbuffer, "Printing command_args");
 945         log_entry(logbuffer);
 946         sprintf(logbuffer, "  args->host=%s", (args->host) ? args->host : "(null)");
 947         log_entry(logbuffer);
 948         sprintf(logbuffer, "  args->port=%s", (args->port) ? args->port : "(null)");
 949         log_entry(logbuffer);
 950         sprintf(logbuffer, "  args->username=%s", (args->user) ? args->user : "(null)");
 951         log_entry(logbuffer);
 952         sprintf(logbuffer, "  args->password=%s", (args->password) ? args->password : "(null)");
 953         log_entry(logbuffer);
 954         sprintf(logbuffer, "  args->logfile=%s", (args->logfile) ? args->logfile : "(null)");
 955         log_entry(logbuffer);
 956         sprintf(logbuffer, "  args->daemonize=%i", args->daemonize);
 957         log_entry(logbuffer);
 958
 959         sprintf(logbuffer, "  args->sleep_base_value=%i", args->sleep_base_value);
 960         log_entry(logbuffer);
 961         sprintf(logbuffer, "  args->sleep_scaling_factor=%f", args->sleep_scaling_factor);
 962         log_entry(logbuffer);
 963         sprintf(logbuffer, "  args->vacuum_base_threshold=%i", args->vacuum_base_threshold);
 964         log_entry(logbuffer);
 965         sprintf(logbuffer, "  args->vacuum_scaling_factor=%f", args->vacuum_scaling_factor);
 966         log_entry(logbuffer);
 967         sprintf(logbuffer, "  args->analyze_base_threshold=%i", args->analyze_base_threshold);
 968         log_entry(logbuffer);
 969         sprintf(logbuffer, "  args->analyze_scaling_factor=%f", args->analyze_scaling_factor);
 970         log_entry(logbuffer);
 971         sprintf(logbuffer, "  args->debug=%i", args->debug);
 972         log_entry(logbuffer);
 973
 974         fflush(LOGOUTPUT);
 975 }
 976
 977 /* Beginning of AutoVacuum Main Program */
 978 int
 979 main(int argc, char *argv[])
 980 {
 981         char            buf[256];
 982         int                     j = 0,
 983                                 loops = 0;
 984
 985         /* int numInserts, numDeletes, */
 986         int                     sleep_secs;
 987         Dllist     *db_list;
 988         Dlelem     *db_elem,
 989                            *tbl_elem;
 990         db_info    *dbs;
 991         tbl_info   *tbl;
 992         PGresult   *res = NULL;
 993         double          diff;
 994         struct timeval now,
 995                                 then;
 996
 997         args = get_cmd_args(argc, argv);        /* Get Command Line Args and put
 998                                                                                  * them in the args struct */
 999
1000         /* Dameonize if requested */
1001         if (args->daemonize == 1)
1002                 daemonize();
1003
1004         if (args->logfile)
1005         {
1006                 LOGOUTPUT = fopen(args->logfile, "a");
1007                 if (!LOGOUTPUT)
1008                 {
1009                         fprintf(stderr, "Could not open log file - [%s]\n", args->logfile);
1010                         exit(-1);
1011                 }
1012         }
1013         else
1014                 LOGOUTPUT = stderr;
1015         if (args->debug >= 2)
1016                 print_cmd_args();
1017
1018         /* Init the db list with template1 */
1019         db_list = init_db_list();
1020         if (db_list == NULL)
1021                 return 1;
1022
1023         if (check_stats_enabled(((db_info *) DLE_VAL(DLGetHead(db_list)))) != 0)
1024         {
1025                 log_entry("Error: GUC variable stats_row_level must be enabled.");
1026                 log_entry("       Please fix the problems and try again.");
1027                 fflush(LOGOUTPUT);
1028
1029                 exit(1);
1030         }
1031
1032         gettimeofday(&then, 0);         /* for use later to caluculate sleep time */
1033
1034         while (1)
1035         {                                                       /* Main Loop */
1036                 db_elem = DLGetHead(db_list);   /* Reset cur_db_node to the
1037                                                                                  * beginning of the db_list */
1038
1039                 dbs = ((db_info *) DLE_VAL(db_elem));   /* get pointer to cur_db's
1040                                                                                                  * db_info struct */
1041                 if (dbs->conn == NULL)
1042                 {
1043                         dbs->conn = db_connect(dbs);
1044                         if (dbs->conn == NULL)
1045                         {                                       /* Serious problem: We can't connect to
1046                                                                  * template1 */
1047                                 log_entry("Error: Cannot connect to template1, exiting.");
1048                                 fflush(LOGOUTPUT);
1049                                 fclose(LOGOUTPUT);
1050                                 exit(1);
1051                         }
1052                 }
1053
1054                 if (loops % UPDATE_INTERVAL == 0)               /* Update the list if it's
1055                                                                                                  * time */
1056                         update_db_list(db_list);        /* Add and remove databases from
1057                                                                                  * the list */
1058
1059                 while (db_elem != NULL)
1060                 {                                               /* Loop through databases in list */
1061                         dbs = ((db_info *) DLE_VAL(db_elem));           /* get pointer to
1062                                                                                                                  * cur_db's db_info
1063                                                                                                                  * struct */
1064                         if (dbs->conn == NULL)
1065                                 dbs->conn = db_connect(dbs);
1066
1067                         if (dbs->conn != NULL)
1068                         {
1069                                 if (loops % UPDATE_INTERVAL == 0)               /* Update the list if
1070                                                                                                                  * it's time */
1071                                         update_table_list(dbs);         /* Add and remove tables
1072                                                                                                  * from the list */
1073
1074                                 if (xid_wraparound_check(dbs) == 0)
1075                                 {
1076                                         res = send_query(TABLE_STATS_QUERY, dbs);       /* Get an updated
1077                                                                                                                                  * snapshot of this dbs
1078                                                                                                                                  * table stats */
1079                                         for (j = 0; j < PQntuples(res); j++)
1080                                         {                       /* loop through result set */
1081                                                 tbl_elem = DLGetHead(dbs->table_list);  /* Reset tbl_elem to top
1082                                                                                                                                  * of dbs->table_list */
1083                                                 while (tbl_elem != NULL)
1084                                                 {               /* Loop through tables in list */
1085                                                         tbl = ((tbl_info *) DLE_VAL(tbl_elem));         /* set tbl_info =
1086                                                                                                                                                  * current_table */
1087                                                         if (tbl->relid == atooid(PQgetvalue(res, j, PQfnumber(res, "oid"))))
1088                                                         {
1089                                                                 tbl->curr_analyze_count =
1090                                                                         (atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_ins"))) +
1091                                                                          atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_upd"))) +
1092                                                                          atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_del"))));
1093                                                                 tbl->curr_vacuum_count =
1094                                                                         (atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_del"))) +
1095                                                                          atol(PQgetvalue(res, j, PQfnumber(res, "n_tup_upd"))));
1096
1097                                                                 /*
1098                                                                  * Check numDeletes to see if we need to
1099                                                                  * vacuum, if so: Run vacuum analyze
1100                                                                  * (adding analyze is small so we might as
1101                                                                  * well) Update table thresholds and
1102                                                                  * related information if numDeletes is
1103                                                                  * not big enough for vacuum then check
1104                                                                  * numInserts for analyze
1105                                                                  */
1106                                                                 if (tbl->curr_vacuum_count - tbl->CountAtLastVacuum >= tbl->vacuum_threshold)
1107                                                                 {
1108                                                                         /*
1109                                                                          * if relisshared = t and database !=
1110                                                                          * template1 then only do an analyze
1111                                                                          */
1112                                                                         if (tbl->relisshared > 0 && strcmp("template1", dbs->dbname))
1113                                                                                 snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
1114                                                                         else
1115                                                                                 snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
1116                                                                         if (args->debug >= 1)
1117                                                                         {
1118                                                                                 sprintf(logbuffer, "Performing: %s", buf);
1119                                                                                 log_entry(logbuffer);
1120                                                                                 fflush(LOGOUTPUT);
1121                                                                         }
1122                                                                         send_query(buf, dbs);
1123                                                                         update_table_thresholds(dbs, tbl, VACUUM_ANALYZE);
1124                                                                         if (args->debug >= 2)
1125                                                                                 print_table_info(tbl);
1126                                                                 }
1127                                                                 else if (tbl->curr_analyze_count - tbl->CountAtLastAnalyze >= tbl->analyze_threshold)
1128                                                                 {
1129                                                                         snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
1130                                                                         if (args->debug >= 1)
1131                                                                         {
1132                                                                                 sprintf(logbuffer, "Performing: %s", buf);
1133                                                                                 log_entry(logbuffer);
1134                                                                                 fflush(LOGOUTPUT);
1135                                                                         }
1136                                                                         send_query(buf, dbs);
1137                                                                         update_table_thresholds(dbs, tbl, ANALYZE_ONLY);
1138                                                                         if (args->debug >= 2)
1139                                                                                 print_table_info(tbl);
1140                                                                 }
1141
1142                                                                 break;  /* once we have found a match, no
1143                                                                                  * need to keep checking. */
1144                                                         }
1145
1146                                                         /*
1147                                                          * Advance the table pointers for the next
1148                                                          * loop
1149                                                          */
1150                                                         tbl_elem = DLGetSucc(tbl_elem);
1151
1152                                                 }               /* end for table while loop */
1153                                         }                       /* end for j loop (tuples in PGresult) */
1154                                 }                               /* close of if(xid_wraparound_check()) */
1155                                 /* Done working on this db, Clean up, then advance cur_db */
1156                                 PQclear(res);
1157                                 res = NULL;
1158                                 db_disconnect(dbs);
1159                         }
1160                         db_elem = DLGetSucc(db_elem);           /* move on to next DB
1161                                                                                                  * regardless */
1162                 }                                               /* end of db_list while loop */
1163
1164                 /* Figure out how long to sleep etc ... */
1165                 gettimeofday(&now, 0);
1166                 diff = (int) (now.tv_sec - then.tv_sec) * 1000000.0 + (int) (now.tv_usec - then.tv_usec);
1167
1168                 sleep_secs = args->sleep_base_value + args->sleep_scaling_factor * diff / 1000000.0;
1169                 loops++;
1170                 if (args->debug >= 2)
1171                 {
1172                         sprintf(logbuffer,
1173                          "%i All DBs checked in: %.0f usec, will sleep for %i secs.",
1174                                         loops, diff, sleep_secs);
1175                         log_entry(logbuffer);
1176                         fflush(LOGOUTPUT);
1177                 }
1178
1179                 sleep(sleep_secs);              /* Larger Pause between outer loops */
1180
1181                 gettimeofday(&then, 0); /* Reset time counter */
1182
1183         }                                                       /* end of while loop */
1184
1185         /*
1186          * program is exiting, this should never run, but is here to make
1187          * compiler / valgrind happy
1188          */
1189         free_db_list(db_list);
1190         free_cmd_args();
1191         return EXIT_SUCCESS;
1192 }