vacuumlo.c 10.4 KB
Newer Older
1 2 3 4 5
/*-------------------------------------------------------------------------
 *
 * vacuumlo.c
 *	  This removes orphaned large objects from a database.
 *
Bruce Momjian's avatar
Bruce Momjian committed
6
 * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
Bruce Momjian's avatar
Add:  
Bruce Momjian committed
7
 * Portions Copyright (c) 1994, Regents of the University of California
8 9 10
 *
 *
 * IDENTIFICATION
11
 *	  $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.18 2002/12/03 07:12:18 tgl Exp $
12 13 14
 *
 *-------------------------------------------------------------------------
 */
Tom Lane's avatar
Tom Lane committed
15
#include "postgres_fe.h"
16

17 18 19
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
Tom Lane's avatar
Tom Lane committed
20 21 22
#ifdef HAVE_TERMIOS_H
#include <termios.h>
#endif
23 24 25 26

#include "libpq-fe.h"
#include "libpq/libpq-fs.h"

Tom Lane's avatar
Tom Lane committed
27 28
#define atooid(x)  ((Oid) strtoul((x), NULL, 10))

29 30
#define BUFSIZE			1024

31
extern char *optarg;
Bruce Momjian's avatar
Bruce Momjian committed
32 33 34 35 36 37 38 39 40 41 42 43
extern int	optind,
			opterr,
			optopt;

struct _param
{
	char	   *pg_user;
	int			pg_prompt;
	char	   *pg_port;
	char	   *pg_host;
	int			verbose;
	int			dry_run;
44 45
};

Bruce Momjian's avatar
Bruce Momjian committed
46 47 48
int			vacuumlo(char *, struct _param *);
char	   *simple_prompt(const char *prompt, int, int);
void		usage(void);
49 50 51 52 53 54 55 56 57 58 59 60 61 62


/*
 * simple_prompt
 *
 * Generalized function especially intended for reading in usernames and
 * password interactively. Reads from /dev/tty or stdin/stderr.
 *
 * prompt:		The prompt to print
 * maxlen:		How many characters to accept
 * echo:		Set to 0 if you want to hide what is entered (for passwords)
 *
 * Returns a malloc()'ed string with the input (w/o trailing newline).
 */
Bruce Momjian's avatar
Bruce Momjian committed
63
static int	prompt_state = 0;
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

char *
simple_prompt(const char *prompt, int maxlen, int echo)
{
	int			length;
	char	   *destination;
	FILE	   *termin,
			   *termout;

#ifdef HAVE_TERMIOS_H
	struct termios t_orig,
				t;
#endif

	destination = (char *) malloc(maxlen + 2);
	if (!destination)
		return NULL;

Bruce Momjian's avatar
Bruce Momjian committed
82
	prompt_state = 1;			/* disable SIGINT */
83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

	/*
	 * Do not try to collapse these into one "w+" mode file. Doesn't work
	 * on some platforms (eg, HPUX 10.20).
	 */
	termin = fopen("/dev/tty", "r");
	termout = fopen("/dev/tty", "w");
	if (!termin || !termout)
	{
		if (termin)
			fclose(termin);
		if (termout)
			fclose(termout);
		termin = stdin;
		termout = stderr;
	}

#ifdef HAVE_TERMIOS_H
	if (!echo)
	{
		tcgetattr(fileno(termin), &t);
		t_orig = t;
		t.c_lflag &= ~ECHO;
		tcsetattr(fileno(termin), TCSAFLUSH, &t);
	}
#endif

	if (prompt)
	{
		fputs(prompt, termout);
		fflush(termout);
	}

	if (fgets(destination, maxlen, termin) == NULL)
		destination[0] = '\0';

	length = strlen(destination);
	if (length > 0 && destination[length - 1] != '\n')
	{
		/* eat rest of the line */
		char		buf[128];
		int			buflen;

		do
		{
			if (fgets(buf, sizeof(buf), termin) == NULL)
				break;
			buflen = strlen(buf);
		} while (buflen > 0 && buf[buflen - 1] != '\n');
	}

	if (length > 0 && destination[length - 1] == '\n')
		/* remove trailing newline */
		destination[length - 1] = '\0';

#ifdef HAVE_TERMIOS_H
	if (!echo)
	{
		tcsetattr(fileno(termin), TCSAFLUSH, &t_orig);
		fputs("\n", termout);
		fflush(termout);
	}
#endif

	if (termin != stdin)
	{
		fclose(termin);
		fclose(termout);
	}

Bruce Momjian's avatar
Bruce Momjian committed
153
	prompt_state = 0;			/* SIGINT okay again */
154 155 156 157

	return destination;
}

