Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit280a77d

Browse files
committed
Code review and cleanup.
1 parent22c528f commit280a77d

File tree

2 files changed

+102
-61
lines changed

2 files changed

+102
-61
lines changed

‎contrib/vacuumlo/README.vacuumlo

Lines changed: 18 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,35 +1,43 @@
1-
$Header: /cvsroot/pgsql/contrib/vacuumlo/Attic/README.vacuumlo,v 1.1 2000/06/19 14:02:16 momjian Exp $
1+
$Header: /cvsroot/pgsql/contrib/vacuumlo/Attic/README.vacuumlo,v 1.2 2000/11/21 17:54:21 tgl Exp $
22

33
This is a simple utility that will remove any orphaned large objects out of a
4-
PostgreSQL database.
4+
PostgreSQL database. An orphaned LO is considered to be any LO whose OID
5+
does not appear in any OID data column of the database.
6+
57

68
Compiling
79
--------
810

911
Simply run make. A single executable "vacuumlo" is created.
1012

11-
Useage
12-
------
13+
14+
Usage
15+
-----
1316

1417
vacuumlo [-v] database [db2 ... dbn]
1518

1619
The -v flag outputs some progress messages to stdout.
1720

21+
1822
Method
1923
------
2024

2125
First, it builds a temporary table which contains all of the oid's of the
2226
large objects in that database.
2327

24-
It then scans through any columns in the database that are of type 'oid', and
25-
removes any entries from the temporary table.
28+
It then scans through all columns in the database that are of type 'oid',
29+
and removes any matching entries from the temporary table.
30+
31+
The remaining entries in the temp table identify orphaned LOs. These are
32+
removed.
33+
2634

27-
Finally, it runs through the first table, and removes from the second table, any
28-
oid's it finds. What is left are the orphans, and these are removed.
35+
Notes
36+
-----
2937

3038
I decided to place this in contrib as it needs further testing, but hopefully,
31-
this (or a variant of it) would make it into thebacked as a "vacuum lo" command
32-
in a later release.
39+
this (or a variant of it) would make it into thebackend as a "vacuum lo"
40+
commandin a later release.
3341

3442
Peter Mount <peter@retep.org.uk>
3543
http://www.retep.org.uk

‎contrib/vacuumlo/vacuumlo.c

Lines changed: 84 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.6 2000/10/24 01:38:20 tgl Exp $
11+
* $Header: /cvsroot/pgsql/contrib/vacuumlo/vacuumlo.c,v 1.7 2000/11/21 17:54:21 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -24,13 +24,15 @@
2424
#include"libpq-fe.h"
2525
#include"libpq/libpq-fs.h"
2626

27+
#defineatooid(x) ((Oid) strtoul((x), NULL, 10))
28+
2729
#defineBUFSIZE1024
2830

2931
intvacuumlo(char*,int);
3032

3133

3234
/*
33-
* This vacuumsadatabase. It returns1 on success, -1 on failure.
35+
* This vacuumsLOs of onedatabase. It returns0 on success, -1 on failure.
3436
*/
3537
int
3638
vacuumlo(char*database,intverbose)
@@ -39,40 +41,68 @@ vacuumlo(char *database, int verbose)
3941
PGresult*res,
4042
*res2;
4143
charbuf[BUFSIZE];
42-
intmatched=0;/* Number matched per scan */
44+
intmatched;
45+
intdeleted;
4346
inti;
4447

4548
conn=PQsetdb(NULL,NULL,NULL,NULL,database);
4649

4750
/* check to see that the backend connection was successfully made */
4851
if (PQstatus(conn)==CONNECTION_BAD)
4952
{
50-
fprintf(stderr,"Connection to database '%s' failed.\n",database);
53+
fprintf(stderr,"Connection to database '%s' failed:\n",database);
5154
fprintf(stderr,"%s",PQerrorMessage(conn));
55+
PQfinish(conn);
5256
return-1;
5357
}
5458

5559
if (verbose)
5660
fprintf(stdout,"Connected to %s\n",database);
5761

