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