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.40 2009/02/26 16:02:37 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(void);
  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(void)
 311 {
 312         printf("vacuumlo removes unreferenced large objects from databases.\n\n");
 313         printf("Usage:\n  vacuumlo [OPTION]... DBNAME...\n\n");
 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("\n");
 323 }
 324
 325
 326 int
 327 main(int argc, char **argv)
 328 {
 329         int                     rc = 0;
 330         struct _param param;
 331         int                     c;
 332         int                     port;
 333
 334         /* Parameter handling */
 335         param.pg_user = NULL;
 336         param.pg_prompt = TRI_DEFAULT;
 337         param.pg_host = NULL;
 338         param.pg_port = NULL;
 339         param.verbose = 0;
 340         param.dry_run = 0;
 341
 342         while (1)
 343         {
 344                 c = getopt(argc, argv, "?h:U:p:vnwW");
 345                 if (c == -1)
 346                         break;
 347
 348                 switch (c)
 349                 {
 350                         case '?':
 351                                 if (optopt == '?')
 352                                 {
 353                                         usage();
 354                                         exit(0);
 355                                 }
 356                                 exit(1);
 357                         case ':':
 358                                 exit(1);
 359                         case 'v':
 360                                 param.verbose = 1;
 361                                 break;
 362                         case 'n':
 363                                 param.dry_run = 1;
 364                                 param.verbose = 1;
 365                                 break;
 366                         case 'U':
 367                                 param.pg_user = strdup(optarg);
 368                                 break;
 369                         case 'w':
 370                                 param.pg_prompt = TRI_NO;
 371                                 break;
 372                         case 'W':
 373                                 param.pg_prompt = TRI_YES;
 374                                 break;
 375                         case 'p':
 376                                 port = strtol(optarg, NULL, 10);
 377                                 if ((port < 1) || (port > 65535))
 378                                 {
 379                                         fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0], optarg);
 380                                         exit(1);
 381                                 }
 382                                 param.pg_port = strdup(optarg);
 383                                 break;
 384                         case 'h':
 385                                 param.pg_host = strdup(optarg);
 386                                 break;
 387                 }
 388         }
 389
 390         /* No database given? Show usage */
 391         if (optind >= argc)
 392         {
 393                 fprintf(stderr, "vacuumlo: missing required argument: database name\n");
 394                 fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
 395                 exit(1);
 396         }
 397
 398         for (c = optind; c < argc; c++)
 399         {
 400                 /* Work on selected database */
 401                 rc += (vacuumlo(argv[c], &param) != 0);
 402         }
 403
 404         return rc;
 405 }