1 /*-------------------------------------------------------------------------
4 * This removes orphaned large objects from a database.
6 * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
7 * Portions Copyright (c) 1994, Regents of the University of California
11 * $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.6 2000/10/24 01:38:20 tgl Exp $
13 *-------------------------------------------------------------------------
19 #include <sys/types.h>
25 #include "libpq/libpq-fs.h"
29 int vacuumlo(char *, int);
33 * This vacuums a database. It returns 1 on success, -1 on failure.
36 vacuumlo(char *database, int verbose)
42 int matched = 0; /* Number matched per scan */
45 conn = PQsetdb(NULL, NULL, NULL, NULL, database);
47 /* check to see that the backend connection was successfully made */
48 if (PQstatus(conn) == CONNECTION_BAD)
50 fprintf(stderr, "Connection to database '%s' failed.\n", database);
51 fprintf(stderr, "%s", PQerrorMessage(conn));
56 fprintf(stdout, "Connected to %s\n", database);
59 * First we create and populate the lo temp table
62 strcat(buf, "SELECT DISTINCT loid AS lo ");
63 strcat(buf, "INTO TEMP TABLE vacuum_l ");
64 strcat(buf, "FROM pg_largeobject ");
65 if (!(res = PQexec(conn, buf)))
67 fprintf(stderr, "Failed to create temp table.\n");
74 * Now find any candidate tables who have columns of type oid (the
75 * column oid is ignored, as it has attnum < 1)
78 strcat(buf, "SELECT c.relname, a.attname ");
79 strcat(buf, "FROM pg_class c, pg_attribute a, pg_type t ");
80 strcat(buf, "WHERE a.attnum > 0 ");
81 strcat(buf, " AND a.attrelid = c.oid ");
82 strcat(buf, " AND a.atttypid = t.oid ");
83 strcat(buf, " AND t.typname = 'oid' ");
84 strcat(buf, " AND c.relname NOT LIKE 'pg_%'");
85 if (!(res = PQexec(conn, buf)))
87 fprintf(stderr, "Failed to create temp table.\n");
91 for (i = 0; i < PQntuples(res); i++)
96 table = PQgetvalue(res, i, 0);
97 field = PQgetvalue(res, i, 1);
101 fprintf(stdout, "Checking %s in %s: ", field, table);
105 res2 = PQexec(conn, "begin");
109 strcat(buf, "DELETE FROM vacuum_l ");
110 strcat(buf, "WHERE lo IN (");
111 strcat(buf, "SELECT ");
113 strcat(buf, " FROM ");
116 if (!(res2 = PQexec(conn, buf)))
118 fprintf(stderr, "Failed to check %s in table %s\n", field, table);
123 if (PQresultStatus(res2) != PGRES_COMMAND_OK)
126 "Failed to check %s in table %s\n%s\n",
137 res2 = PQexec(conn, "end");
143 /* Start the transaction */
144 res = PQexec(conn, "begin");
148 * Finally, those entries remaining in vacuum_l are orphans.
151 strcat(buf, "SELECT lo ");
152 strcat(buf, "FROM vacuum_l");
153 if (!(res = PQexec(conn, buf)))
155 fprintf(stderr, "Failed to read temp table.\n");
159 matched = PQntuples(res);
160 for (i = 0; i < matched; i++)
162 Oid lo = (Oid) atoi(PQgetvalue(res, i, 0));
166 fprintf(stdout, "\rRemoving lo %6d \n", lo);
170 if (lo_unlink(conn, lo) < 0)
171 fprintf(stderr, "Failed to remove lo %d\n", lo);
178 res = PQexec(conn, "end");
183 fprintf(stdout, "\rRemoved %d large objects from %s.\n", matched, database);
189 main(int argc, char **argv)
197 fprintf(stderr, "Usage: %s [-v] database_name [db2 ... dbn]\n",
202 for (arg = 1; arg < argc; arg++)
204 if (strcmp("-v", argv[arg]) == 0)
207 rc += vacuumlo(argv[arg], verbose);