1 /*-------------------------------------------------------------------------
4 * This removes orphaned large objects from a database.
6 * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.19 2002/12/10 01:57:16 momjian Exp $
13 *-------------------------------------------------------------------------
15 #include "postgres_fe.h"
25 #include "libpq/libpq-fs.h"
27 #define atooid(x) ((Oid) strtoul((x), NULL, 10))
46 int vacuumlo(char *, struct _param *);
47 char *simple_prompt(const char *prompt, int, int);
54 * Generalized function especially intended for reading in usernames and
55 * password interactively. Reads from /dev/tty or stdin/stderr.
57 * prompt: The prompt to print
58 * maxlen: How many characters to accept
59 * echo: Set to 0 if you want to hide what is entered (for passwords)
61 * Returns a malloc()'ed string with the input (w/o trailing newline).
63 static int prompt_state = 0;
66 simple_prompt(const char *prompt, int maxlen, int echo)
74 struct termios t_orig,
78 destination = (char *) malloc(maxlen + 2);
82 prompt_state = 1; /* disable SIGINT */
85 * Do not try to collapse these into one "w+" mode file. Doesn't work
86 * on some platforms (eg, HPUX 10.20).
88 termin = fopen("/dev/tty", "r");
89 termout = fopen("/dev/tty", "w");
90 if (!termin || !termout)
100 #ifdef HAVE_TERMIOS_H
103 tcgetattr(fileno(termin), &t);
106 tcsetattr(fileno(termin), TCSAFLUSH, &t);
112 fputs(prompt, termout);
116 if (fgets(destination, maxlen, termin) == NULL)
117 destination[0] = '\0';
119 length = strlen(destination);
120 if (length > 0 && destination[length - 1] != '\n')
122 /* eat rest of the line */
128 if (fgets(buf, sizeof(buf), termin) == NULL)
130 buflen = strlen(buf);
131 } while (buflen > 0 && buf[buflen - 1] != '\n');
134 if (length > 0 && destination[length - 1] == '\n')
135 /* remove trailing newline */
136 destination[length - 1] = '\0';
138 #ifdef HAVE_TERMIOS_H
141 tcsetattr(fileno(termin), TCSAFLUSH, &t_orig);
142 fputs("\n", termout);
153 prompt_state = 0; /* SIGINT okay again */
161 * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
164 vacuumlo(char *database, struct _param * param)
173 char *password = NULL;
175 if (param->pg_prompt)
177 password = simple_prompt("Password: ", 32, 0);
180 fprintf(stderr, "failed to get password\n");
185 conn = PQsetdbLogin(param->pg_host,
194 /* check to see that the backend connection was successfully made */
195 if (PQstatus(conn) == CONNECTION_BAD)
197 fprintf(stderr, "Connection to database '%s' failed:\n", database);
198 fprintf(stderr, "%s", PQerrorMessage(conn));
205 fprintf(stdout, "Connected to %s\n", database);
207 fprintf(stdout, "Test run: no large objects will be removed!\n");
210 res = PQexec(conn, "SET search_path = public");
211 if (PQresultStatus(res) != PGRES_COMMAND_OK)
213 fprintf(stderr, "Failed to set search_path on:\n");
214 fprintf(stderr, "%s", PQerrorMessage(conn));
221 res = PQexec(conn, "SET autocommit TO 'on'");
222 if (PQresultStatus(res) != PGRES_COMMAND_OK)
224 fprintf(stderr, "Failed to set autocommit on:\n");
225 fprintf(stderr, "%s", PQerrorMessage(conn));
233 * First we create and populate the LO temp table
236 strcat(buf, "SELECT DISTINCT loid AS lo ");
237 strcat(buf, "INTO TEMP TABLE vacuum_l ");
238 strcat(buf, "FROM pg_largeobject ");
239 res = PQexec(conn, buf);
240 if (PQresultStatus(res) != PGRES_COMMAND_OK)
242 fprintf(stderr, "Failed to create temp table:\n");
243 fprintf(stderr, "%s", PQerrorMessage(conn));
251 * Vacuum the temp table so that planner will generate decent plans
252 * for the DELETEs below.
255 strcat(buf, "VACUUM ANALYZE vacuum_l ");
256 res = PQexec(conn, buf);
257 if (PQresultStatus(res) != PGRES_COMMAND_OK)
259 fprintf(stderr, "Failed to vacuum temp table:\n");
260 fprintf(stderr, "%s", PQerrorMessage(conn));
268 * Now find any candidate tables who have columns of type oid.
270 * NOTE: the temp table formed above is ignored, because its real table
271 * name will be pg_something. Also, pg_largeobject will be ignored.
272 * If either of these were scanned, obviously we'd end up with nothing
275 * NOTE: the system oid column is ignored, as it has attnum < 1. This
276 * shouldn't matter for correctness, but it saves time.
279 strcat(buf, "SELECT c.relname, a.attname ");
280 strcat(buf, "FROM pg_class c, pg_attribute a, pg_type t ");
281 strcat(buf, "WHERE a.attnum > 0 ");
282 strcat(buf, " AND a.attrelid = c.oid ");
283 strcat(buf, " AND a.atttypid = t.oid ");
284 strcat(buf, " AND t.typname in ('oid', 'lo') ");
285 strcat(buf, " AND c.relkind = 'r'");
286 strcat(buf, " AND c.relname NOT LIKE 'pg_%'");
287 strcat(buf, " AND c.relname != 'vacuum_l'");
288 res = PQexec(conn, buf);
289 if (PQresultStatus(res) != PGRES_TUPLES_OK)
291 fprintf(stderr, "Failed to find OID columns:\n");
292 fprintf(stderr, "%s", PQerrorMessage(conn));
298 for (i = 0; i < PQntuples(res); i++)
303 table = PQgetvalue(res, i, 0);
304 field = PQgetvalue(res, i, 1);
307 fprintf(stdout, "Checking %s in %s\n", field, table);
310 * We use a DELETE with implicit join for efficiency. This is a
311 * Postgres-ism and not portable to other DBMSs, but then this
312 * whole program is a Postgres-ism.
314 snprintf(buf, BUFSIZE, "DELETE FROM vacuum_l WHERE lo = \"%s\".\"%s\" ",
316 res2 = PQexec(conn, buf);
317 if (PQresultStatus(res2) != PGRES_COMMAND_OK)
319 fprintf(stderr, "Failed to check %s in table %s:\n",
321 fprintf(stderr, "%s", PQerrorMessage(conn));
332 * Run the actual deletes in a single transaction. Note that this
333 * would be a bad idea in pre-7.1 Postgres releases (since rolling
334 * back a table delete used to cause problems), but it should be safe
337 res = PQexec(conn, "begin");
341 * Finally, those entries remaining in vacuum_l are orphans.
344 strcat(buf, "SELECT lo ");
345 strcat(buf, "FROM vacuum_l");
346 res = PQexec(conn, buf);
347 if (PQresultStatus(res) != PGRES_TUPLES_OK)
349 fprintf(stderr, "Failed to read temp table:\n");
350 fprintf(stderr, "%s", PQerrorMessage(conn));
356 matched = PQntuples(res);
358 for (i = 0; i < matched; i++)
360 Oid lo = atooid(PQgetvalue(res, i, 0));
364 fprintf(stdout, "\rRemoving lo %6u ", lo);
368 if (param->dry_run == 0)
370 if (lo_unlink(conn, lo) < 0)
372 fprintf(stderr, "\nFailed to remove lo %u: ", lo);
373 fprintf(stderr, "%s", PQerrorMessage(conn));
386 res = PQexec(conn, "end");
392 fprintf(stdout, "\r%s %d large objects from %s.\n",
393 (param->dry_run ? "Would remove" : "Removed"), deleted, database);
401 fprintf(stdout, "vacuumlo removes unreferenced large objects from databases\n\n");
402 fprintf(stdout, "Usage:\n vacuumlo [options] dbname [dbnames...]\n\n");
403 fprintf(stdout, "Options:\n");
404 fprintf(stdout, " -v\t\tWrite a lot of output\n");
405 fprintf(stdout, " -n\t\tDon't remove any large object, just show what would be done\n");
406 fprintf(stdout, " -U username\tUsername to connect as\n");
407 fprintf(stdout, " -W\t\tPrompt for password\n");
408 fprintf(stdout, " -h hostname\tDatabase server host\n");
409 fprintf(stdout, " -p port\tDatabase server port\n\n");
414 main(int argc, char **argv)
421 /* Parameter handling */
422 param.pg_user = NULL;
424 param.pg_host = NULL;
431 c = getopt(argc, argv, "?h:U:p:vnW");
454 param.pg_user = strdup(optarg);
460 port = strtol(optarg, NULL, 10);
461 if ((port < 1) || (port > 65535))
463 fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0], optarg);
466 param.pg_port = strdup(optarg);
469 param.pg_host = strdup(optarg);
474 /* No database given? Show usage */
477 fprintf(stderr, "vacuumlo: missing required argument: database name\n");
478 fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
482 for (c = optind; c < argc; c++)
484 /* Work on selected database */
485 rc += (vacuumlo(argv[c], ¶m) != 0);