PostgreSQL Source Code  git master
vacuumlo.c File Reference
#include "postgres_fe.h"
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include "catalog/pg_class_d.h"
#include "common/connect.h"
#include "common/logging.h"
#include "common/string.h"
#include "getopt_long.h"
#include "libpq-fe.h"
#include "pg_getopt.h"
Include dependency graph for vacuumlo.c:

Go to the source code of this file.

Data Structures

struct  _param
 

Macros

#define BUFSIZE   1024
 
#define PARAMS_ARRAY_SIZE   7
 

Enumerations

enum  trivalue {
  TRI_DEFAULT, TRI_NO, TRI_YES, TRI_DEFAULT,
  TRI_NO, TRI_YES, TRI_DEFAULT, TRI_NO,
  TRI_YES, TRI_DEFAULT, TRI_NO, TRI_YES
}
 

Functions

static int vacuumlo (const char *database, const struct _param *param)
 
static void usage (const char *progname)
 
int main (int argc, char **argv)
 

Macro Definition Documentation

◆ BUFSIZE

#define BUFSIZE   1024

Definition at line 32 of file vacuumlo.c.

Referenced by vacuumlo().

◆ PARAMS_ARRAY_SIZE

#define PARAMS_ARRAY_SIZE   7

Referenced by vacuumlo().

Enumeration Type Documentation

◆ trivalue

enum trivalue
Enumerator
TRI_DEFAULT 
TRI_NO 
TRI_YES 
TRI_DEFAULT 
TRI_NO 
TRI_YES 
TRI_DEFAULT 
TRI_NO 
TRI_YES 
TRI_DEFAULT 
TRI_NO 
TRI_YES 

Definition at line 34 of file vacuumlo.c.

35 {
37  TRI_NO,
38  TRI_YES
39 };

Function Documentation

◆ main()

int main ( int  argc,
char **  argv 
)

Definition at line 440 of file vacuumlo.c.

References _, _param::dry_run, fprintf, get_progname(), getopt_long(), no_argument, optarg, optind, _param::pg_host, pg_log_error, pg_logging_init(), _param::pg_port, _param::pg_prompt, pg_strdup(), _param::pg_user, port, _param::progname, required_argument, _param::transaction_limit, TRI_DEFAULT, TRI_NO, TRI_YES, usage(), vacuumlo(), and _param::verbose.

441 {
442  static struct option long_options[] = {
443  {"host", required_argument, NULL, 'h'},
444  {"limit", required_argument, NULL, 'l'},
445  {"dry-run", no_argument, NULL, 'n'},
446  {"port", required_argument, NULL, 'p'},
447  {"username", required_argument, NULL, 'U'},
448  {"verbose", no_argument, NULL, 'v'},
449  {"version", no_argument, NULL, 'V'},
450  {"no-password", no_argument, NULL, 'w'},
451  {"password", no_argument, NULL, 'W'},
452  {"help", no_argument, NULL, '?'},
453  {NULL, 0, NULL, 0}
454  };
455 
456  int rc = 0;
457  struct _param param;
458  int c;
459  int port;
460  const char *progname;
461  int optindex;
462 
463  pg_logging_init(argv[0]);
464  progname = get_progname(argv[0]);
465 
466  /* Set default parameter values */
467  param.pg_user = NULL;
468  param.pg_prompt = TRI_DEFAULT;
469  param.pg_host = NULL;
470  param.pg_port = NULL;
471  param.progname = progname;
472  param.verbose = 0;
473  param.dry_run = 0;
474  param.transaction_limit = 1000;
475 
476  /* Process command-line arguments */
477  if (argc > 1)
478  {
479  if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
480  {
481  usage(progname);
482  exit(0);
483  }
484  if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
485  {
486  puts("vacuumlo (PostgreSQL) " PG_VERSION);
487  exit(0);
488  }
489  }
490 
491  while ((c = getopt_long(argc, argv, "h:l:np:U:vwW", long_options, &optindex)) != -1)
492  {
493  switch (c)
494  {
495  case '?':
496  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
497  exit(1);
498  case 'h':
499  param.pg_host = pg_strdup(optarg);
500  break;
501  case 'l':
502  param.transaction_limit = strtol(optarg, NULL, 10);
503  if (param.transaction_limit < 0)
504  {
505  pg_log_error("transaction limit must not be negative (0 disables)");
506  exit(1);
507  }
508  break;
509  case 'n':
510  param.dry_run = 1;
511  param.verbose = 1;
512  break;
513  case 'p':
514  port = strtol(optarg, NULL, 10);
515  if ((port < 1) || (port > 65535))
516  {
517  pg_log_error("invalid port number: %s", optarg);
518  exit(1);
519  }
520  param.pg_port = pg_strdup(optarg);
521  break;
522  case 'U':
523  param.pg_user = pg_strdup(optarg);
524  break;
525  case 'v':
526  param.verbose = 1;
527  break;
528  case 'w':
529  param.pg_prompt = TRI_NO;
530  break;
531  case 'W':
532  param.pg_prompt = TRI_YES;
533  break;
534  default:
535  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
536  exit(1);
537  }
538  }
539 
540  /* No database given? Show usage */
541  if (optind >= argc)
542  {
543  pg_log_error("missing required argument: database name");
544  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
545  exit(1);
546  }
547 
548  for (c = optind; c < argc; c++)
549  {
550  /* Work on selected database */
551  rc += (vacuumlo(argv[c], &param) != 0);
552  }
553 
554  return rc;
555 }
const char * progname
Definition: main.c:46
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
void pg_logging_init(const char *argv0)
Definition: logging.c:81
#define fprintf
Definition: port.h:221
#define required_argument
Definition: getopt_long.h:25
int optind
Definition: getopt.c:50
char * c
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
static int port
Definition: pg_regress.c:92
#define no_argument
Definition: getopt_long.h:24
static int vacuumlo(const char *database, const struct _param *param)
Definition: vacuumlo.c:62
char * optarg
Definition: getopt.c:52
static void usage(const char *progname)
Definition: vacuumlo.c:417
#define _(x)
Definition: elog.c:89

◆ usage()

static void usage ( const char *  progname)
static

Definition at line 417 of file vacuumlo.c.

References printf.

Referenced by exec_command_slash_command_help(), main(), parse_psql_options(), and pg_notification_queue_usage().

418 {
419  printf("%s removes unreferenced large objects from databases.\n\n", progname);
420  printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
421  printf("Options:\n");
422  printf(" -l, --limit=LIMIT commit after removing each LIMIT large objects\n");
423  printf(" -n, --dry-run don't remove large objects, just show what would be done\n");
424  printf(" -v, --verbose write a lot of progress messages\n");
425  printf(" -V, --version output version information, then exit\n");
426  printf(" -?, --help show this help, then exit\n");
427  printf("\nConnection options:\n");
428  printf(" -h, --host=HOSTNAME database server host or socket directory\n");
429  printf(" -p, --port=PORT database server port\n");
430  printf(" -U, --username=USERNAME user name to connect as\n");
431  printf(" -w, --no-password never prompt for password\n");
432  printf(" -W, --password force password prompt\n");
433  printf("\n");
434  printf("Report bugs to <%s>.\n", PACKAGE_BUGREPORT);
435  printf("%s home page: <%s>\n", PACKAGE_NAME, PACKAGE_URL);
436 }
const char * progname
Definition: main.c:46
#define printf(...)
Definition: port.h:223

◆ vacuumlo()

static int vacuumlo ( const char *  database,
const struct _param param 
)
static

Definition at line 62 of file vacuumlo.c.

References ALWAYS_SECURE_SEARCH_PATH_SQL, atooid, buf, BUFSIZE, conn, CONNECTION_BAD, CppAsString2, _param::dry_run, fprintf, i, lo_unlink(), PARAMS_ARRAY_SIZE, password, _param::pg_host, pg_log_error, _param::pg_port, _param::pg_prompt, _param::pg_user, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PQclear(), PQconnectdbParams(), PQconnectionNeedsPassword(), PQerrorMessage(), PQescapeIdentifier(), PQexec(), PQfinish(), PQfreemem(), PQgetvalue(), PQntuples(), PQresultStatus(), PQserverVersion(), PQstatus(), PQTRANS_INERROR, PQtransactionStatus(), _param::progname, simple_prompt(), snprintf, generate_unaccent_rules::stdout, success, _param::transaction_limit, TRI_NO, TRI_YES, values, and _param::verbose.

Referenced by main().

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("%s", PQerrorMessage(conn));
128  PQfinish(conn);
129  return -1;
130  }
131 
132  if (param->verbose)
133  {
134  fprintf(stdout, "Connected to database \"%s\"\n", database);
135  if (param->dry_run)
136  fprintf(stdout, "Test run: no large objects will be removed!\n");
137  }
138 
140  if (PQresultStatus(res) != PGRES_TUPLES_OK)
141  {
142  pg_log_error("failed to set search_path: %s", PQerrorMessage(conn));
143  PQclear(res);
144  PQfinish(conn);
145  return -1;
146  }
147  PQclear(res);
148 
149  /*
150  * First we create and populate the LO temp table
151  */
152  buf[0] = '\0';
153  strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
154  if (PQserverVersion(conn) >= 90000)
155  strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
156  else
157  strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
158  res = PQexec(conn, buf);
159  if (PQresultStatus(res) != PGRES_COMMAND_OK)
160  {
161  pg_log_error("failed to create temp table: %s", PQerrorMessage(conn));
162  PQclear(res);
163  PQfinish(conn);
164  return -1;
165  }
166  PQclear(res);
167 
168  /*
169  * Analyze the temp table so that planner will generate decent plans for
170  * the DELETEs below.
171  */
172  buf[0] = '\0';
173  strcat(buf, "ANALYZE vacuum_l");
174  res = PQexec(conn, buf);
175  if (PQresultStatus(res) != PGRES_COMMAND_OK)
176  {
177  pg_log_error("failed to vacuum temp table: %s", PQerrorMessage(conn));
178  PQclear(res);
179  PQfinish(conn);
180  return -1;
181  }
182  PQclear(res);
183 
184  /*
185  * Now find any candidate tables that have columns of type oid.
186  *
187  * NOTE: we ignore system tables and temp tables by the expedient of
188  * rejecting tables in schemas named 'pg_*'. In particular, the temp
189  * table formed above is ignored, and pg_largeobject will be too. If
190  * either of these were scanned, obviously we'd end up with nothing to
191  * delete...
192  */
193  buf[0] = '\0';
194  strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
195  strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
196  strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
197  strcat(buf, " AND a.attrelid = c.oid ");
198  strcat(buf, " AND a.atttypid = t.oid ");
199  strcat(buf, " AND c.relnamespace = s.oid ");
200  strcat(buf, " AND t.typname in ('oid', 'lo') ");
201  strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
202  strcat(buf, " AND s.nspname !~ '^pg_'");
203  res = PQexec(conn, buf);
204  if (PQresultStatus(res) != PGRES_TUPLES_OK)
205  {
206  pg_log_error("failed to find OID columns: %s", PQerrorMessage(conn));
207  PQclear(res);
208  PQfinish(conn);
209  return -1;
210  }
211 
212  for (i = 0; i < PQntuples(res); i++)
213  {
214  char *schema,
215  *table,
216  *field;
217 
218  schema = PQgetvalue(res, i, 0);
219  table = PQgetvalue(res, i, 1);
220  field = PQgetvalue(res, i, 2);
221 
222  if (param->verbose)
223  fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
224 
225  schema = PQescapeIdentifier(conn, schema, strlen(schema));
226  table = PQescapeIdentifier(conn, table, strlen(table));
227  field = PQescapeIdentifier(conn, field, strlen(field));
228 
229  if (!schema || !table || !field)
230  {
231  pg_log_error("%s", PQerrorMessage(conn));
232  PQclear(res);
233  PQfinish(conn);
234  if (schema != NULL)
235  PQfreemem(schema);
236  if (table != NULL)
237  PQfreemem(table);
238  if (field != NULL)
239  PQfreemem(field);
240  return -1;
241  }
242 
243  snprintf(buf, BUFSIZE,
244  "DELETE FROM vacuum_l "
245  "WHERE lo IN (SELECT %s FROM %s.%s)",
246  field, schema, table);
247  res2 = PQexec(conn, buf);
248  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
249  {
250  pg_log_error("failed to check %s in table %s.%s: %s",
251  field, schema, table, PQerrorMessage(conn));
252  PQclear(res2);
253  PQclear(res);
254  PQfinish(conn);
255  PQfreemem(schema);
256  PQfreemem(table);
257  PQfreemem(field);
258  return -1;
259  }
260  PQclear(res2);
261 
262  PQfreemem(schema);
263  PQfreemem(table);
264  PQfreemem(field);
265  }
266  PQclear(res);
267 
268  /*
269  * Now, those entries remaining in vacuum_l are orphans. Delete 'em.
270  *
271  * We don't want to run each delete as an individual transaction, because
272  * the commit overhead would be high. However, since 9.0 the backend will
273  * acquire a lock per deleted LO, so deleting too many LOs per transaction
274  * risks running out of room in the shared-memory lock table. Accordingly,
275  * we delete up to transaction_limit LOs per transaction.
276  */
277  res = PQexec(conn, "begin");
278  if (PQresultStatus(res) != PGRES_COMMAND_OK)
279  {
280  pg_log_error("failed to start transaction: %s", PQerrorMessage(conn));
281  PQclear(res);
282  PQfinish(conn);
283  return -1;
284  }
285  PQclear(res);
286 
287  buf[0] = '\0';
288  strcat(buf,
289  "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
290  res = PQexec(conn, buf);
291  if (PQresultStatus(res) != PGRES_COMMAND_OK)
292  {
293  pg_log_error("DECLARE CURSOR failed: %s", PQerrorMessage(conn));
294  PQclear(res);
295  PQfinish(conn);
296  return -1;
297  }
298  PQclear(res);
299 
300  snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
301  param->transaction_limit > 0 ? param->transaction_limit : 1000L);
302 
303  deleted = 0;
304 
305  do
306  {
307  res = PQexec(conn, buf);
308  if (PQresultStatus(res) != PGRES_TUPLES_OK)
309  {
310  pg_log_error("FETCH FORWARD failed: %s", PQerrorMessage(conn));
311  PQclear(res);
312  PQfinish(conn);
313  return -1;
314  }
315 
316  matched = PQntuples(res);
317  if (matched <= 0)
318  {
319  /* at end of resultset */
320  PQclear(res);
321  break;
322  }
323 
324  for (i = 0; i < matched; i++)
325  {
326  Oid lo = atooid(PQgetvalue(res, i, 0));
327 
328  if (param->verbose)
329  {
330  fprintf(stdout, "\rRemoving lo %6u ", lo);
331  fflush(stdout);
332  }
333 
334  if (param->dry_run == 0)
335  {
336  if (lo_unlink(conn, lo) < 0)
337  {
338  pg_log_error("failed to remove lo %u: %s", lo,
339  PQerrorMessage(conn));
341  {
342  success = false;
343  break; /* out of inner for-loop */
344  }
345  }
346  else
347  deleted++;
348  }
349  else
350  deleted++;
351 
352  if (param->transaction_limit > 0 &&
353  (deleted % param->transaction_limit) == 0)
354  {
355  res2 = PQexec(conn, "commit");
356  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
357  {
358  pg_log_error("failed to commit transaction: %s",
359  PQerrorMessage(conn));
360  PQclear(res2);
361  PQclear(res);
362  PQfinish(conn);
363  return -1;
364  }
365  PQclear(res2);
366  res2 = PQexec(conn, "begin");
367  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
368  {
369  pg_log_error("failed to start transaction: %s",
370  PQerrorMessage(conn));
371  PQclear(res2);
372  PQclear(res);
373  PQfinish(conn);
374  return -1;
375  }
376  PQclear(res2);
377  }
378  }
379 
380  PQclear(res);
381  } while (success);
382 
383  /*
384  * That's all folks!
385  */
386  res = PQexec(conn, "commit");
387  if (PQresultStatus(res) != PGRES_COMMAND_OK)
388  {
389  pg_log_error("failed to commit transaction: %s",
390  PQerrorMessage(conn));
391  PQclear(res);
392  PQfinish(conn);
393  return -1;
394  }
395  PQclear(res);
396 
397  PQfinish(conn);
398 
399  if (param->verbose)
400  {
401  if (param->dry_run)
402  fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
403  deleted, database);
404  else if (success)
405  fprintf(stdout,
406  "\rSuccessfully removed %ld large objects from database \"%s\".\n",
407  deleted, database);
408  else
409  fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
410  database, deleted, matched);
411  }
412 
413  return ((param->dry_run || success) ? 0 : -1);
414 }
const char * progname
Definition: vacuumlo.c:47
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6744
#define PARAMS_ARRAY_SIZE
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3642
int verbose
Definition: vacuumlo.c:48
#define pg_log_error(...)
Definition: logging.h:80
int lo_unlink(PGconn *conn, Oid lobjId)
Definition: fe-lobj.c:597
char * pg_port
Definition: vacuumlo.c:45
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4231
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:6734
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3248
#define fprintf
Definition: port.h:221
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:3178
PGconn * PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname)
Definition: fe-connect.c:657
int dry_run
Definition: vacuumlo.c:49
char * PQescapeIdentifier(PGconn *conn, const char *str, size_t len)
Definition: fe-exec.c:4075
PGconn * conn
Definition: streamutil.c:54
static char * buf
Definition: pg_test_fsync.c:68
static char * password
Definition: streamutil.c:53
#define atooid(x)
Definition: postgres_ext.h:42
#define BUFSIZE
Definition: vacuumlo.c:32
PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
Definition: fe-connect.c:6699
#define CppAsString2(x)
Definition: c.h:289
void PQclear(PGresult *res)
Definition: fe-exec.c:694
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:156
int PQconnectionNeedsPassword(const PGconn *conn)
Definition: fe-connect.c:6795
int i
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:2193
long transaction_limit
Definition: vacuumlo.c:50
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6691
static bool success
Definition: initdb.c:165
char * pg_user
Definition: vacuumlo.c:43
#define snprintf
Definition: port.h:217
void PQfreemem(void *ptr)
Definition: fe-exec.c:3796