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

   1 /*-------------------------------------------------------------------------
   2  *
   3  * vacuumlo.c
   4  *        This removes orphaned large objects from a database.
   5  *
   6  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
   7  * Portions Copyright (c) 1994, Regents of the University of California
   8  *
   9  *
  10  * IDENTIFICATION
  11  *        $PostgreSQL: pgsql/contrib/vacuumlo/vacuumlo.c,v 1.41 2009/02/27 09:30:21 petere Exp $
  12  *
  13  *-------------------------------------------------------------------------
  14  */
  15 #include "postgres_fe.h"
  16
  17 #include <sys/stat.h>
  18 #include <fcntl.h>
  19 #include <unistd.h>
  20 #ifdef HAVE_TERMIOS_H
  21 #include <termios.h>
  22 #endif
  23
  24 #include "libpq-fe.h"
  25 #include "libpq/libpq-fs.h"
  26
  27 #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
  28
  29 #define BUFSIZE                 1024
  30
  31 extern char *optarg;
  32 extern int      optind,
  33                         opterr,
  34                         optopt;
  35
  36 enum trivalue
  37 {
  38         TRI_DEFAULT,
  39         TRI_NO,
  40         TRI_YES
  41 };
  42
  43 struct _param
  44 {
  45         char       *pg_user;
  46         enum trivalue pg_prompt;
  47         char       *pg_port;
  48         char       *pg_host;
  49         int                     verbose;
  50         int                     dry_run;
  51 };
  52
  53 int                     vacuumlo(char *, struct _param *);
  54 void            usage(const char *progname);
  55
  56
  57
  58 /*
  59  * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
  60  */
  61 int
  62 vacuumlo(char *database, struct _param * param)
  63 {
  64         PGconn     *conn;
  65         PGresult   *res,
  66                            *res2;
  67         char            buf[BUFSIZE];
  68         int                     matched;
  69         int                     deleted;
  70         int                     i;
  71         static char *password = NULL;
  72         bool            new_pass;
  73
  74         if (param->pg_prompt == TRI_YES && password == NULL)
  75                 password = simple_prompt("Password: ", 100, false);
  76
  77         /*
  78          * Start the connection.  Loop until we have a password if requested by
  79          * backend.
  80          */
  81         do
  82         {
  83                 new_pass = false;
  84
  85                 conn = PQsetdbLogin(param->pg_host,
  86                                                         param->pg_port,
  87                                                         NULL,
  88                                                         NULL,
  89                                                         database,
  90                                                         param->pg_user,
  91                                                         password);
  92                 if (!conn)
  93                 {
  94                         fprintf(stderr, "Connection to database \"%s\" failed\n",
  95                                         database);
  96                         return -1;
  97                 }
  98
  99                 if (PQstatus(conn) == CONNECTION_BAD &&
 100                         PQconnectionNeedsPassword(conn) &&
 101                         password == NULL &&
 102                         param->pg_prompt != TRI_NO)
 103                 {
 104                         PQfinish(conn);
 105                         password = simple_prompt("Password: ", 100, false);
 106                         new_pass = true;
 107                 }
 108         } while (new_pass);
 109
 110         /* check to see that the backend connection was successfully made */
 111         if (PQstatus(conn) == CONNECTION_BAD)
 112         {
 113                 fprintf(stderr, "Connection to database \"%s\" failed:\n%s",
 114                                 database, PQerrorMessage(conn));
 115                 PQfinish(conn);
 116                 return -1;
 117         }
 118
 119         if (param->verbose)
 120         {
 121                 fprintf(stdout, "Connected to %s\n", database);
 122                 if (param->dry_run)
 123                         fprintf(stdout, "Test run: no large objects will be removed!\n");
 124         }
 125
 126         /*
 127          * Don't get fooled by any non-system catalogs
 128          */
 129         res = PQexec(conn, "SET search_path = pg_catalog");
 130         if (PQresultStatus(res) != PGRES_COMMAND_OK)
 131         {
 132                 fprintf(stderr, "Failed to set search_path:\n");
 133                 fprintf(stderr, "%s", PQerrorMessage(conn));
 134                 PQclear(res);
 135                 PQfinish(conn);
 136                 return -1;
 137         }
 138         PQclear(res);
 139
 140         /*
 141          * First we create and populate the LO temp table
 142          */
 143         buf[0] = '\0';
 144         strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
 145         strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject ");
 146         res = PQexec(conn, buf);
 147         if (PQresultStatus(res) != PGRES_COMMAND_OK)
 148         {
 149                 fprintf(stderr, "Failed to create temp table:\n");
 150                 fprintf(stderr, "%s", PQerrorMessage(conn));
 151                 PQclear(res);
 152                 PQfinish(conn);
 153                 return -1;
 154         }
 155         PQclear(res);
 156
 157         /*
 158          * Vacuum the temp table so that planner will generate decent plans for
 159          * the DELETEs below.
 160          */
 161         buf[0] = '\0';
 162         strcat(buf, "VACUUM ANALYZE vacuum_l");
 163         res = PQexec(conn, buf);
 164         if (PQresultStatus(res) != PGRES_COMMAND_OK)
 165         {
 166                 fprintf(stderr, "Failed to vacuum temp table:\n");
 167                 fprintf(stderr, "%s", PQerrorMessage(conn));
 168                 PQclear(res);
 169                 PQfinish(conn);
 170                 return -1;
 171         }
 172         PQclear(res);
 173
 174         /*
 175          * Now find any candidate tables that have columns of type oid.
 176          *
 177          * NOTE: we ignore system tables and temp tables by the expedient of
 178          * rejecting tables in schemas named 'pg_*'.  In particular, the temp
 179          * table formed above is ignored, and pg_largeobject will be too. If
 180          * either of these were scanned, obviously we'd end up with nothing to
 181          * delete...
 182          *
 183          * NOTE: the system oid column is ignored, as it has attnum < 1. This
 184          * shouldn't matter for correctness, but it saves time.
 185          */
 186         buf[0] = '\0';
 187         strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
 188         strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
 189         strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
 190         strcat(buf, "      AND a.attrelid = c.oid ");
 191         strcat(buf, "      AND a.atttypid = t.oid ");
 192         strcat(buf, "      AND c.relnamespace = s.oid ");
 193         strcat(buf, "      AND t.typname in ('oid', 'lo') ");
 194         strcat(buf, "      AND c.relkind = 'r'");
 195         strcat(buf, "      AND s.nspname !~ '^pg_'");
 196         res = PQexec(conn, buf);
 197         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 198         {
 199                 fprintf(stderr, "Failed to find OID columns:\n");
 200                 fprintf(stderr, "%s", PQerrorMessage(conn));
 201                 PQclear(res);
 202                 PQfinish(conn);
 203                 return -1;
 204         }
 205
 206         for (i = 0; i < PQntuples(res); i++)
 207         {
 208                 char       *schema,
 209                                    *table,
 210                                    *field;
 211
 212                 schema = PQgetvalue(res, i, 0);
 213                 table = PQgetvalue(res, i, 1);
 214                 field = PQgetvalue(res, i, 2);
 215
 216                 if (param->verbose)
 217                         fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
 218
 219                 /*
 220                  * The "IN" construct used here was horribly inefficient before
 221                  * Postgres 7.4, but should be now competitive if not better than the
 222                  * bogus join we used before.
 223                  */
 224                 snprintf(buf, BUFSIZE,
 225                                  "DELETE FROM vacuum_l "
 226                                  "WHERE lo IN (SELECT \"%s\" FROM \"%s\".\"%s\")",
 227                                  field, schema, table);
 228                 res2 = PQexec(conn, buf);
 229                 if (PQresultStatus(res2) != PGRES_COMMAND_OK)
 230                 {
 231                         fprintf(stderr, "Failed to check %s in table %s.%s:\n",
 232                                         field, schema, table);
 233                         fprintf(stderr, "%s", PQerrorMessage(conn));
 234                         PQclear(res2);
 235                         PQclear(res);
 236                         PQfinish(conn);
 237                         return -1;
 238                 }
 239                 PQclear(res2);
 240         }
 241         PQclear(res);
 242
 243         /*
 244          * Run the actual deletes in a single transaction.      Note that this would
 245          * be a bad idea in pre-7.1 Postgres releases (since rolling back a table
 246          * delete used to cause problems), but it should be safe now.
 247          */
 248         res = PQexec(conn, "begin");
 249         PQclear(res);
 250
 251         /*
 252          * Finally, those entries remaining in vacuum_l are orphans.
 253          */
 254         buf[0] = '\0';
 255         strcat(buf, "SELECT lo ");
 256         strcat(buf, "FROM vacuum_l");
 257         res = PQexec(conn, buf);
 258         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 259         {
 260                 fprintf(stderr, "Failed to read temp table:\n");
 261                 fprintf(stderr, "%s", PQerrorMessage(conn));
 262                 PQclear(res);
 263                 PQfinish(conn);
 264                 return -1;
 265         }
 266
 267         matched = PQntuples(res);
 268         deleted = 0;
 269         for (i = 0; i < matched; i++)
 270         {
 271                 Oid                     lo = atooid(PQgetvalue(res, i, 0));
 272
 273                 if (param->verbose)
 274                 {
 275                         fprintf(stdout, "\rRemoving lo %6u   ", lo);
 276                         fflush(stdout);
 277                 }
 278
 279                 if (param->dry_run == 0)
 280                 {
 281                         if (lo_unlink(conn, lo) < 0)
 282                         {
 283                                 fprintf(stderr, "\nFailed to remove lo %u: ", lo);
 284                                 fprintf(stderr, "%s", PQerrorMessage(conn));
 285                         }
 286                         else
 287                                 deleted++;
 288                 }
 289                 else
 290                         deleted++;
 291         }
 292         PQclear(res);
 293
 294         /*
 295          * That's all folks!
 296          */
 297         res = PQexec(conn, "end");
 298         PQclear(res);
 299
 300         PQfinish(conn);
 301
 302         if (param->verbose)
 303                 fprintf(stdout, "\r%s %d large objects from %s.\n",
 304                    (param->dry_run ? "Would remove" : "Removed"), deleted, database);
 305
 306         return 0;
 307 }
 308
 309 void
 310 usage(const char *progname)
 311 {
 312         printf("%s removes unreferenced large objects from databases.\n\n", progname);
 313         printf("Usage:\n  %s [OPTION]... DBNAME...\n\n", progname);
 314         printf("Options:\n");
 315         printf("  -h HOSTNAME  database server host or socket directory\n");
 316         printf("  -n           don't remove large objects, just show what would be done\n");
 317         printf("  -p PORT      database server port\n");
 318         printf("  -U USERNAME  user name to connect as\n");
 319         printf("  -w           never prompt for password\n");
 320         printf("  -W           force password prompt\n");
 321         printf("  -v           write a lot of progress messages\n");
 322         printf("  --help       show this help, then exit\n");
 323         printf("  --version    output version information, then exit\n");
 324         printf("\n");
 325         printf("Report bugs to <pgsql-bugs@postgresql.org>.\n");
 326 }
 327
 328
 329 int
 330 main(int argc, char **argv)
 331 {
 332         int                     rc = 0;
 333         struct _param param;
 334         int                     c;
 335         int                     port;
 336         const char *progname;
 337
 338         progname = get_progname(argv[0]);
 339
 340         /* Parameter handling */
 341         param.pg_user = NULL;
 342         param.pg_prompt = TRI_DEFAULT;
 343         param.pg_host = NULL;
 344         param.pg_port = NULL;
 345         param.verbose = 0;
 346         param.dry_run = 0;
 347
 348         if (argc > 1)
 349         {
 350                 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
 351                 {
 352                         usage(progname);
 353                         exit(0);
 354                 }
 355                 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
 356                 {
 357                         puts("vacuumlo (PostgreSQL) " PG_VERSION);
 358                         exit(0);
 359                 }
 360         }
 361
 362         while (1)
 363         {
 364                 c = getopt(argc, argv, "h:U:p:vnwW");
 365                 if (c == -1)
 366                         break;
 367
 368                 switch (c)
 369                 {
 370                         case '?':
 371                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 372                                 exit(1);
 373                         case ':':
 374                                 exit(1);
 375                         case 'v':
 376                                 param.verbose = 1;
 377                                 break;
 378                         case 'n':
 379                                 param.dry_run = 1;
 380                                 param.verbose = 1;
 381                                 break;
 382                         case 'U':
 383                                 param.pg_user = strdup(optarg);
 384                                 break;
 385                         case 'w':
 386                                 param.pg_prompt = TRI_NO;
 387                                 break;
 388                         case 'W':
 389                                 param.pg_prompt = TRI_YES;
 390                                 break;
 391                         case 'p':
 392                                 port = strtol(optarg, NULL, 10);
 393                                 if ((port < 1) || (port > 65535))
 394                                 {
 395                                         fprintf(stderr, "%s: invalid port number: %s\n", progname, optarg);
 396                                         exit(1);
 397                                 }
 398                                 param.pg_port = strdup(optarg);
 399                                 break;
 400                         case 'h':
 401                                 param.pg_host = strdup(optarg);
 402                                 break;
 403                 }
 404         }
 405
 406         /* No database given? Show usage */
 407         if (optind >= argc)
 408         {
 409                 fprintf(stderr, "vacuumlo: missing required argument: database name\n");
 410                 fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
 411                 exit(1);
 412         }
 413
 414         for (c = optind; c < argc; c++)
 415         {
 416                 /* Work on selected database */
 417                 rc += (vacuumlo(argv[c], &param) != 0);
 418         }
 419
 420         return rc;
 421 }