Header And Logo

PostgreSQL
| The world's most advanced open source database.

vacuumlo.c

Go to the documentation of this file.
00001 /*-------------------------------------------------------------------------
00002  *
00003  * vacuumlo.c
00004  *    This removes orphaned large objects from a database.
00005  *
00006  * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group
00007  * Portions Copyright (c) 1994, Regents of the University of California
00008  *
00009  *
00010  * IDENTIFICATION
00011  *    contrib/vacuumlo/vacuumlo.c
00012  *
00013  *-------------------------------------------------------------------------
00014  */
00015 #include "postgres_fe.h"
00016 
00017 #include <sys/stat.h>
00018 #include <fcntl.h>
00019 #include <unistd.h>
00020 #ifdef HAVE_TERMIOS_H
00021 #include <termios.h>
00022 #endif
00023 
00024 #include "libpq-fe.h"
00025 
00026 #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
00027 
00028 #define BUFSIZE         1024
00029 
00030 extern char *optarg;
00031 extern int  optind,
00032             opterr;
00033 
00034 enum trivalue
00035 {
00036     TRI_DEFAULT,
00037     TRI_NO,
00038     TRI_YES
00039 };
00040 
00041 struct _param
00042 {
00043     char       *pg_user;
00044     enum trivalue pg_prompt;
00045     char       *pg_port;
00046     char       *pg_host;
00047     const char *progname;
00048     int         verbose;
00049     int         dry_run;
00050     long        transaction_limit;
00051 };
00052 
00053 static int  vacuumlo(const char *database, const struct _param * param);
00054 static void usage(const char *progname);
00055 
00056 
00057 
00058 /*
00059  * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
00060  */
00061 static int
00062 vacuumlo(const char *database, const struct _param * param)
00063 {
00064     PGconn     *conn;
00065     PGresult   *res,
00066                *res2;
00067     char        buf[BUFSIZE];
00068     long        matched;
00069     long        deleted;
00070     int         i;
00071     static char *password = NULL;
00072     bool        new_pass;
00073     bool        success = true;
00074 
00075     /* Note: password can be carried over from a previous call */
00076     if (param->pg_prompt == TRI_YES && password == NULL)
00077         password = simple_prompt("Password: ", 100, false);
00078 
00079     /*
00080      * Start the connection.  Loop until we have a password if requested by
00081      * backend.
00082      */
00083     do
00084     {
00085 #define PARAMS_ARRAY_SIZE      7
00086 
00087         const char *keywords[PARAMS_ARRAY_SIZE];
00088         const char *values[PARAMS_ARRAY_SIZE];
00089 
00090         keywords[0] = "host";
00091         values[0] = param->pg_host;
00092         keywords[1] = "port";
00093         values[1] = param->pg_port;
00094         keywords[2] = "user";
00095         values[2] = param->pg_user;
00096         keywords[3] = "password";
00097         values[3] = password;
00098         keywords[4] = "dbname";
00099         values[4] = database;
00100         keywords[5] = "fallback_application_name";
00101         values[5] = param->progname;
00102         keywords[6] = NULL;
00103         values[6] = NULL;
00104 
00105         new_pass = false;
00106         conn = PQconnectdbParams(keywords, values, true);
00107         if (!conn)
00108         {
00109             fprintf(stderr, "Connection to database \"%s\" failed\n",
00110                     database);
00111             return -1;
00112         }
00113 
00114         if (PQstatus(conn) == CONNECTION_BAD &&
00115             PQconnectionNeedsPassword(conn) &&
00116             password == NULL &&
00117             param->pg_prompt != TRI_NO)
00118         {
00119             PQfinish(conn);
00120             password = simple_prompt("Password: ", 100, false);
00121             new_pass = true;
00122         }
00123     } while (new_pass);
00124 
00125     /* check to see that the backend connection was successfully made */
00126     if (PQstatus(conn) == CONNECTION_BAD)
00127     {
00128         fprintf(stderr, "Connection to database \"%s\" failed:\n%s",
00129                 database, PQerrorMessage(conn));
00130         PQfinish(conn);
00131         return -1;
00132     }
00133 
00134     if (param->verbose)
00135     {
00136         fprintf(stdout, "Connected to database \"%s\"\n", database);
00137         if (param->dry_run)
00138             fprintf(stdout, "Test run: no large objects will be removed!\n");
00139     }
00140 
00141     /*
00142      * Don't get fooled by any non-system catalogs
00143      */
00144     res = PQexec(conn, "SET search_path = pg_catalog");
00145     if (PQresultStatus(res) != PGRES_COMMAND_OK)
00146     {
00147         fprintf(stderr, "Failed to set search_path:\n");
00148         fprintf(stderr, "%s", PQerrorMessage(conn));
00149         PQclear(res);
00150         PQfinish(conn);
00151         return -1;
00152     }
00153     PQclear(res);
00154 
00155     /*
00156      * First we create and populate the LO temp table
00157      */
00158     buf[0] = '\0';
00159     strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
00160     if (PQserverVersion(conn) >= 90000)
00161         strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
00162     else
00163         strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
00164     res = PQexec(conn, buf);
00165     if (PQresultStatus(res) != PGRES_COMMAND_OK)
00166     {
00167         fprintf(stderr, "Failed to create temp table:\n");
00168         fprintf(stderr, "%s", PQerrorMessage(conn));
00169         PQclear(res);
00170         PQfinish(conn);
00171         return -1;
00172     }
00173     PQclear(res);
00174 
00175     /*
00176      * Analyze the temp table so that planner will generate decent plans for
00177      * the DELETEs below.
00178      */
00179     buf[0] = '\0';
00180     strcat(buf, "ANALYZE vacuum_l");
00181     res = PQexec(conn, buf);
00182     if (PQresultStatus(res) != PGRES_COMMAND_OK)
00183     {
00184         fprintf(stderr, "Failed to vacuum temp table:\n");
00185         fprintf(stderr, "%s", PQerrorMessage(conn));
00186         PQclear(res);
00187         PQfinish(conn);
00188         return -1;
00189     }
00190     PQclear(res);
00191 
00192     /*
00193      * Now find any candidate tables that have columns of type oid.
00194      *
00195      * NOTE: we ignore system tables and temp tables by the expedient of
00196      * rejecting tables in schemas named 'pg_*'.  In particular, the temp
00197      * table formed above is ignored, and pg_largeobject will be too. If
00198      * either of these were scanned, obviously we'd end up with nothing to
00199      * delete...
00200      *
00201      * NOTE: the system oid column is ignored, as it has attnum < 1. This
00202      * shouldn't matter for correctness, but it saves time.
00203      */
00204     buf[0] = '\0';
00205     strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
00206     strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
00207     strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
00208     strcat(buf, "      AND a.attrelid = c.oid ");
00209     strcat(buf, "      AND a.atttypid = t.oid ");
00210     strcat(buf, "      AND c.relnamespace = s.oid ");
00211     strcat(buf, "      AND t.typname in ('oid', 'lo') ");
00212     strcat(buf, "      AND c.relkind in ('r', 'm')");
00213     strcat(buf, "      AND s.nspname !~ '^pg_'");
00214     res = PQexec(conn, buf);
00215     if (PQresultStatus(res) != PGRES_TUPLES_OK)
00216     {
00217         fprintf(stderr, "Failed to find OID columns:\n");
00218         fprintf(stderr, "%s", PQerrorMessage(conn));
00219         PQclear(res);
00220         PQfinish(conn);
00221         return -1;
00222     }
00223 
00224     for (i = 0; i < PQntuples(res); i++)
00225     {
00226         char       *schema,
00227                    *table,
00228                    *field;
00229 
00230         schema = PQgetvalue(res, i, 0);
00231         table = PQgetvalue(res, i, 1);
00232         field = PQgetvalue(res, i, 2);
00233 
00234         if (param->verbose)
00235             fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
00236 
00237         schema = PQescapeIdentifier(conn, schema, strlen(schema));
00238         table = PQescapeIdentifier(conn, table, strlen(table));
00239         field = PQescapeIdentifier(conn, field, strlen(field));
00240 
00241         if (!schema || !table || !field)
00242         {
00243             fprintf(stderr, "Out of memory\n");
00244             PQclear(res);
00245             PQfinish(conn);
00246             return -1;
00247         }
00248 
00249         snprintf(buf, BUFSIZE,
00250                  "DELETE FROM vacuum_l "
00251                  "WHERE lo IN (SELECT %s FROM %s.%s)",
00252                  field, schema, table);
00253         res2 = PQexec(conn, buf);
00254         if (PQresultStatus(res2) != PGRES_COMMAND_OK)
00255         {
00256             fprintf(stderr, "Failed to check %s in table %s.%s:\n",
00257                     field, schema, table);
00258             fprintf(stderr, "%s", PQerrorMessage(conn));
00259             PQclear(res2);
00260             PQclear(res);
00261             PQfinish(conn);
00262             return -1;
00263         }
00264         PQclear(res2);
00265 
00266         PQfreemem(schema);
00267         PQfreemem(table);
00268         PQfreemem(field);
00269     }
00270     PQclear(res);
00271 
00272     /*
00273      * Now, those entries remaining in vacuum_l are orphans.  Delete 'em.
00274      *
00275      * We don't want to run each delete as an individual transaction, because
00276      * the commit overhead would be high.  However, since 9.0 the backend will
00277      * acquire a lock per deleted LO, so deleting too many LOs per transaction
00278      * risks running out of room in the shared-memory lock table. Accordingly,
00279      * we delete up to transaction_limit LOs per transaction.
00280      */
00281     res = PQexec(conn, "begin");
00282     if (PQresultStatus(res) != PGRES_COMMAND_OK)
00283     {
00284         fprintf(stderr, "Failed to start transaction:\n");
00285         fprintf(stderr, "%s", PQerrorMessage(conn));
00286         PQclear(res);
00287         PQfinish(conn);
00288         return -1;
00289     }
00290     PQclear(res);
00291 
00292     buf[0] = '\0';
00293     strcat(buf, "SELECT lo FROM vacuum_l");
00294     res = PQexec(conn, buf);
00295     if (PQresultStatus(res) != PGRES_TUPLES_OK)
00296     {
00297         fprintf(stderr, "Failed to read temp table:\n");
00298         fprintf(stderr, "%s", PQerrorMessage(conn));
00299         PQclear(res);
00300         PQfinish(conn);
00301         return -1;
00302     }
00303 
00304     matched = PQntuples(res);
00305     deleted = 0;
00306     for (i = 0; i < matched; i++)
00307     {
00308         Oid         lo = atooid(PQgetvalue(res, i, 0));
00309 
00310         if (param->verbose)
00311         {
00312             fprintf(stdout, "\rRemoving lo %6u   ", lo);
00313             fflush(stdout);
00314         }
00315 
00316         if (param->dry_run == 0)
00317         {
00318             if (lo_unlink(conn, lo) < 0)
00319             {
00320                 fprintf(stderr, "\nFailed to remove lo %u: ", lo);
00321                 fprintf(stderr, "%s", PQerrorMessage(conn));
00322                 if (PQtransactionStatus(conn) == PQTRANS_INERROR)
00323                 {
00324                     success = false;
00325                     break;
00326                 }
00327             }
00328             else
00329                 deleted++;
00330         }
00331         else
00332             deleted++;
00333         if (param->transaction_limit > 0 &&
00334             (deleted % param->transaction_limit) == 0)
00335         {
00336             res2 = PQexec(conn, "commit");
00337             if (PQresultStatus(res2) != PGRES_COMMAND_OK)
00338             {
00339                 fprintf(stderr, "Failed to commit transaction:\n");
00340                 fprintf(stderr, "%s", PQerrorMessage(conn));
00341                 PQclear(res2);
00342                 PQclear(res);
00343                 PQfinish(conn);
00344                 return -1;
00345             }
00346             PQclear(res2);
00347             res2 = PQexec(conn, "begin");
00348             if (PQresultStatus(res2) != PGRES_COMMAND_OK)
00349             {
00350                 fprintf(stderr, "Failed to start transaction:\n");
00351                 fprintf(stderr, "%s", PQerrorMessage(conn));
00352                 PQclear(res2);
00353                 PQclear(res);
00354                 PQfinish(conn);
00355                 return -1;
00356             }
00357             PQclear(res2);
00358         }
00359     }
00360     PQclear(res);
00361 
00362     /*
00363      * That's all folks!
00364      */
00365     res = PQexec(conn, "commit");
00366     if (PQresultStatus(res) != PGRES_COMMAND_OK)
00367     {
00368         fprintf(stderr, "Failed to commit transaction:\n");
00369         fprintf(stderr, "%s", PQerrorMessage(conn));
00370         PQclear(res);
00371         PQfinish(conn);
00372         return -1;
00373     }
00374     PQclear(res);
00375 
00376     PQfinish(conn);
00377 
00378     if (param->verbose)
00379     {
00380         if (param->dry_run)
00381             fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
00382                     deleted, database);
00383         else if (success)
00384             fprintf(stdout,
00385                     "\rSuccessfully removed %ld large objects from database \"%s\".\n",
00386                     deleted, database);
00387         else
00388             fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
00389                     database, deleted, matched);
00390     }
00391 
00392     return ((param->dry_run || success) ? 0 : -1);
00393 }
00394 
00395 static void
00396 usage(const char *progname)
00397 {
00398     printf("%s removes unreferenced large objects from databases.\n\n", progname);
00399     printf("Usage:\n  %s [OPTION]... DBNAME...\n\n", progname);
00400     printf("Options:\n");
00401     printf("  -l LIMIT       commit after removing each LIMIT large objects\n");
00402     printf("  -n             don't remove large objects, just show what would be done\n");
00403     printf("  -v             write a lot of progress messages\n");
00404     printf("  -V, --version  output version information, then exit\n");
00405     printf("  -?, --help     show this help, then exit\n");
00406     printf("\nConnection options:\n");
00407     printf("  -h HOSTNAME    database server host or socket directory\n");
00408     printf("  -p PORT        database server port\n");
00409     printf("  -U USERNAME    user name to connect as\n");
00410     printf("  -w             never prompt for password\n");
00411     printf("  -W             force password prompt\n");
00412     printf("\n");
00413     printf("Report bugs to <[email protected]>.\n");
00414 }
00415 
00416 
00417 int
00418 main(int argc, char **argv)
00419 {
00420     int         rc = 0;
00421     struct _param param;
00422     int         c;
00423     int         port;
00424     const char *progname;
00425 
00426     progname = get_progname(argv[0]);
00427 
00428     /* Set default parameter values */
00429     param.pg_user = NULL;
00430     param.pg_prompt = TRI_DEFAULT;
00431     param.pg_host = NULL;
00432     param.pg_port = NULL;
00433     param.progname = progname;
00434     param.verbose = 0;
00435     param.dry_run = 0;
00436     param.transaction_limit = 1000;
00437 
00438     /* Process command-line arguments */
00439     if (argc > 1)
00440     {
00441         if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
00442         {
00443             usage(progname);
00444             exit(0);
00445         }
00446         if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
00447         {
00448             puts("vacuumlo (PostgreSQL) " PG_VERSION);
00449             exit(0);
00450         }
00451     }
00452 
00453     while (1)
00454     {
00455         c = getopt(argc, argv, "h:l:U:p:vnwW");
00456         if (c == -1)
00457             break;
00458 
00459         switch (c)
00460         {
00461             case '?':
00462                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
00463                 exit(1);
00464             case ':':
00465                 exit(1);
00466             case 'v':
00467                 param.verbose = 1;
00468                 break;
00469             case 'n':
00470                 param.dry_run = 1;
00471                 param.verbose = 1;
00472                 break;
00473             case 'l':
00474                 param.transaction_limit = strtol(optarg, NULL, 10);
00475                 if (param.transaction_limit < 0)
00476                 {
00477                     fprintf(stderr,
00478                             "%s: transaction limit must not be negative (0 disables)\n",
00479                             progname);
00480                     exit(1);
00481                 }
00482                 break;
00483             case 'U':
00484                 param.pg_user = strdup(optarg);
00485                 break;
00486             case 'w':
00487                 param.pg_prompt = TRI_NO;
00488                 break;
00489             case 'W':
00490                 param.pg_prompt = TRI_YES;
00491                 break;
00492             case 'p':
00493                 port = strtol(optarg, NULL, 10);
00494                 if ((port < 1) || (port > 65535))
00495                 {
00496                     fprintf(stderr, "%s: invalid port number: %s\n", progname, optarg);
00497                     exit(1);
00498                 }
00499                 param.pg_port = strdup(optarg);
00500                 break;
00501             case 'h':
00502                 param.pg_host = strdup(optarg);
00503                 break;
00504         }
00505     }
00506 
00507     /* No database given? Show usage */
00508     if (optind >= argc)
00509     {
00510         fprintf(stderr, "vacuumlo: missing required argument: database name\n");
00511         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
00512         exit(1);
00513     }
00514 
00515     for (c = optind; c < argc; c++)
00516     {
00517         /* Work on selected database */
00518         rc += (vacuumlo(argv[c], &param) != 0);
00519     }
00520 
00521     return rc;
00522 }