1
/*-------------------------------------------------------------------------
4
* This removes orphaned large objects from a database.
6
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
7
* Portions Copyright (c) 1994, Regents of the University of California
13
*-------------------------------------------------------------------------
15
#include "postgres_fe.h"
25
#include "libpq/libpq-fs.h"
27
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
46
enum trivalue pg_prompt;
53
int vacuumlo(char *, struct _param *);
54
void usage(const char *progname);
59
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
62
vacuumlo(char *database, struct _param * param)
71
static char *password = NULL;
74
if (param->pg_prompt == TRI_YES && password == NULL)
75
password = simple_prompt("Password: ", 100, false);
78
* Start the connection. Loop until we have a password if requested by
85
conn = PQsetdbLogin(param->pg_host,
94
fprintf(stderr, "Connection to database \"%s\" failed\n",
99
if (PQstatus(conn) == CONNECTION_BAD &&
100
PQconnectionNeedsPassword(conn) &&
102
param->pg_prompt != TRI_NO)
105
password = simple_prompt("Password: ", 100, false);
110
/* check to see that the backend connection was successfully made */
111
if (PQstatus(conn) == CONNECTION_BAD)
113
fprintf(stderr, "Connection to database \"%s\" failed:\n%s",
114
database, PQerrorMessage(conn));
121
fprintf(stdout, "Connected to %s\n", database);
123
fprintf(stdout, "Test run: no large objects will be removed!\n");
127
* Don't get fooled by any non-system catalogs
129
res = PQexec(conn, "SET search_path = pg_catalog");
130
if (PQresultStatus(res) != PGRES_COMMAND_OK)
132
fprintf(stderr, "Failed to set search_path:\n");
133
fprintf(stderr, "%s", PQerrorMessage(conn));
141
* First we create and populate the LO temp table
144
strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
145
strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject ");
146
res = PQexec(conn, buf);
147
if (PQresultStatus(res) != PGRES_COMMAND_OK)
149
fprintf(stderr, "Failed to create temp table:\n");
150
fprintf(stderr, "%s", PQerrorMessage(conn));
158
* Vacuum the temp table so that planner will generate decent plans for
162
strcat(buf, "VACUUM ANALYZE vacuum_l");
163
res = PQexec(conn, buf);
164
if (PQresultStatus(res) != PGRES_COMMAND_OK)
166
fprintf(stderr, "Failed to vacuum temp table:\n");
167
fprintf(stderr, "%s", PQerrorMessage(conn));
175
* Now find any candidate tables that have columns of type oid.
177
* NOTE: we ignore system tables and temp tables by the expedient of
178
* rejecting tables in schemas named 'pg_*'. In particular, the temp
179
* table formed above is ignored, and pg_largeobject will be too. If
180
* either of these were scanned, obviously we'd end up with nothing to
183
* NOTE: the system oid column is ignored, as it has attnum < 1. This
184
* shouldn't matter for correctness, but it saves time.
187
strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
188
strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
189
strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
190
strcat(buf, " AND a.attrelid = c.oid ");
191
strcat(buf, " AND a.atttypid = t.oid ");
192
strcat(buf, " AND c.relnamespace = s.oid ");
193
strcat(buf, " AND t.typname in ('oid', 'lo') ");
194
strcat(buf, " AND c.relkind = 'r'");
195
strcat(buf, " AND s.nspname !~ '^pg_'");
196
res = PQexec(conn, buf);
197
if (PQresultStatus(res) != PGRES_TUPLES_OK)
199
fprintf(stderr, "Failed to find OID columns:\n");
200
fprintf(stderr, "%s", PQerrorMessage(conn));
206
for (i = 0; i < PQntuples(res); i++)
212
schema = PQgetvalue(res, i, 0);
213
table = PQgetvalue(res, i, 1);
214
field = PQgetvalue(res, i, 2);
217
fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
220
* The "IN" construct used here was horribly inefficient before
221
* Postgres 7.4, but should be now competitive if not better than the
222
* bogus join we used before.
224
snprintf(buf, BUFSIZE,
225
"DELETE FROM vacuum_l "
226
"WHERE lo IN (SELECT \"%s\" FROM \"%s\".\"%s\")",
227
field, schema, table);
228
res2 = PQexec(conn, buf);
229
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
231
fprintf(stderr, "Failed to check %s in table %s.%s:\n",
232
field, schema, table);
233
fprintf(stderr, "%s", PQerrorMessage(conn));
244
* Run the actual deletes in a single transaction. Note that this would
245
* be a bad idea in pre-7.1 Postgres releases (since rolling back a table
246
* delete used to cause problems), but it should be safe now.
248
res = PQexec(conn, "begin");
252
* Finally, those entries remaining in vacuum_l are orphans.
255
strcat(buf, "SELECT lo ");
256
strcat(buf, "FROM vacuum_l");
257
res = PQexec(conn, buf);
258
if (PQresultStatus(res) != PGRES_TUPLES_OK)
260
fprintf(stderr, "Failed to read temp table:\n");
261
fprintf(stderr, "%s", PQerrorMessage(conn));
267
matched = PQntuples(res);
269
for (i = 0; i < matched; i++)
271
Oid lo = atooid(PQgetvalue(res, i, 0));
275
fprintf(stdout, "\rRemoving lo %6u ", lo);
279
if (param->dry_run == 0)
281
if (lo_unlink(conn, lo) < 0)
283
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
284
fprintf(stderr, "%s", PQerrorMessage(conn));
297
res = PQexec(conn, "end");
303
fprintf(stdout, "\r%s %d large objects from %s.\n",
304
(param->dry_run ? "Would remove" : "Removed"), deleted, database);
310
usage(const char *progname)
312
printf("%s removes unreferenced large objects from databases.\n\n", progname);
313
printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
314
printf("Options:\n");
315
printf(" -h HOSTNAME database server host or socket directory\n");
316
printf(" -n don't remove large objects, just show what would be done\n");
317
printf(" -p PORT database server port\n");
318
printf(" -U USERNAME user name to connect as\n");
319
printf(" -w never prompt for password\n");
320
printf(" -W force password prompt\n");
321
printf(" -v write a lot of progress messages\n");
322
printf(" --help show this help, then exit\n");
323
printf(" --version output version information, then exit\n");
325
printf("Report bugs to <pgsql-bugs@postgresql.org>.\n");
330
main(int argc, char **argv)
336
const char *progname;
338
progname = get_progname(argv[0]);
340
/* Parameter handling */
341
param.pg_user = NULL;
342
param.pg_prompt = TRI_DEFAULT;
343
param.pg_host = NULL;
344
param.pg_port = NULL;
350
if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
355
if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
357
puts("vacuumlo (PostgreSQL) " PG_VERSION);
364
c = getopt(argc, argv, "h:U:p:vnwW");
371
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
383
param.pg_user = strdup(optarg);
386
param.pg_prompt = TRI_NO;
389
param.pg_prompt = TRI_YES;
392
port = strtol(optarg, NULL, 10);
393
if ((port < 1) || (port > 65535))
395
fprintf(stderr, "%s: invalid port number: %s\n", progname, optarg);
398
param.pg_port = strdup(optarg);
401
param.pg_host = strdup(optarg);
406
/* No database given? Show usage */
409
fprintf(stderr, "vacuumlo: missing required argument: database name\n");
410
fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
414
for (c = optind; c < argc; c++)
416
/* Work on selected database */
417
rc += (vacuumlo(argv[c], ¶m) != 0);