1
/*-------------------------------------------------------------------------
4
* This removes orphaned large objects from a database.
6
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
7
* Portions Copyright (c) 1994, Regents of the University of California
11
* $PostgreSQL: pgsql/contrib/vacuumlo/vacuumlo.c,v 1.29 2004-12-31 21:58:50 pgsql 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 *);
52
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
55
vacuumlo(char *database, struct _param * param)
64
char *password = NULL;
68
password = simple_prompt("Password: ", 32, 0);
71
fprintf(stderr, "failed to get password\n");
76
conn = PQsetdbLogin(param->pg_host,
85
/* check to see that the backend connection was successfully made */
86
if (PQstatus(conn) == CONNECTION_BAD)
88
fprintf(stderr, "Connection to database '%s' failed:\n", database);
89
fprintf(stderr, "%s", PQerrorMessage(conn));
96
fprintf(stdout, "Connected to %s\n", database);
98
fprintf(stdout, "Test run: no large objects will be removed!\n");
102
* Don't get fooled by any non-system catalogs
104
res = PQexec(conn, "SET search_path = pg_catalog");
105
if (PQresultStatus(res) != PGRES_COMMAND_OK)
107
fprintf(stderr, "Failed to set search_path:\n");
108
fprintf(stderr, "%s", PQerrorMessage(conn));
116
* First we create and populate the LO temp table
119
strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
120
strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject ");
121
res = PQexec(conn, buf);
122
if (PQresultStatus(res) != PGRES_COMMAND_OK)
124
fprintf(stderr, "Failed to create temp table:\n");
125
fprintf(stderr, "%s", PQerrorMessage(conn));
133
* Vacuum the temp table so that planner will generate decent plans
134
* for the DELETEs below.
137
strcat(buf, "VACUUM ANALYZE vacuum_l");
138
res = PQexec(conn, buf);
139
if (PQresultStatus(res) != PGRES_COMMAND_OK)
141
fprintf(stderr, "Failed to vacuum temp table:\n");
142
fprintf(stderr, "%s", PQerrorMessage(conn));
150
* Now find any candidate tables that have columns of type oid.
152
* NOTE: we ignore system tables and temp tables by the expedient of
153
* rejecting tables in schemas named 'pg_*'. In particular, the temp
154
* table formed above is ignored, and pg_largeobject will be too. If
155
* either of these were scanned, obviously we'd end up with nothing to
158
* NOTE: the system oid column is ignored, as it has attnum < 1. This
159
* shouldn't matter for correctness, but it saves time.
162
strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
163
strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
164
strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
165
strcat(buf, " AND a.attrelid = c.oid ");
166
strcat(buf, " AND a.atttypid = t.oid ");
167
strcat(buf, " AND c.relnamespace = s.oid ");
168
strcat(buf, " AND t.typname in ('oid', 'lo') ");
169
strcat(buf, " AND c.relkind = 'r'");
170
strcat(buf, " AND s.nspname NOT LIKE 'pg\\\\_%'");
171
res = PQexec(conn, buf);
172
if (PQresultStatus(res) != PGRES_TUPLES_OK)
174
fprintf(stderr, "Failed to find OID columns:\n");
175
fprintf(stderr, "%s", PQerrorMessage(conn));
181
for (i = 0; i < PQntuples(res); i++)
187
schema = PQgetvalue(res, i, 0);
188
table = PQgetvalue(res, i, 1);
189
field = PQgetvalue(res, i, 2);
192
fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);
195
* The "IN" construct used here was horribly inefficient before
196
* Postgres 7.4, but should be now competitive if not better than
197
* the bogus join we used before.
199
snprintf(buf, BUFSIZE,
200
"DELETE FROM vacuum_l "
201
"WHERE lo IN (SELECT \"%s\" FROM \"%s\".\"%s\")",
202
field, schema, table);
203
res2 = PQexec(conn, buf);
204
if (PQresultStatus(res2) != PGRES_COMMAND_OK)
206
fprintf(stderr, "Failed to check %s in table %s.%s:\n",
207
field, schema, table);
208
fprintf(stderr, "%s", PQerrorMessage(conn));
219
* Run the actual deletes in a single transaction. Note that this
220
* would be a bad idea in pre-7.1 Postgres releases (since rolling
221
* back a table delete used to cause problems), but it should be safe
224
res = PQexec(conn, "begin");
228
* Finally, those entries remaining in vacuum_l are orphans.
231
strcat(buf, "SELECT lo ");
232
strcat(buf, "FROM vacuum_l");
233
res = PQexec(conn, buf);
234
if (PQresultStatus(res) != PGRES_TUPLES_OK)
236
fprintf(stderr, "Failed to read temp table:\n");
237
fprintf(stderr, "%s", PQerrorMessage(conn));
243
matched = PQntuples(res);
245
for (i = 0; i < matched; i++)
247
Oid lo = atooid(PQgetvalue(res, i, 0));
251
fprintf(stdout, "\rRemoving lo %6u ", lo);
255
if (param->dry_run == 0)
257
if (lo_unlink(conn, lo) < 0)
259
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
260
fprintf(stderr, "%s", PQerrorMessage(conn));
273
res = PQexec(conn, "end");
279
fprintf(stdout, "\r%s %d large objects from %s.\n",
280
(param->dry_run ? "Would remove" : "Removed"), deleted, database);
288
fprintf(stdout, "vacuumlo removes unreferenced large objects from databases\n\n");
289
fprintf(stdout, "Usage:\n vacuumlo [options] dbname [dbname ...]\n\n");
290
fprintf(stdout, "Options:\n");
291
fprintf(stdout, " -v\t\tWrite a lot of progress messages\n");
292
fprintf(stdout, " -n\t\tDon't remove large objects, just show what would be done\n");
293
fprintf(stdout, " -U username\tUsername to connect as\n");
294
fprintf(stdout, " -W\t\tPrompt for password\n");
295
fprintf(stdout, " -h hostname\tDatabase server host\n");
296
fprintf(stdout, " -p port\tDatabase server port\n\n");
301
main(int argc, char **argv)
308
/* Parameter handling */
309
param.pg_user = NULL;
311
param.pg_host = NULL;
312
param.pg_port = NULL;
318
c = getopt(argc, argv, "?h:U:p:vnW");
341
param.pg_user = strdup(optarg);
347
port = strtol(optarg, NULL, 10);
348
if ((port < 1) || (port > 65535))
350
fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0], optarg);
353
param.pg_port = strdup(optarg);
356
param.pg_host = strdup(optarg);
361
/* No database given? Show usage */
364
fprintf(stderr, "vacuumlo: missing required argument: database name\n");
365
fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
369
for (c = optind; c < argc; c++)
371
/* Work on selected database */
372
rc += (vacuumlo(argv[c], ¶m) != 0);