5862
/*
59-
* First we create and populate thelo temp table
63+
* First we create and populate theLO temp table
6064
*/
6165
buf[0]='\0';
6266
strcat(buf,"SELECT DISTINCT loid AS lo ");
6367
strcat(buf,"INTO TEMP TABLE vacuum_l ");
6468
strcat(buf,"FROM pg_largeobject ");
65-
if (!(res=PQexec(conn,buf)))
69+
res=PQexec(conn,buf);
70+
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
71+
{
72+
fprintf(stderr,"Failed to create temp table:\n");
73+
fprintf(stderr,"%s",PQerrorMessage(conn));
74+
PQclear(res);
75+
PQfinish(conn);
76+
return-1;
77+
}
78+
PQclear(res);
79+
/*
80+
* Vacuum the temp table so that planner will generate decent plans
81+
* for the DELETEs below.
82+
*/
83+
buf[0]='\0';
84+
strcat(buf,"VACUUM ANALYZE vacuum_l ");
85+
res=PQexec(conn,buf);
86+
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
6687
{
67-
fprintf(stderr,"Failed to create temp table.\n");
88+
fprintf(stderr,"Failed to vacuum temp table:\n");
89+
fprintf(stderr,"%s",PQerrorMessage(conn));
90+
PQclear(res);
6891
PQfinish(conn);
6992
return-1;
7093
}
7194
PQclear(res);
7295

7396
/*
74-
* Now find any candidate tables who have columns of type oid (the
75-
* column oid is ignored, as it has attnum < 1)
97+
* Now find any candidate tables who have columns of type oid.
98+
*
99+
* NOTE: the temp table formed above is ignored, because its real
100+
* table name will be pg_something. Also, pg_largeobject will be
101+
* ignored. If either of these were scanned, obviously we'd end up
102+
* with nothing to delete...
103+
*
104+
* NOTE: the system oid column is ignored, as it has attnum < 1.
105+
* This shouldn't matter for correctness, but it saves time.
76106
*/
77107
buf[0]='\0';
78108
strcat(buf,"SELECT c.relname, a.attname ");
@@ -81,13 +111,18 @@ vacuumlo(char *database, int verbose)
81111
strcat(buf," AND a.attrelid = c.oid ");
82112
strcat(buf," AND a.atttypid = t.oid ");
83113
strcat(buf," AND t.typname = 'oid' ");
114+
strcat(buf," AND c.relkind = 'r'");
84115
strcat(buf," AND c.relname NOT LIKE 'pg_%'");
85-
if (!(res=PQexec(conn,buf)))
116+
res=PQexec(conn,buf);
117+
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
86118
{
87-
fprintf(stderr,"Failed to create temp table.\n");
119+
fprintf(stderr,"Failed to find OID columns:\n");
120+
fprintf(stderr,"%s",PQerrorMessage(conn));
121+
PQclear(res);
88122
PQfinish(conn);
89123
return-1;
90124
}
125+
91126
for (i=0;i<PQntuples(res);i++)
92127
{
93128
char*table,
@@ -97,50 +132,36 @@ vacuumlo(char *database, int verbose)
97132
field=PQgetvalue(res,i,1);
98133

99134
if (verbose)
100-
{
101-
fprintf(stdout,"Checking %s in %s: ",field,table);
102-
fflush(stdout);
103-
}
104-
105-
res2=PQexec(conn,"begin");
106-
PQclear(res2);
107-
108-
buf[0]='\0';
109-
strcat(buf,"DELETE FROM vacuum_l ");
110-
strcat(buf,"WHERE lo IN (");
111-
strcat(buf,"SELECT ");
112-
strcat(buf,field);
113-
strcat(buf," FROM ");
114-
strcat(buf,table);
115-
strcat(buf,");");
116-
if (!(res2=PQexec(conn,buf)))
117-
{
118-
fprintf(stderr,"Failed to check %s in table %s\n",field,table);
119-
PQclear(res);
120-
PQfinish(conn);
121-
return-1;
122-
}
135+
fprintf(stdout,"Checking %s in %s\n",field,table);
136+
137+
/*
138+
* We use a DELETE with implicit join for efficiency. This
139+
* is a Postgres-ism and not portable to other DBMSs, but
140+
* then this whole program is a Postgres-ism.
141+
*/
142+
sprintf(buf,"DELETE FROM vacuum_l WHERE lo = \"%s\".\"%s\" ",
143+
table,field);
144+
res2=PQexec(conn,buf);
123145
if (PQresultStatus(res2)!=PGRES_COMMAND_OK)
124146
{
125-
fprintf(stderr,
126-
"Failed to check %s in table %s\n%s\n",
127-
field,table,
128-
PQerrorMessage(conn)
129-
);
147+
fprintf(stderr,"Failed to check %s in table %s:\n",
148+
field,table);
149+
fprintf(stderr,"%s",PQerrorMessage(conn));
130150
PQclear(res2);
131151
PQclear(res);
132152
PQfinish(conn);
133153
return-1;
134154
}
135155
PQclear(res2);
136-
137-
res2=PQexec(conn,"end");
138-
PQclear(res2);
139-
140156
}
141157
PQclear(res);
142158

