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