PostgreSQL Source Code  git master
vacuumlo.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * vacuumlo.c
4  * This removes orphaned large objects from a database.
5  *
6  * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
7  * Portions Copyright (c) 1994, Regents of the University of California
8  *
9  *
10  * IDENTIFICATION
11  * contrib/vacuumlo/vacuumlo.c
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 "catalog/pg_class_d.h"
25 #include "common/logging.h"
26 #include "fe_utils/connect.h"
27 #include "getopt_long.h"
28 #include "libpq-fe.h"
29 #include "pg_getopt.h"
30 
31 #define BUFSIZE 1024
32 
34 {
38 };
39 
40 struct _param
41 {
42  char *pg_user;
44  char *pg_port;
45  char *pg_host;
46  const char *progname;
47  int verbose;
48  int dry_run;
50 };
51 
52 static int vacuumlo(const char *database, const struct _param *param);
53 static void usage(const char *progname);
54 
55 
56 
57 /*
58  * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
59  */
60 static int
61 vacuumlo(const char *database, const struct _param *param)
62 {
63  PGconn *conn;
64  PGresult *res,
65  *res2;
66  char buf[BUFSIZE];
67  long matched;
68  long deleted;
69  int i;
70  bool new_pass;
71  bool success = true;
72  static bool have_password = false;
73  static char password[100];
74 
75  /* Note: password can be carried over from a previous call */
76  if (param->pg_prompt == TRI_YES && !have_password)
77  {
78  simple_prompt("Password: ", password, sizeof(password), false);
79  have_password = true;
80  }
81 
82  /*
83  * Start the connection. Loop until we have a password if requested by
84  * backend.
85  */
86  do
87  {
88 #define PARAMS_ARRAY_SIZE 7
89 
90  const char *keywords[PARAMS_ARRAY_SIZE];
91  const char *values[PARAMS_ARRAY_SIZE];
92 
93  keywords[0] = "host";
94  values[0] = param->pg_host;
95  keywords[1] = "port";
96  values[1] = param->pg_port;
97  keywords[2] = "user";
98  values[2] = param->pg_user;
99  keywords[3] = "password";
100  values[3] = have_password ? password : NULL;
101  keywords[4] = "dbname";
102  values[4] = database;
103  keywords[5] = "fallback_application_name";
104  values[5] = param->progname;
105  keywords[6] = NULL;
106  values[6] = NULL;
107 
108  new_pass = false;
109  conn = PQconnectdbParams(keywords, values, true);
110  if (!conn)
111  {
112  pg_log_error("connection to database \"%s\" failed", database);
113  return -1;
114  }
115 
116  if (PQstatus(conn) == CONNECTION_BAD &&
118  !have_password &&
119  param->pg_prompt != TRI_NO)
120  {
121  PQfinish(conn);
122  simple_prompt("Password: ", password, sizeof(password), false);
123  have_password = true;
124  new_pass = true;
125  }
126  } while (new_pass);
127 
128  /* check to see that the backend connection was successfully made */
129  if (PQstatus(conn) == CONNECTION_BAD)
130  {
131  pg_log_error("connection to database \"%s\" failed: %s",
132  database, PQerrorMessage(conn));
133  PQfinish(conn);
134  return -1;
135  }
136 
137  if (param->verbose)
138  {
139  fprintf(stdout, "Connected to database \"%s\"\n", database);
140  if (param->dry_run)
141  fprintf(stdout, "Test run: no large objects will be removed!\n");
142  }
143 
145  if (PQresultStatus(res) != PGRES_TUPLES_OK)
146  {
147  pg_log_error("failed to set search_path: %s", PQerrorMessage(conn));
148  PQclear(res);
149  PQfinish(conn);
150  return -1;
151  }
152  PQclear(res);
153 
154  /*
155  * First we create and populate the LO temp table
156  */
157  buf[0] = '\0';
158  strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
159  if (PQserverVersion(conn) >= 90000)
160  strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
161  else
162  strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
163  res = PQexec(conn, buf);
164  if (PQresultStatus(res) != PGRES_COMMAND_OK)
165  {
166  pg_log_error("failed to create temp table: %s", PQerrorMessage(conn));
167  PQclear(res);
168  PQfinish(conn);
169  return -1;
170  }
171  PQclear(res);
172 
173  /*
174  * Analyze the temp table so that planner will generate decent plans for
175  * the DELETEs below.
176  */
177  buf[0] = '\0';
178  strcat(buf, "ANALYZE vacuum_l");
179  res = PQexec(conn, buf);
180  if (PQresultStatus(res) != PGRES_COMMAND_OK)
181  {
182  pg_log_error("failed to vacuum temp table: %s", PQerrorMessage(conn));
183  PQclear(res);
184  PQfinish(conn);
185  return -1;
186  }
187  PQclear(res);
188 
189  /*
190  * Now find any candidate tables that have columns of type oid.
191  *
192  * NOTE: we ignore system tables and temp tables by the expedient of
193  * rejecting tables in schemas named 'pg_*'. In particular, the temp
194  * table formed above is ignored, and pg_largeobject will be too. If
195  * either of these were scanned, obviously we'd end up with nothing to
196  * delete...
197  */
198  buf[0] = '\0';
199  strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
200  strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
201  strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
202  strcat(buf, " AND a.attrelid = c.oid ");
203  strcat(buf, " AND a.atttypid = t.oid ");
204  strcat(buf, " AND c.relnamespace = s.oid ");
205  strcat(buf, " AND t.typname in ('oid', 'lo') ");
206  strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
207  strcat(buf, " AND s.nspname !~ '^pg_'");
208  res = PQexec(conn, buf);
209  if (PQresultStatus(res) != PGRES_TUPLES_OK)
210  {
211  pg_log_error("failed to find OID columns: %s", PQerrorMessage(conn));
212  PQclear(res);
213  PQfinish(conn);
214  return -1;
215  }
216 
217  for (i = 0; i < PQntuples(res); i++)
218  {
219  char *schema,
220  *table,
221  *field;
222 
223  schema = PQgetvalue(res, i, 0);
224  table = PQgetvalue(res, i, 1);
225  field = PQgetvalue(res, i, 2);
226 
227  if (param->verbose)
228  fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
229 
230  schema = PQescapeIdentifier(conn, schema, strlen(schema));
231  table = PQescapeIdentifier(conn, table, strlen(table));
232  field = PQescapeIdentifier(conn, field, strlen(field));
233 
234  if (!schema || !table || !field)
235  {
236  pg_log_error("%s", PQerrorMessage(conn));
237  PQclear(res);
238  PQfinish(conn);
239  if (schema != NULL)
240  PQfreemem(schema);
241  if (table != NULL)
242  PQfreemem(table);
243  if (field != NULL)
244  PQfreemem(field);
245  return -1;
246  }
247 
248  snprintf(buf, BUFSIZE,
249  "DELETE FROM vacuum_l "
250  "WHERE lo IN (SELECT %s FROM %s.%s)",
251  field, schema, table);
252  res2 = PQexec(conn, buf);
253  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
254  {
255  pg_log_error("failed to check %s in table %s.%s: %s",
256  field, schema, table, PQerrorMessage(conn));
257  PQclear(res2);
258  PQclear(res);
259  PQfinish(conn);
260  PQfreemem(schema);
261  PQfreemem(table);
262  PQfreemem(field);
263  return -1;
264  }
265  PQclear(res2);
266 
267  PQfreemem(schema);
268  PQfreemem(table);
269  PQfreemem(field);
270  }
271  PQclear(res);
272 
273  /*
274  * Now, those entries remaining in vacuum_l are orphans. Delete 'em.
275  *
276  * We don't want to run each delete as an individual transaction, because
277  * the commit overhead would be high. However, since 9.0 the backend will
278  * acquire a lock per deleted LO, so deleting too many LOs per transaction
279  * risks running out of room in the shared-memory lock table. Accordingly,
280  * we delete up to transaction_limit LOs per transaction.
281  */
282  res = PQexec(conn, "begin");
283  if (PQresultStatus(res) != PGRES_COMMAND_OK)
284  {
285  pg_log_error("failed to start transaction: %s", PQerrorMessage(conn));
286  PQclear(res);
287  PQfinish(conn);
288  return -1;
289  }
290  PQclear(res);
291 
292  buf[0] = '\0';
293  strcat(buf,
294  "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
295  res = PQexec(conn, buf);
296  if (PQresultStatus(res) != PGRES_COMMAND_OK)
297  {
298  pg_log_error("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
299  PQclear(res);
300  PQfinish(conn);
301  return -1;
302  }
303  PQclear(res);
304 
305  snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
306  param->transaction_limit > 0 ? param->transaction_limit : 1000L);
307 
308  deleted = 0;
309 
310  do
311  {
312  res = PQexec(conn, buf);
313  if (PQresultStatus(res) != PGRES_TUPLES_OK)
314  {
315  pg_log_error("FETCH FORWARD failed: %s", PQerrorMessage(conn));
316  PQclear(res);
317  PQfinish(conn);
318  return -1;
319  }
320 
321  matched = PQntuples(res);
322  if (matched <= 0)
323  {
324  /* at end of resultset */
325  PQclear(res);
326  break;
327  }
328 
329  for (i = 0; i < matched; i++)
330  {
331  Oid lo = atooid(PQgetvalue(res, i, 0));
332 
333  if (param->verbose)
334  {
335  fprintf(stdout, "\rRemoving lo %6u ", lo);
336  fflush(stdout);
337  }
338 
339  if (param->dry_run == 0)
340  {
341  if (lo_unlink(conn, lo) < 0)
342  {
343  pg_log_error("failed to remove lo %u: %s", lo,
344  PQerrorMessage(conn));
346  {
347  success = false;
348  break; /* out of inner for-loop */
349  }
350  }
351  else
352  deleted++;
353  }
354  else
355  deleted++;
356 
357  if (param->transaction_limit > 0 &&
358  (deleted % param->transaction_limit) == 0)
359  {
360  res2 = PQexec(conn, "commit");
361  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
362  {
363  pg_log_error("failed to commit transaction: %s",
364  PQerrorMessage(conn));
365  PQclear(res2);
366  PQclear(res);
367  PQfinish(conn);
368  return -1;
369  }
370  PQclear(res2);
371  res2 = PQexec(conn, "begin");
372  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
373  {
374  pg_log_error("failed to start transaction: %s",
375  PQerrorMessage(conn));
376  PQclear(res2);
377  PQclear(res);
378  PQfinish(conn);
379  return -1;
380  }
381  PQclear(res2);
382  }
383  }
384 
385  PQclear(res);
386  } while (success);
387 
388  /*
389  * That's all folks!
390  */
391  res = PQexec(conn, "commit");
392  if (PQresultStatus(res) != PGRES_COMMAND_OK)
393  {
394  pg_log_error("failed to commit transaction: %s",
395  PQerrorMessage(conn));
396  PQclear(res);
397  PQfinish(conn);
398  return -1;
399  }
400  PQclear(res);
401 
402  PQfinish(conn);
403 
404  if (param->verbose)
405  {
406  if (param->dry_run)
407  fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
408  deleted, database);
409  else if (success)
410  fprintf(stdout,
411  "\rSuccessfully removed %ld large objects from database \"%s\".\n",
412  deleted, database);
413  else
414  fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
415  database, deleted, matched);
416  }
417 
418  return ((param->dry_run || success) ? 0 : -1);
419 }
420 
421 static void
422 usage(const char *progname)
423 {
424  printf("%s removes unreferenced large objects from databases.\n\n", progname);
425  printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
426  printf("Options:\n");
427  printf(" -l, --limit=LIMIT commit after removing each LIMIT large objects\n");
428  printf(" -n, --dry-run don't remove large objects, just show what would be done\n");
429  printf(" -v, --verbose write a lot of progress messages\n");
430  printf(" -V, --version output version information, then exit\n");
431  printf(" -?, --help show this help, then exit\n");
432  printf("\nConnection options:\n");
433  printf(" -h, --host=HOSTNAME database server host or socket directory\n");
434  printf(" -p, --port=PORT database server port\n");
435  printf(" -U, --username=USERNAME user name to connect as\n");
436  printf(" -w, --no-password never prompt for password\n");
437  printf(" -W, --password force password prompt\n");
438  printf("\n");
439  printf("Report bugs to <pgsql-bugs@lists.postgresql.org>.\n");
440 }
441 
442 
443 int
444 main(int argc, char **argv)
445 {
446  static struct option long_options[] = {
447  {"host", required_argument, NULL, 'h'},
448  {"limit", required_argument, NULL, 'l'},
449  {"dry-run", no_argument, NULL, 'n'},
450  {"port", required_argument, NULL, 'p'},
451  {"username", required_argument, NULL, 'U'},
452  {"verbose", no_argument, NULL, 'v'},
453  {"version", no_argument, NULL, 'V'},
454  {"no-password", no_argument, NULL, 'w'},
455  {"password", no_argument, NULL, 'W'},
456  {"help", no_argument, NULL, '?'},
457  {NULL, 0, NULL, 0}
458  };
459 
460  int rc = 0;
461  struct _param param;
462  int c;
463  int port;
464  const char *progname;
465  int optindex;
466 
467  pg_logging_init(argv[0]);
468  progname = get_progname(argv[0]);
469 
470  /* Set default parameter values */
471  param.pg_user = NULL;
472  param.pg_prompt = TRI_DEFAULT;
473  param.pg_host = NULL;
474  param.pg_port = NULL;
475  param.progname = progname;
476  param.verbose = 0;
477  param.dry_run = 0;
478  param.transaction_limit = 1000;
479 
480  /* Process command-line arguments */
481  if (argc > 1)
482  {
483  if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
484  {
485  usage(progname);
486  exit(0);
487  }
488  if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
489  {
490  puts("vacuumlo (PostgreSQL) " PG_VERSION);
491  exit(0);
492  }
493  }
494 
495  while ((c = getopt_long(argc, argv, "h:l:np:U:vwW", long_options, &optindex)) != -1)
496  {
497  switch (c)
498  {
499  case '?':
500  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
501  exit(1);
502  case 'h':
503  param.pg_host = pg_strdup(optarg);
504  break;
505  case 'l':
506  param.transaction_limit = strtol(optarg, NULL, 10);
507  if (param.transaction_limit < 0)
508  {
509  pg_log_error("transaction limit must not be negative (0 disables)");
510  exit(1);
511  }
512  break;
513  case 'n':
514  param.dry_run = 1;
515  param.verbose = 1;
516  break;
517  case 'p':
518  port = strtol(optarg, NULL, 10);
519  if ((port < 1) || (port > 65535))
520  {
521  pg_log_error("invalid port number: %s", optarg);
522  exit(1);
523  }
524  param.pg_port = pg_strdup(optarg);
525  break;
526  case 'U':
527  param.pg_user = pg_strdup(optarg);
528  break;
529  case 'v':
530  param.verbose = 1;
531  break;
532  case 'w':
533  param.pg_prompt = TRI_NO;
534  break;
535  case 'W':
536  param.pg_prompt = TRI_YES;
537  break;
538  default:
539  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
540  exit(1);
541  }
542  }
543 
544  /* No database given? Show usage */
545  if (optind >= argc)
546  {
547  pg_log_error("missing required argument: database name");
548  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
549  exit(1);
550  }
551 
552  for (c = optind; c < argc; c++)
553  {
554  /* Work on selected database */
555  rc += (vacuumlo(argv[c], &param) != 0);
556  }
557 
558  return rc;
559 }
static char password[100]
Definition: streamutil.c:53
const char * progname
Definition: vacuumlo.c:46
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6623
#define PARAMS_ARRAY_SIZE
int main(int argc, char **argv)
Definition: vacuumlo.c:444
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
int verbose
Definition: vacuumlo.c:47
const char * get_progname(const char *argv0)
Definition: path.c:453
#define pg_log_error(...)
Definition: logging.h:79
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
int lo_unlink(PGconn *conn, Oid lobjId)
Definition: fe-lobj.c:628
void pg_logging_init(const char *argv0)
Definition: logging.c:39
char * pg_port
Definition: vacuumlo.c:44
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4125
#define printf(...)
Definition: port.h:198
unsigned int Oid
Definition: postgres_ext.h:31
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6613
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:196
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2692
PGconn * PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname)
Definition: fe-connect.c:624
int dry_run
Definition: vacuumlo.c:48
char * PQescapeIdentifier(PGconn *conn, const char *str, size_t len)
Definition: fe-exec.c:3570
#define required_argument
Definition: getopt_long.h:25
int optind
Definition: getopt.c:50
PGconn * conn
Definition: streamutil.c:54
char * c
static char * buf
Definition: pg_test_fsync.c:67
void simple_prompt(const char *prompt, char *destination, size_t destlen, bool echo)
Definition: sprompt.c:37
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
#define atooid(x)
Definition: postgres_ext.h:42
#define BUFSIZE
Definition: vacuumlo.c:31
static int port
Definition: pg_regress.c:91
PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
Definition: fe-connect.c:6578
trivalue
Definition: vacuumlo.c:33
#define CppAsString2(x)
Definition: c.h:224
#define no_argument
Definition: getopt_long.h:24
void PQclear(PGresult *res)
Definition: fe-exec.c:694
static bool have_password
Definition: streamutil.c:52
static int vacuumlo(const char *database, const struct _param *param)
Definition: vacuumlo.c:61
enum trivalue pg_prompt
Definition: vacuumlo.c:43
char * pg_host
Definition: vacuumlo.c:45
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
static Datum values[MAXATTR]
Definition: bootstrap.c:167
int PQconnectionNeedsPassword(const PGconn *conn)
Definition: fe-connect.c:6657
char * optarg
Definition: getopt.c:52
int i
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1939
static void usage(const char *progname)
Definition: vacuumlo.c:422
long transaction_limit
Definition: vacuumlo.c:49
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6570
static bool success
Definition: initdb.c:163
char * pg_user
Definition: vacuumlo.c:42
#define snprintf
Definition: port.h:192
#define _(x)
Definition: elog.c:87
void PQfreemem(void *ptr)
Definition: fe-exec.c:3296