|
| 1 | +/*------------------------------------------------------------------------- |
| 2 | + * |
| 3 | + * vacuumlo.c |
| 4 | + * This removes orphaned large objects from a database. |
| 5 | + * |
| 6 | + * Copyright (c) 1994, Regents of the University of California |
| 7 | + * |
| 8 | + * |
| 9 | + * IDENTIFICATION |
| 10 | + * $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.1 1999/04/10 16:48:05 peter Exp $ |
| 11 | + * |
| 12 | + *------------------------------------------------------------------------- |
| 13 | + */ |
| 14 | +#include<stdio.h> |
| 15 | +#include<stdlib.h> |
| 16 | +#include<strings.h> |
| 17 | + |
| 18 | +#include<sys/types.h> |
| 19 | +#include<sys/stat.h> |
| 20 | +#include<fcntl.h> |
| 21 | +#include<unistd.h> |
| 22 | + |
| 23 | +#include"libpq-fe.h" |
| 24 | +#include"libpq/libpq-fs.h" |
| 25 | + |
| 26 | +#defineBUFSIZE1024 |
| 27 | + |
| 28 | +intvacuumlo(char*,int); |
| 29 | + |
| 30 | + |
| 31 | +/* |
| 32 | + * This vacuums a database. It returns 1 on success, -1 on failure. |
| 33 | + */ |
| 34 | +intvacuumlo(char*database,intverbose) |
| 35 | +{ |
| 36 | +PGconn*conn; |
| 37 | +PGresult*res,*res2; |
| 38 | +charbuf[BUFSIZE]; |
| 39 | +intmatched=0;/* Number matched per scan */ |
| 40 | +inti; |
| 41 | + |
| 42 | +conn=PQsetdb(NULL,NULL,NULL,NULL,database); |
| 43 | + |
| 44 | +/* check to see that the backend connection was successfully made */ |
| 45 | +if (PQstatus(conn)==CONNECTION_BAD) |
| 46 | +{ |
| 47 | +fprintf(stderr,"Connection to database '%s' failed.\n",database); |
| 48 | +fprintf(stderr,"%s",PQerrorMessage(conn)); |
| 49 | +return-1; |
| 50 | +} |
| 51 | + |
| 52 | +if(verbose) |
| 53 | +fprintf(stdout,"Connected to %s\n",database); |
| 54 | + |
| 55 | +/* |
| 56 | + * First we create and populate the lo temp table |
| 57 | + */ |
| 58 | +buf[0]='\0'; |
| 59 | +strcat(buf,"SELECT oid AS lo "); |
| 60 | +strcat(buf,"INTO TEMP TABLE vacuum_l "); |
| 61 | +strcat(buf,"FROM pg_class "); |
| 62 | +strcat(buf,"WHERE relkind='l'"); |
| 63 | +if(!(res=PQexec(conn,buf))) { |
| 64 | +fprintf(stderr,"Failed to create temp table.\n"); |
| 65 | +PQfinish(conn); |
| 66 | +return-1; |
| 67 | + } |
| 68 | +PQclear(res); |
| 69 | + |
| 70 | +/* |
| 71 | + * Now find any candidate tables who have columns of type oid (the column |
| 72 | + * oid is ignored, as it has attnum < 1) |
| 73 | + */ |
| 74 | +buf[0]='\0'; |
| 75 | +strcat(buf,"SELECT c.relname, a.attname "); |
| 76 | +strcat(buf,"FROM pg_class c, pg_attribute a, pg_type t "); |
| 77 | +strcat(buf,"WHERE a.attnum > 0 "); |
| 78 | +strcat(buf," AND a.attrelid = c.oid "); |
| 79 | +strcat(buf," AND a.atttypid = t.oid "); |
| 80 | +strcat(buf," AND t.typname = 'oid' "); |
| 81 | +strcat(buf," AND c.relname NOT LIKE 'pg_%'"); |
| 82 | +if(!(res=PQexec(conn,buf))) { |
| 83 | +fprintf(stderr,"Failed to create temp table.\n"); |
| 84 | +PQfinish(conn); |
| 85 | +return-1; |
| 86 | + } |
| 87 | +for(i=0;i<PQntuples(res);i++) |
| 88 | +{ |
| 89 | +char*table,*field; |
| 90 | + |
| 91 | +table=PQgetvalue(res,i,0); |
| 92 | +field=PQgetvalue(res,i,1); |
| 93 | + |
| 94 | +if(verbose) { |
| 95 | +fprintf(stdout,"Checking %s in %s: ",field,table); |
| 96 | +fflush(stdout); |
| 97 | + } |
| 98 | + |
| 99 | +res2=PQexec(conn,"begin"); |
| 100 | +PQclear(res2); |
| 101 | + |
| 102 | +buf[0]='\0'; |
| 103 | +strcat(buf,"DELETE FROM vacuum_l "); |
| 104 | +strcat(buf,"WHERE lo IN ("); |
| 105 | +strcat(buf,"SELECT "); |
| 106 | +strcat(buf,field); |
| 107 | +strcat(buf," FROM "); |
| 108 | +strcat(buf,table); |
| 109 | +strcat(buf,");"); |
| 110 | +if(!(res2=PQexec(conn,buf))) { |
| 111 | +fprintf(stderr,"Failed to check %s in table %s\n",field,table); |
| 112 | +PQclear(res); |
| 113 | +PQfinish(conn); |
| 114 | +return-1; |
| 115 | + } |
| 116 | +if(PQresultStatus(res2)!=PGRES_COMMAND_OK) { |
| 117 | +fprintf(stderr, |
| 118 | +"Failed to check %s in table %s\n%s\n", |
| 119 | +field,table, |
| 120 | +PQerrorMessage(conn) |
| 121 | +); |
| 122 | +PQclear(res2); |
| 123 | +PQclear(res); |
| 124 | +PQfinish(conn); |
| 125 | +return-1; |
| 126 | + } |
| 127 | +PQclear(res2); |
| 128 | + |
| 129 | +res2=PQexec(conn,"end"); |
| 130 | +PQclear(res2); |
| 131 | + |
| 132 | +} |
| 133 | +PQclear(res); |
| 134 | + |
| 135 | +/* Start the transaction */ |
| 136 | +res=PQexec(conn,"begin"); |
| 137 | +PQclear(res); |
| 138 | + |
| 139 | +/* |
| 140 | + * Finally, those entries remaining in vacuum_l are orphans. |
| 141 | + */ |
| 142 | +buf[0]='\0'; |
| 143 | +strcat(buf,"SELECT lo "); |
| 144 | +strcat(buf,"FROM vacuum_l"); |
| 145 | +if(!(res=PQexec(conn,buf))) { |
| 146 | +fprintf(stderr,"Failed to read temp table.\n"); |
| 147 | +PQfinish(conn); |
| 148 | +return-1; |
| 149 | + } |
| 150 | +matched=PQntuples(res); |
| 151 | +for(i=0;i<matched;i++) |
| 152 | +{ |
| 153 | +Oidlo= (Oid)atoi(PQgetvalue(res,i,0)); |
| 154 | + |
| 155 | +if(verbose) { |
| 156 | +fprintf(stdout,"\rRemoving lo %6d \n",lo); |
| 157 | +fflush(stdout); |
| 158 | + } |
| 159 | + |
| 160 | +if(lo_unlink(conn,lo)<0) { |
| 161 | +fprintf(stderr,"Failed to remove lo %d\n",lo); |
| 162 | + } |
| 163 | +} |
| 164 | +PQclear(res); |
| 165 | + |
| 166 | +/* |
| 167 | + * That's all folks! |
| 168 | + */ |
| 169 | +res=PQexec(conn,"end"); |
| 170 | +PQclear(res); |
| 171 | +PQfinish(conn); |
| 172 | + |
| 173 | +if(verbose) |
| 174 | +fprintf(stdout,"\rRemoved %d large objects from %s.\n",matched,database); |
| 175 | + |
| 176 | +return0; |
| 177 | +} |
| 178 | + |
| 179 | +int |
| 180 | +main(intargc,char**argv) |
| 181 | +{ |
| 182 | +intverbose=0; |
| 183 | +intarg; |
| 184 | +intrc=0; |
| 185 | + |
| 186 | +if (argc<2) |
| 187 | +{ |
| 188 | +fprintf(stderr,"Usage: %s [-v] database_name [db2 ... dbn]\n", |
| 189 | +argv[0]); |
| 190 | +exit(1); |
| 191 | +} |
| 192 | + |
| 193 | +for(arg=1;arg<argc;arg++) { |
| 194 | +if(strcmp("-v",argv[arg])==0) |
| 195 | +verbose=!verbose; |
| 196 | +else |
| 197 | +rc+=vacuumlo(argv[arg],verbose); |
| 198 | + } |
| 199 | + |
| 200 | +returnrc; |
| 201 | +} |