143-
/* Start the transaction */
159+
/*
160+
* Run the actual deletes in a single transaction. Note that this
161+
* would be a bad idea in pre-7.1 Postgres releases (since rolling
162+
* back a table delete used to cause problems), but it should
163+
* be safe now.
164+
*/
144165
res=PQexec(conn,"begin");
145166
PQclear(res);
146167

@@ -150,25 +171,35 @@ vacuumlo(char *database, int verbose)
150171
buf[0]='\0';
151172
strcat(buf,"SELECT lo ");
152173
strcat(buf,"FROM vacuum_l");
153-
if (!(res=PQexec(conn,buf)))
174+
res=PQexec(conn,buf);
175+
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
154176
{
155-
fprintf(stderr,"Failed to read temp table.\n");
177+
fprintf(stderr,"Failed to read temp table:\n");
178+
fprintf(stderr,"%s",PQerrorMessage(conn));
179+
PQclear(res);
156180
PQfinish(conn);
157181
return-1;
158182
}
183+
159184
matched=PQntuples(res);
185+
deleted=0;
160186
for (i=0;i<matched;i++)
161187
{
162-
Oidlo=(Oid)atoi(PQgetvalue(res,i,0));
188+
Oidlo=atooid(PQgetvalue(res,i,0));
163189

164190
if (verbose)
165191
{
166-
fprintf(stdout,"\rRemoving lo %6d \n",lo);
192+
fprintf(stdout,"\rRemoving lo %6u",lo);
167193
fflush(stdout);
168194
}
169195

170196
if (lo_unlink(conn,lo)<0)
171-
fprintf(stderr,"Failed to remove lo %d\n",lo);
197+
{
198+
fprintf(stderr,"\nFailed to remove lo %u: ",lo);
199+
fprintf(stderr,"%s",PQerrorMessage(conn));
200+
}
201+
else
202+
deleted++;
172203
}
173204
PQclear(res);
174205

@@ -177,10 +208,12 @@ vacuumlo(char *database, int verbose)
177208
*/
178209
res=PQexec(conn,"end");
179210
PQclear(res);
211+
180212
PQfinish(conn);
181213

182214
if (verbose)
183-
fprintf(stdout,"\rRemoved %d large objects from %s.\n",matched,database);
215+
fprintf(stdout,"\rRemoved %d large objects from %s.\n",
216+
deleted,database);
184217

185218
return0;
186219
}
@@ -204,7 +237,7 @@ main(int argc, char **argv)
204237
if (strcmp("-v",argv[arg])==0)
205238
verbose= !verbose;
206239
else
207-
rc+=vacuumlo(argv[arg],verbose);
240+
rc+=(vacuumlo(argv[arg],verbose)!=0);
208241
}
209242

210243
returnrc;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp