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.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 29 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 31 of file vacuumlo.c.

32 {
34  TRI_NO,
35  TRI_YES
36 };

Function Documentation

◆ main()

int main ( int  argc,
char **  argv 
)

Definition at line 456 of file vacuumlo.c.

References _, _param::dry_run, get_progname(), getopt(), optarg, optind, _param::pg_host, _param::pg_port, _param::pg_prompt, pg_strdup(), _param::pg_user, port, _param::progname, _param::transaction_limit, TRI_DEFAULT, TRI_NO, TRI_YES, usage(), vacuumlo(), and _param::verbose.

457 {
458  int rc = 0;
459  struct _param param;
460  int c;
461  int port;
462  const char *progname;
463 
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 (1)
492  {
493  c = getopt(argc, argv, "h:l:U:p:vnwW");
494  if (c == -1)
495  break;
496 
497  switch (c)
498  {
499  case '?':
500  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
501  exit(1);
502  case ':':
503  exit(1);
504  case 'v':
505  param.verbose = 1;
506  break;
507  case 'n':
508  param.dry_run = 1;
509  param.verbose = 1;
510  break;
511  case 'l':
512  param.transaction_limit = strtol(optarg, NULL, 10);
513  if (param.transaction_limit < 0)
514  {
515  fprintf(stderr,
516  "%s: transaction limit must not be negative (0 disables)\n",
517  progname);
518  exit(1);
519  }
520  break;
521  case 'U':
522  param.pg_user = pg_strdup(optarg);
523  break;
524  case 'w':
525  param.pg_prompt = TRI_NO;
526  break;
527  case 'W':
528  param.pg_prompt = TRI_YES;
529  break;
530  case 'p':
531  port = strtol(optarg, NULL, 10);
532  if ((port < 1) || (port > 65535))
533  {
534  fprintf(stderr, "%s: invalid port number: %s\n", progname, optarg);
535  exit(1);
536  }
537  param.pg_port = pg_strdup(optarg);
538  break;
539  case 'h':
540  param.pg_host = pg_strdup(optarg);
541  break;
542  }
543  }
544 
545  /* No database given? Show usage */
546  if (optind >= argc)
547  {
548  fprintf(stderr, "vacuumlo: missing required argument: database name\n");
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 }
const char * get_progname(const char *argv0)
Definition: path.c:453
const char * progname
Definition: pg_standby.c:37
int getopt(int nargc, char *const *nargv, const char *ostr)
Definition: getopt.c:72
int optind
Definition: getopt.c:51
char * c
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
static int port
Definition: pg_regress.c:90
static int vacuumlo(const char *database, const struct _param *param)
Definition: vacuumlo.c:59
char * optarg
Definition: getopt.c:53
static void usage(const char *progname)
Definition: vacuumlo.c:434
#define _(x)
Definition: elog.c:84

◆ usage()

static void usage ( const char *  progname)
static

Definition at line 434 of file vacuumlo.c.

Referenced by main().

435 {
436  printf("%s removes unreferenced large objects from databases.\n\n", progname);
437  printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
438  printf("Options:\n");
439  printf(" -l LIMIT commit after removing each LIMIT large objects\n");
440  printf(" -n don't remove large objects, just show what would be done\n");
441  printf(" -v write a lot of progress messages\n");
442  printf(" -V, --version output version information, then exit\n");
443  printf(" -?, --help show this help, then exit\n");
444  printf("\nConnection options:\n");
445  printf(" -h HOSTNAME database server host or socket directory\n");
446  printf(" -p PORT database server port\n");
447  printf(" -U USERNAME user name to connect as\n");
448  printf(" -w never prompt for password\n");
449  printf(" -W force password prompt\n");
450  printf("\n");
451  printf("Report bugs to <pgsql-bugs@postgresql.org>.\n");
452 }
const char * progname
Definition: pg_standby.c:37

◆ vacuumlo()

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

Definition at line 59 of file vacuumlo.c.

References atooid, buf, BUFSIZE, conn, CONNECTION_BAD, CppAsString2, _param::dry_run, have_password, i, lo_unlink(), PARAMS_ARRAY_SIZE, password, _param::pg_host, _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, RELKIND_MATVIEW, RELKIND_RELATION, simple_prompt(), snprintf(), success, _param::transaction_limit, TRI_NO, TRI_YES, values, and _param::verbose.

Referenced by main().

60 {
61  PGconn *conn;
62  PGresult *res,
63  *res2;
64  char buf[BUFSIZE];
65  long matched;
66  long deleted;
67  int i;
68  bool new_pass;
69  bool success = true;
70  static bool have_password = false;
71  static char password[100];
72 
73  /* Note: password can be carried over from a previous call */
74  if (param->pg_prompt == TRI_YES && !have_password)
75  {
76  simple_prompt("Password: ", password, sizeof(password), false);
77  have_password = true;
78  }
79 
80  /*
81  * Start the connection. Loop until we have a password if requested by
82  * backend.
83  */
84  do
85  {
86 #define PARAMS_ARRAY_SIZE 7
87 
88  const char *keywords[PARAMS_ARRAY_SIZE];
89  const char *values[PARAMS_ARRAY_SIZE];
90 
91  keywords[0] = "host";
92  values[0] = param->pg_host;
93  keywords[1] = "port";
94  values[1] = param->pg_port;
95  keywords[2] = "user";
96  values[2] = param->pg_user;
97  keywords[3] = "password";
98  values[3] = have_password ? password : NULL;
99  keywords[4] = "dbname";
100  values[4] = database;
101  keywords[5] = "fallback_application_name";
102  values[5] = param->progname;
103  keywords[6] = NULL;
104  values[6] = NULL;
105 
106  new_pass = false;
107  conn = PQconnectdbParams(keywords, values, true);
108  if (!conn)
109  {
110  fprintf(stderr, "Connection to database \"%s\" failed\n",
111  database);
112  return -1;
113  }
114 
115  if (PQstatus(conn) == CONNECTION_BAD &&
117  !have_password &&
118  param->pg_prompt != TRI_NO)
119  {
120  PQfinish(conn);
121  simple_prompt("Password: ", password, sizeof(password), false);
122  have_password = true;
123  new_pass = true;
124  }
125  } while (new_pass);
126 
127  /* check to see that the backend connection was successfully made */
128  if (PQstatus(conn) == CONNECTION_BAD)
129  {
130  fprintf(stderr, "Connection to database \"%s\" failed:\n%s",
131  database, PQerrorMessage(conn));
132  PQfinish(conn);
133  return -1;
134  }
135 
136  if (param->verbose)
137  {
138  fprintf(stdout, "Connected to database \"%s\"\n", database);
139  if (param->dry_run)
140  fprintf(stdout, "Test run: no large objects will be removed!\n");
141  }
142 
143  /*
144  * Don't get fooled by any non-system catalogs
145  */
146  res = PQexec(conn, "SET search_path = pg_catalog");
147  if (PQresultStatus(res) != PGRES_COMMAND_OK)
148  {
149  fprintf(stderr, "Failed to set search_path:\n");
150  fprintf(stderr, "%s", PQerrorMessage(conn));
151  PQclear(res);
152  PQfinish(conn);
153  return -1;
154  }
155  PQclear(res);
156 
157  /*
158  * First we create and populate the LO temp table
159  */
160  buf[0] = '\0';
161  strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
162  if (PQserverVersion(conn) >= 90000)
163  strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
164  else
165  strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
166  res = PQexec(conn, buf);
167  if (PQresultStatus(res) != PGRES_COMMAND_OK)
168  {
169  fprintf(stderr, "Failed to create temp table:\n");
170  fprintf(stderr, "%s", PQerrorMessage(conn));
171  PQclear(res);
172  PQfinish(conn);
173  return -1;
174  }
175  PQclear(res);
176 
177  /*
178  * Analyze the temp table so that planner will generate decent plans for
179  * the DELETEs below.
180  */
181  buf[0] = '\0';
182  strcat(buf, "ANALYZE vacuum_l");
183  res = PQexec(conn, buf);
184  if (PQresultStatus(res) != PGRES_COMMAND_OK)
185  {
186  fprintf(stderr, "Failed to vacuum temp table:\n");
187  fprintf(stderr, "%s", PQerrorMessage(conn));
188  PQclear(res);
189  PQfinish(conn);
190  return -1;
191  }
192  PQclear(res);
193 
194  /*
195  * Now find any candidate tables that have columns of type oid.
196  *
197  * NOTE: we ignore system tables and temp tables by the expedient of
198  * rejecting tables in schemas named 'pg_*'. In particular, the temp
199  * table formed above is ignored, and pg_largeobject will be too. If
200  * either of these were scanned, obviously we'd end up with nothing to
201  * delete...
202  *
203  * NOTE: the system oid column is ignored, as it has attnum < 1. This
204  * shouldn't matter for correctness, but it saves time.
205  */
206  buf[0] = '\0';
207  strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
208  strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
209  strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
210  strcat(buf, " AND a.attrelid = c.oid ");
211  strcat(buf, " AND a.atttypid = t.oid ");
212  strcat(buf, " AND c.relnamespace = s.oid ");
213  strcat(buf, " AND t.typname in ('oid', 'lo') ");
214  strcat(buf, " AND c.relkind in (" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) ")");
215  strcat(buf, " AND s.nspname !~ '^pg_'");
216  res = PQexec(conn, buf);
217  if (PQresultStatus(res) != PGRES_TUPLES_OK)
218  {
219  fprintf(stderr, "Failed to find OID columns:\n");
220  fprintf(stderr, "%s", PQerrorMessage(conn));
221  PQclear(res);
222  PQfinish(conn);
223  return -1;
224  }
225 
226  for (i = 0; i < PQntuples(res); i++)
227  {
228  char *schema,
229  *table,
230  *field;
231 
232  schema = PQgetvalue(res, i, 0);
233  table = PQgetvalue(res, i, 1);
234  field = PQgetvalue(res, i, 2);
235 
236  if (param->verbose)
237  fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
238 
239  schema = PQescapeIdentifier(conn, schema, strlen(schema));
240  table = PQescapeIdentifier(conn, table, strlen(table));
241  field = PQescapeIdentifier(conn, field, strlen(field));
242 
243  if (!schema || !table || !field)
244  {
245  fprintf(stderr, "%s", PQerrorMessage(conn));
246  PQclear(res);
247  PQfinish(conn);
248  if (schema != NULL)
249  PQfreemem(schema);
250  if (schema != NULL)
251  PQfreemem(table);
252  if (schema != NULL)
253  PQfreemem(field);
254  return -1;
255  }
256 
257  snprintf(buf, BUFSIZE,
258  "DELETE FROM vacuum_l "
259  "WHERE lo IN (SELECT %s FROM %s.%s)",
260  field, schema, table);
261  res2 = PQexec(conn, buf);
262  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
263  {
264  fprintf(stderr, "Failed to check %s in table %s.%s:\n",
265  field, schema, table);
266  fprintf(stderr, "%s", PQerrorMessage(conn));
267  PQclear(res2);
268  PQclear(res);
269  PQfinish(conn);
270  PQfreemem(schema);
271  PQfreemem(table);
272  PQfreemem(field);
273  return -1;
274  }
275  PQclear(res2);
276 
277  PQfreemem(schema);
278  PQfreemem(table);
279  PQfreemem(field);
280  }
281  PQclear(res);
282 
283  /*
284  * Now, those entries remaining in vacuum_l are orphans. Delete 'em.
285  *
286  * We don't want to run each delete as an individual transaction, because
287  * the commit overhead would be high. However, since 9.0 the backend will
288  * acquire a lock per deleted LO, so deleting too many LOs per transaction
289  * risks running out of room in the shared-memory lock table. Accordingly,
290  * we delete up to transaction_limit LOs per transaction.
291  */
292  res = PQexec(conn, "begin");
293  if (PQresultStatus(res) != PGRES_COMMAND_OK)
294  {
295  fprintf(stderr, "Failed to start transaction:\n");
296  fprintf(stderr, "%s", PQerrorMessage(conn));
297  PQclear(res);
298  PQfinish(conn);
299  return -1;
300  }
301  PQclear(res);
302 
303  buf[0] = '\0';
304  strcat(buf,
305  "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
306  res = PQexec(conn, buf);
307  if (PQresultStatus(res) != PGRES_COMMAND_OK)
308  {
309  fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
310  PQclear(res);
311  PQfinish(conn);
312  return -1;
313  }
314  PQclear(res);
315 
316  snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
317  param->transaction_limit > 0 ? param->transaction_limit : 1000L);
318 
319  deleted = 0;
320 
321  while (1)
322  {
323  res = PQexec(conn, buf);
324  if (PQresultStatus(res) != PGRES_TUPLES_OK)
325  {
326  fprintf(stderr, "FETCH FORWARD failed: %s", PQerrorMessage(conn));
327  PQclear(res);
328  PQfinish(conn);
329  return -1;
330  }
331 
332  matched = PQntuples(res);
333  if (matched <= 0)
334  {
335  /* at end of resultset */
336  PQclear(res);
337  break;
338  }
339 
340  for (i = 0; i < matched; i++)
341  {
342  Oid lo = atooid(PQgetvalue(res, i, 0));
343 
344  if (param->verbose)
345  {
346  fprintf(stdout, "\rRemoving lo %6u ", lo);
347  fflush(stdout);
348  }
349 
350  if (param->dry_run == 0)
351  {
352  if (lo_unlink(conn, lo) < 0)
353  {
354  fprintf(stderr, "\nFailed to remove lo %u: ", lo);
355  fprintf(stderr, "%s", PQerrorMessage(conn));
357  {
358  success = false;
359  PQclear(res);
360  break;
361  }
362  }
363  else
364  deleted++;
365  }
366  else
367  deleted++;
368 
369  if (param->transaction_limit > 0 &&
370  (deleted % param->transaction_limit) == 0)
371  {
372  res2 = PQexec(conn, "commit");
373  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
374  {
375  fprintf(stderr, "Failed to commit transaction:\n");
376  fprintf(stderr, "%s", PQerrorMessage(conn));
377  PQclear(res2);
378  PQclear(res);
379  PQfinish(conn);
380  return -1;
381  }
382  PQclear(res2);
383  res2 = PQexec(conn, "begin");
384  if (PQresultStatus(res2) != PGRES_COMMAND_OK)
385  {
386  fprintf(stderr, "Failed to start transaction:\n");
387  fprintf(stderr, "%s", PQerrorMessage(conn));
388  PQclear(res2);
389  PQclear(res);
390  PQfinish(conn);
391  return -1;
392  }
393  PQclear(res2);
394  }
395  }
396 
397  PQclear(res);
398  }
399 
400  /*
401  * That's all folks!
402  */
403  res = PQexec(conn, "commit");
404  if (PQresultStatus(res) != PGRES_COMMAND_OK)
405  {
406  fprintf(stderr, "Failed to commit transaction:\n");
407  fprintf(stderr, "%s", PQerrorMessage(conn));
408  PQclear(res);
409  PQfinish(conn);
410  return -1;
411  }
412  PQclear(res);
413 
414  PQfinish(conn);
415 
416  if (param->verbose)
417  {
418  if (param->dry_run)
419  fprintf(stdout, "\rWould remove %ld large objects from database \"%s\".\n",
420  deleted, database);
421  else if (success)
422  fprintf(stdout,
423  "\rSuccessfully removed %ld large objects from database \"%s\".\n",
424  deleted, database);
425  else
426  fprintf(stdout, "\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
427  database, deleted, matched);
428  }
429 
430  return ((param->dry_run || success) ? 0 : -1);
431 }
static char password[100]
Definition: streamutil.c:45
const char * progname
Definition: vacuumlo.c:44
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6106
#define PARAMS_ARRAY_SIZE
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3118
int verbose
Definition: vacuumlo.c:45
int lo_unlink(PGconn *conn, Oid lobjId)
Definition: fe-lobj.c:628
char * pg_port
Definition: vacuumlo.c:42
#define RELKIND_MATVIEW
Definition: pg_class.h:165
void PQfinish(PGconn *conn)
Definition: fe-connect.c:3629
int snprintf(char *str, size_t count, const char *fmt,...) pg_attribute_printf(3
unsigned int Oid
Definition: postgres_ext.h:31
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6096
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2724
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2647
PGconn * PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname)
Definition: fe-connect.c:529
int dry_run
Definition: vacuumlo.c:46
char * PQescapeIdentifier(PGconn *conn, const char *str, size_t len)
Definition: fe-exec.c:3525
PGconn * conn
Definition: streamutil.c:46
static bool success
Definition: pg_basebackup.c:99
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
#define atooid(x)
Definition: postgres_ext.h:42
#define BUFSIZE
Definition: vacuumlo.c:29
PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
Definition: fe-connect.c:6061
#define CppAsString2(x)
Definition: c.h:216
void PQclear(PGresult *res)
Definition: fe-exec.c:671
static bool have_password
Definition: streamutil.c:44
enum trivalue pg_prompt
Definition: vacuumlo.c:41
char * pg_host
Definition: vacuumlo.c:43
static Datum values[MAXATTR]
Definition: bootstrap.c:164
int PQconnectionNeedsPassword(const PGconn *conn)
Definition: fe-connect.c:6140
int i
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1897
long transaction_limit
Definition: vacuumlo.c:47
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6053
#define RELKIND_RELATION
Definition: pg_class.h:160
char * pg_user
Definition: vacuumlo.c:40
void PQfreemem(void *ptr)
Definition: fe-exec.c:3251