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