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 "fe_utils/connect.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 30 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 32 of file vacuumlo.c.

33 {
35  TRI_NO,
36  TRI_YES
37 };

Function Documentation

◆ main()

int main ( int  argc,
char **  argv 
)

Definition at line 454 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.

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

◆ usage()

static void usage ( const char *  progname)
static

Definition at line 432 of file vacuumlo.c.

Referenced by main().

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

◆ vacuumlo()

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

Definition at line 60 of file vacuumlo.c.

References ALWAYS_SECURE_SEARCH_PATH_SQL, 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, simple_prompt(), snprintf(), success, _param::transaction_limit, TRI_NO, TRI_YES, values, and _param::verbose.

Referenced by main().

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