158 159 160


/*
Tom Lane's avatar
Tom Lane committed
161
 * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
162
 */
Bruce Momjian's avatar
Bruce Momjian committed
163
int
Bruce Momjian's avatar
Bruce Momjian committed
164
vacuumlo(char *database, struct _param * param)
165
{
Bruce Momjian's avatar
Bruce Momjian committed
166 167
	PGconn	   *conn;
	PGresult   *res,
Bruce Momjian's avatar
Bruce Momjian committed
168
			   *res2;
Bruce Momjian's avatar
Bruce Momjian committed
169
	char		buf[BUFSIZE];
Bruce Momjian's avatar
Bruce Momjian committed
170 171 172 173 174 175 176 177 178 179 180 181
	int			matched;
	int			deleted;
	int			i;
	char	   *password = NULL;

	if (param->pg_prompt)
	{
		password = simple_prompt("Password: ", 32, 0);
		if (!password)
		{
			fprintf(stderr, "failed to get password\n");
			exit(1);
182 183 184
		}
	}

Bruce Momjian's avatar
Bruce Momjian committed
185 186 187 188 189 190 191 192
	conn = PQsetdbLogin(param->pg_host,
						param->pg_port,
						NULL,
						NULL,
						database,
						param->pg_user,
						password
		);
Bruce Momjian's avatar
Bruce Momjian committed
193 194 195

	/* check to see that the backend connection was successfully made */
	if (PQstatus(conn) == CONNECTION_BAD)
196
	{
Tom Lane's avatar
Tom Lane committed
197
		fprintf(stderr, "Connection to database '%s' failed:\n", database);
Bruce Momjian's avatar
Bruce Momjian committed
198
		fprintf(stderr, "%s", PQerrorMessage(conn));
Tom Lane's avatar
Tom Lane committed
199
		PQfinish(conn);
Bruce Momjian's avatar
Bruce Momjian committed
200
		return -1;
201
	}
Bruce Momjian's avatar
Bruce Momjian committed
202

Bruce Momjian's avatar
Bruce Momjian committed
203 204
	if (param->verbose)
	{
Bruce Momjian's avatar
Bruce Momjian committed
205
		fprintf(stdout, "Connected to %s\n", database);
Bruce Momjian's avatar
Bruce Momjian committed
206 207
		if (param->dry_run)
			fprintf(stdout, "Test run: no large objects will be removed!\n");
208
	}
Bruce Momjian's avatar
Bruce Momjian committed
209

210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
	res = PQexec(conn, "SET search_path = public");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to set search_path on:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	res = PQexec(conn, "SET autocommit TO 'on'");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to set autocommit on:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

Bruce Momjian's avatar
Bruce Momjian committed
232
	/*
Tom Lane's avatar
Tom Lane committed
233
	 * First we create and populate the LO temp table
Bruce Momjian's avatar
Bruce Momjian committed
234 235
	 */
	buf[0] = '\0';
236
	strcat(buf, "SELECT DISTINCT loid AS lo ");
Bruce Momjian's avatar
Bruce Momjian committed
237
	strcat(buf, "INTO TEMP TABLE vacuum_l ");
238
	strcat(buf, "FROM pg_largeobject ");
Tom Lane's avatar
Tom Lane committed
239 240 241 242 243 244 245 246 247 248
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to create temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);
249

Tom Lane's avatar
Tom Lane committed
250 251 252 253 254 255 256 257
	/*
	 * Vacuum the temp table so that planner will generate decent plans
	 * for the DELETEs below.
	 */
	buf[0] = '\0';
	strcat(buf, "VACUUM ANALYZE vacuum_l ");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
