1 /*-------------------------------------------------------------------------
2 *
3 * vacuumlo.c
4 * This removes orphaned large objects from a database.
5 *
6 * Portions Copyright (c) 1996-2021, 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
34 enum trivalue
35 {
36 TRI_DEFAULT,
37 TRI_NO,
38 TRI_YES
39 };
40
41 struct _param
42 {
43 char *pg_user;
44 enum trivalue pg_prompt;
45 char *pg_port;
46 char *pg_host;
47 const char *progname;
48 int verbose;
49 int dry_run;
50 long transaction_limit;
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
vacuumlo(const char * database,const struct _param * param)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 &&
114 PQconnectionNeedsPassword(conn) &&
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
139 res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
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));
340 if (PQtransactionStatus(conn) == PQTRANS_INERROR)
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 }
415
416 static void
usage(const char * progname)417 usage(const char *progname)
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 }
437
438
439 int
main(int argc,char ** argv)440 main(int argc, char **argv)
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], ¶m) != 0);
552 }
553
554 return rc;
555 }
556