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