258
	{
Tom Lane's avatar
Tom Lane committed
259 260 261
		fprintf(stderr, "Failed to vacuum temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
262 263
		PQfinish(conn);
		return -1;
Bruce Momjian's avatar
Bruce Momjian committed
264 265 266 267
	}
	PQclear(res);

	/*
Tom Lane's avatar
Tom Lane committed
268 269
	 * Now find any candidate tables who have columns of type oid.
	 *
270 271 272 273
	 * NOTE: the temp table formed above is ignored, because its real table
	 * name will be pg_something.  Also, pg_largeobject will be ignored.
	 * If either of these were scanned, obviously we'd end up with nothing
	 * to delete...
Tom Lane's avatar
Tom Lane committed
274
	 *
275 276
	 * NOTE: the system oid column is ignored, as it has attnum < 1. This
	 * shouldn't matter for correctness, but it saves time.
Bruce Momjian's avatar
Bruce Momjian committed
277 278 279 280 281 282 283
	 */
	buf[0] = '\0';
	strcat(buf, "SELECT c.relname, a.attname ");
	strcat(buf, "FROM pg_class c, pg_attribute a, pg_type t ");
	strcat(buf, "WHERE a.attnum > 0 ");
	strcat(buf, "      AND a.attrelid = c.oid ");
	strcat(buf, "      AND a.atttypid = t.oid ");
284
	strcat(buf, "      AND t.typname in ('oid', 'lo') ");
Tom Lane's avatar
Tom Lane committed
285
	strcat(buf, "      AND c.relkind = 'r'");
Bruce Momjian's avatar
Bruce Momjian committed
286
	strcat(buf, "      AND c.relname NOT LIKE 'pg_%'");
Tom Lane's avatar
Tom Lane committed
287 288
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
Bruce Momjian's avatar
Bruce Momjian committed
289
	{
Tom Lane's avatar
Tom Lane committed
290 291 292
		fprintf(stderr, "Failed to find OID columns:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
293 294
		PQfinish(conn);
		return -1;
Bruce Momjian's avatar
Bruce Momjian committed
295
	}
Tom Lane's avatar
Tom Lane committed
296

Bruce Momjian's avatar
Bruce Momjian committed
297 298 299 300 301 302 303 304
	for (i = 0; i < PQntuples(res); i++)
	{
		char	   *table,
				   *field;

		table = PQgetvalue(res, i, 0);
		field = PQgetvalue(res, i, 1);

305
		if (param->verbose)
Tom Lane's avatar
Tom Lane committed
306 307 308
			fprintf(stdout, "Checking %s in %s\n", field, table);

		/*
309 310 311
		 * We use a DELETE with implicit join for efficiency.  This is a
		 * Postgres-ism and not portable to other DBMSs, but then this
		 * whole program is a Postgres-ism.
Tom Lane's avatar
Tom Lane committed
312
		 */
313
		snprintf(buf, BUFSIZE, "DELETE FROM vacuum_l WHERE lo = \"%s\".\"%s\" ",
Bruce Momjian's avatar
Bruce Momjian committed
314
				 table, field);
Tom Lane's avatar
Tom Lane committed
315
		res2 = PQexec(conn, buf);
Bruce Momjian's avatar
Bruce Momjian committed
316 317
		if (PQresultStatus(res2) != PGRES_COMMAND_OK)
		{
Tom Lane's avatar
Tom Lane committed
318 319 320
			fprintf(stderr, "Failed to check %s in table %s:\n",
					field, table);
			fprintf(stderr, "%s", PQerrorMessage(conn));
Bruce Momjian's avatar
Bruce Momjian committed
321 322 323 324 325 326
			PQclear(res2);
			PQclear(res);
			PQfinish(conn);
			return -1;
		}
		PQclear(res2);
327
	}
Bruce Momjian's avatar
Bruce Momjian committed
328 329
	PQclear(res);

Tom Lane's avatar
Tom Lane committed
330
	/*
331
	 * Run the actual deletes in a single transaction.	Note that this
Tom Lane's avatar
Tom Lane committed
332
	 * would be a bad idea in pre-7.1 Postgres releases (since rolling
333 334
	 * back a table delete used to cause problems), but it should be safe
	 * now.
Tom Lane's avatar
Tom Lane committed
335
	 */
Bruce Momjian's avatar
Bruce Momjian committed
336 337 338 339 340 341 342 343 344
	res = PQexec(conn, "begin");
	PQclear(res);

	/*
	 * Finally, those entries remaining in vacuum_l are orphans.
	 */
	buf[0] = '\0';
	strcat(buf, "SELECT lo ");
	strcat(buf, "FROM vacuum_l");
Tom Lane's avatar
Tom Lane committed
345 346
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
347
	{
Tom Lane's avatar
Tom Lane committed
348 349 350
		fprintf(stderr, "Failed to read temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
Bruce Momjian's avatar
Bruce Momjian committed
351 352 353
		PQfinish(conn);
		return -1;
	}
Tom Lane's avatar
Tom Lane committed
354

Bruce Momjian's avatar
Bruce Momjian committed
355
	matched = PQntuples(res);
Tom Lane's avatar
Tom Lane committed
356
	deleted = 0;
Bruce Momjian's avatar
Bruce Momjian committed
357 358
	for (i = 0; i < matched; i++)
	{
Tom Lane's avatar
Tom Lane committed
359
		Oid			lo = atooid(PQgetvalue(res, i, 0));
Bruce Momjian's avatar
Bruce Momjian committed
360

361
		if (param->verbose)
Bruce Momjian's avatar
Bruce Momjian committed
362
		{
Tom Lane's avatar
Tom Lane committed
363
			fprintf(stdout, "\rRemoving lo %6u   ", lo);
Bruce Momjian's avatar
Bruce Momjian committed
364 365 366
			fflush(stdout);
		}

Bruce Momjian's avatar
Bruce Momjian committed
367 368 369 370 371 372 373 374 375 376 377 378
		if (param->dry_run == 0)
		{
			if (lo_unlink(conn, lo) < 0)
			{
				fprintf(stderr, "\nFailed to remove lo %u: ", lo);
				fprintf(stderr, "%s", PQerrorMessage(conn));
			}
			else
				deleted++;
		}
		else
			deleted++;
379
	}
Bruce Momjian's avatar
Bruce Momjian committed
380 381 382 383 384 385 386
	PQclear(res);

	/*
	 * That's all folks!
	 */
	res = PQexec(conn, "end");
	PQclear(res);
Tom Lane's avatar
Tom Lane committed
387

Bruce Momjian's avatar
Bruce Momjian committed
388 389
	PQfinish(conn);

390 391
	if (param->verbose)
		fprintf(stdout, "\r%s %d large objects from %s.\n",
Bruce Momjian's avatar
Bruce Momjian committed
392
		(param->dry_run ? "Would remove" : "Removed"), deleted, database);
Bruce Momjian's avatar
Bruce Momjian committed
393 394

	return 0;
395 396
}

397
void
Bruce Momjian's avatar
Bruce Momjian committed
398 399 400 401 402
usage(void)
{
	fprintf(stdout, "vacuumlo removes unreferenced large objects from databases\n\n");
	fprintf(stdout, "Usage:\n  vacuumlo [options] dbname [dbnames...]\n\n");
	fprintf(stdout, "Options:\n");
403 404
	fprintf(stdout, "  -v\t\tWrite a lot of output\n");
	fprintf(stdout, "  -n\t\tDon't remove any large object, just show what would be done\n");
Bruce Momjian's avatar
Bruce Momjian committed
405 406 407 408
	fprintf(stdout, "  -U username\tUsername to connect as\n");
	fprintf(stdout, "  -W\t\tPrompt for password\n");
	fprintf(stdout, "  -h hostname\tDatabase server host\n");
	fprintf(stdout, "  -p port\tDatabase server port\n\n");
409 410 411
}


412 413 414
int
main(int argc, char **argv)
{
Bruce Momjian's avatar
Bruce Momjian committed
415
	int			rc = 0;
Bruce Momjian's avatar
Bruce Momjian committed
416 417 418
	struct _param param;
	int			c;
	int			port;
Bruce Momjian's avatar
Bruce Momjian committed
419

420
	/* Parameter handling */
Bruce Momjian's avatar
Bruce Momjian committed
421
	param.pg_user = NULL;
422 423 424
	param.pg_prompt = 0;
	param.pg_host = NULL;
	param.pg_port = 0;
Bruce Momjian's avatar
Bruce Momjian committed
425
	param.verbose = 0;
426 427
	param.dry_run = 0;

Bruce Momjian's avatar
Bruce Momjian committed
428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470
	while (1)
	{
		c = getopt(argc, argv, "?h:U:p:vnW");
		if (c == -1)
			break;

		switch (c)
		{
			case '?':
				if (optopt == '?')
				{
					usage();
					exit(0);
				}
				exit(1);
			case ':':
				exit(1);
			case 'v':
				param.verbose = 1;
				break;
			case 'n':
				param.dry_run = 1;
				param.verbose = 1;
				break;
			case 'U':
				param.pg_user = strdup(optarg);
				break;
			case 'W':
				param.pg_prompt = 1;
				break;
			case 'p':
				port = strtol(optarg, NULL, 10);
				if ((port < 1) || (port > 65535))
				{
					fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0], optarg);
					exit(1);
				}
				param.pg_port = strdup(optarg);
				break;
			case 'h':
				param.pg_host = strdup(optarg);
				break;
		}
471
	}
Bruce Momjian's avatar
Bruce Momjian committed
472

473
	/* No database given? Show usage */
474
	if (optind >= argc)
Bruce Momjian's avatar
Bruce Momjian committed
475 476 477 478
	{
		fprintf(stderr, "vacuumlo: missing required argument: database name\n");
		fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
		exit(1);
479
	}
Bruce Momjian's avatar
Bruce Momjian committed
480

Bruce Momjian's avatar
Bruce Momjian committed
481 482 483 484
	for (c = optind; c < argc; c++)
	{
		/* Work on selected database */
		rc += (vacuumlo(argv[c], &param) != 0);
Bruce Momjian's avatar
Bruce Momjian committed
485 486 487
	}

	return rc;
488
}