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

Commit5bd06e6

Browse files
committed
Back-patch contrib/vacuumlo's new -l (limit) option into 9.0 and 9.1.
Since 9.0, removing lots of large objects in a single transaction risksexceeding max_locks_per_transaction, because we merged large object removalinto the generic object-drop mechanism, which takes out an exclusive lockon each object to be dropped. This creates a hazard for contrib/vacuumlo,which has historically tried to drop all unreferenced large objects in onetransaction. There doesn't seem to be any correctness requirement to do itthat way, though; we only need to drop enough large objects per transactionto amortize the commit costs.To prevent a regression from pre-9.0 releases wherein vacuumlo worked justfine, back-patch commitsb69f2e3 and64c6048, which break vacuumlo's deletionsinto multiple transactions with a user-controllable upper limit on thenumber of objects dropped per transaction.Tim Lewis, Robert Haas, Tom Lane
1 parent16f42be commit5bd06e6

File tree

2 files changed

+133
-32
lines changed

2 files changed

+133
-32
lines changed

‎contrib/vacuumlo/vacuumlo.c

Lines changed: 115 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* vacuumlo.c
44
* This removes orphaned large objects from a database.
55
*
6-
* Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group
6+
* Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
*
@@ -22,16 +22,14 @@
2222
#endif
2323

2424
#include"libpq-fe.h"
25-
#include"libpq/libpq-fs.h"
2625

2726
#defineatooid(x) ((Oid) strtoul((x), NULL, 10))
2827

2928
#defineBUFSIZE1024
3029

3130
externchar*optarg;
3231
externintoptind,
33-
opterr,
34-
optopt;
32+
opterr;
3533

3634
enumtrivalue
3735
{
@@ -48,29 +46,32 @@ struct _param
4846
char*pg_host;
4947
intverbose;
5048
intdry_run;
49+
longtransaction_limit;
5150
};
5251

53-
intvacuumlo(char*,struct_param*);
54-
voidusage(constchar*progname);
52+
staticintvacuumlo(constchar*database,conststruct_param*param);
53+
staticvoidusage(constchar*progname);
5554

5655

5756

5857
/*
5958
* This vacuums LOs of one database. It returns 0 on success, -1 on failure.
6059
*/
61-
int
62-
vacuumlo(char*database,struct_param*param)
60+
staticint
61+
vacuumlo(constchar*database,conststruct_param*param)
6362
{
6463
PGconn*conn;
6564
PGresult*res,
6665
*res2;
6766
charbuf[BUFSIZE];
68-
intmatched;
69-
intdeleted;
67+
longmatched;
68+
longdeleted;
7069
inti;
7170
staticchar*password=NULL;
7271
boolnew_pass;
72+
boolsuccess= true;
7373

74+
/* Note: password can be carried over from a previous call */
7475
if (param->pg_prompt==TRI_YES&&password==NULL)
7576
password=simple_prompt("Password: ",100, false);
7677

@@ -118,7 +119,7 @@ vacuumlo(char *database, struct _param * param)
118119

119120
if (param->verbose)
120121
{
121-
fprintf(stdout,"Connected to%s\n",database);
122+
fprintf(stdout,"Connected todatabase \"%s\"\n",database);
122123
if (param->dry_run)
123124
fprintf(stdout,"Test run: no large objects will be removed!\n");
124125
}
@@ -219,9 +220,21 @@ vacuumlo(char *database, struct _param * param)
219220
if (param->verbose)
220221
fprintf(stdout,"Checking %s in %s.%s\n",field,schema,table);
221222

223+
schema=PQescapeIdentifier(conn,schema,strlen(schema));
224+
table=PQescapeIdentifier(conn,table,strlen(table));
225+
field=PQescapeIdentifier(conn,field,strlen(field));
226+
227+
if (!schema|| !table|| !field)
228+
{
229+
fprintf(stderr,"Out of memory\n");
230+
PQclear(res);
231+
PQfinish(conn);
232+
return-1;
233+
}
234+
222235
snprintf(buf,BUFSIZE,
223236
"DELETE FROM vacuum_l "
224-
"WHERE lo IN (SELECT\"%s\" FROM\"%s\".\"%s\")",
237+
"WHERE lo IN (SELECT%s FROM%s.%s)",
225238
field,schema,table);
226239
res2=PQexec(conn,buf);
227240
if (PQresultStatus(res2)!=PGRES_COMMAND_OK)
@@ -235,23 +248,35 @@ vacuumlo(char *database, struct _param * param)
235248
return-1;
236249
}
237250
PQclear(res2);
251+
252+
PQfreemem(schema);
253+
PQfreemem(table);
254+
PQfreemem(field);
238255
}
239256
PQclear(res);
240257

241258
/*
242-
* Run the actual deletes in a single transaction.Note that this would
243-
* be a bad idea in pre-7.1 Postgres releases (since rolling back a table
244-
* delete used to cause problems), but it should be safe now.
259+
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
260+
*
261+
* We don't want to run each delete as an individual transaction, because
262+
* the commit overhead would be high. However, since 9.0 the backend will
263+
* acquire a lock per deleted LO, so deleting too many LOs per transaction
264+
* risks running out of room in the shared-memory lock table.
265+
* Accordingly, we delete up to transaction_limit LOs per transaction.
245266
*/
246267
res=PQexec(conn,"begin");
268+
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
269+
{
270+
fprintf(stderr,"Failed to start transaction:\n");
271+
fprintf(stderr,"%s",PQerrorMessage(conn));
272+
PQclear(res);
273+
PQfinish(conn);
274+
return-1;
275+
}
247276
PQclear(res);
248277

249-
/*
250-
* Finally, those entries remaining in vacuum_l are orphans.
251-
*/
252278
buf[0]='\0';
253-
strcat(buf,"SELECT lo ");
254-
strcat(buf,"FROM vacuum_l");
279+
strcat(buf,"SELECT lo FROM vacuum_l");
255280
res=PQexec(conn,buf);
256281
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
257282
{
@@ -280,37 +305,87 @@ vacuumlo(char *database, struct _param * param)
280305
{
281306
fprintf(stderr,"\nFailed to remove lo %u: ",lo);
282307
fprintf(stderr,"%s",PQerrorMessage(conn));
308+
if (PQtransactionStatus(conn)==PQTRANS_INERROR)
309+
{
310+
success= false;
311+
break;
312+
}
283313
}
284314
else
285315
deleted++;
286316
}
287317
else
288318
deleted++;
319+
if (param->transaction_limit>0&&
320+
(deleted %param->transaction_limit)==0)
321+
{
322+
res2=PQexec(conn,"commit");
323+
if (PQresultStatus(res2)!=PGRES_COMMAND_OK)
324+
{
325+
fprintf(stderr,"Failed to commit transaction:\n");
326+
fprintf(stderr,"%s",PQerrorMessage(conn));
327+
PQclear(res2);
328+
PQclear(res);
329+
PQfinish(conn);
330+
return-1;
331+
}
332+
PQclear(res2);
333+
res2=PQexec(conn,"begin");
334+
if (PQresultStatus(res2)!=PGRES_COMMAND_OK)
335+
{
336+
fprintf(stderr,"Failed to start transaction:\n");
337+
fprintf(stderr,"%s",PQerrorMessage(conn));
338+
PQclear(res2);
339+
PQclear(res);
340+
PQfinish(conn);
341+
return-1;
342+
}
343+
PQclear(res2);
344+
}
289345
}
290346
PQclear(res);
291347

292348
/*
293349
* That's all folks!
294350
*/
295-
res=PQexec(conn,"end");
351+
res=PQexec(conn,"commit");
352+
if (PQresultStatus(res)!=PGRES_COMMAND_OK)
353+
{
354+
fprintf(stderr,"Failed to commit transaction:\n");
355+
fprintf(stderr,"%s",PQerrorMessage(conn));
356+
PQclear(res);
357+
PQfinish(conn);
358+
return-1;
359+
}
296360
PQclear(res);
297361

298362
PQfinish(conn);
299363

300364
if (param->verbose)
301-
fprintf(stdout,"\r%s %d large objects from %s.\n",
302-
(param->dry_run ?"Would remove" :"Removed"),deleted,database);
365+
{
366+
if (param->dry_run)
367+
fprintf(stdout,"\rWould remove %ld large objects from database \"%s\".\n",
368+
deleted,database);
369+
elseif (success)
370+
fprintf(stdout,
371+
"\rSuccessfully removed %ld large objects from database \"%s\".\n",
372+
deleted,database);
373+
else
374+
fprintf(stdout,"\rRemoval from database \"%s\" failed at object %ld of %ld.\n",
375+
database,deleted,matched);
376+
}
303377

304-
return0;
378+
return((param->dry_run||success) ?0 :-1);
305379
}
306380

307-
void
381+
staticvoid
308382
usage(constchar*progname)
309383
{
310384
printf("%s removes unreferenced large objects from databases.\n\n",progname);
311385
printf("Usage:\n %s [OPTION]... DBNAME...\n\n",progname);
312386
printf("Options:\n");
313387
printf(" -h HOSTNAME database server host or socket directory\n");
388+
printf(" -l LIMIT commit after removing each LIMIT large objects\n");
314389
printf(" -n don't remove large objects, just show what would be done\n");
315390
printf(" -p PORT database server port\n");
316391
printf(" -U USERNAME user name to connect as\n");
@@ -335,14 +410,16 @@ main(int argc, char **argv)
335410

336411
progname=get_progname(argv[0]);
337412

338-
/*Parameter handling */
413+
/*Set default parameter values */
339414
param.pg_user=NULL;
340415
param.pg_prompt=TRI_DEFAULT;
341416
param.pg_host=NULL;
342417
param.pg_port=NULL;
343418
param.verbose=0;
344419
param.dry_run=0;
420+
param.transaction_limit=1000;
345421

422+
/* Process command-line arguments */
346423
if (argc>1)
347424
{
348425
if (strcmp(argv[1],"--help")==0||strcmp(argv[1],"-?")==0)
@@ -359,7 +436,7 @@ main(int argc, char **argv)
359436

360437
while (1)
361438
{
362-
c=getopt(argc,argv,"h:U:p:vnwW");
439+
c=getopt(argc,argv,"h:l:U:p:vnwW");
363440
if (c==-1)
364441
break;
365442

@@ -377,6 +454,16 @@ main(int argc, char **argv)
377454
param.dry_run=1;
378455
param.verbose=1;
379456
break;
457+
case'l':
458+
param.transaction_limit=strtol(optarg,NULL,10);
459+
if (param.transaction_limit<0)
460+
{
461+
fprintf(stderr,
462+
"%s: transaction limit must not be negative (0 disables)\n",
463+
progname);
464+
exit(1);
465+
}
466+
break;
380467
case'U':
381468
param.pg_user=strdup(optarg);
382469
break;
@@ -405,7 +492,7 @@ main(int argc, char **argv)
405492
if (optind >=argc)
406493
{
407494
fprintf(stderr,"vacuumlo: missing required argument: database name\n");
408-
fprintf(stderr,"Try'vacuumlo -?' forhelp.\n");
495+
fprintf(stderr,_("Try\"%s --help\" formore information.\n"),progname);
409496
exit(1);
410497
}
411498

‎doc/src/sgml/vacuumlo.sgml

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,19 @@ vacuumlo [options] database [database2 ... databaseN]
4949
</listitem>
5050
</varlistentry>
5151

52+
<varlistentry>
53+
<term><option>-l</option> <replaceable>limit</></term>
54+
<listitem>
55+
<para>
56+
Remove no more than <replaceable>limit</> large objects per
57+
transaction (default 1000). Since the server acquires a lock per LO
58+
removed, removing too many LOs in one transaction risks exceeding
59+
<xref linkend="guc-max-locks-per-transaction">. Set the limit to
60+
zero if you want all removals done in a single transaction.
61+
</para>
62+
</listitem>
63+
</varlistentry>
64+
5265
<varlistentry>
5366
<term><option>-U</option> <replaceable>username</></term>
5467
<listitem>
@@ -110,18 +123,19 @@ vacuumlo [options] database [database2 ... databaseN]
110123
<title>Method</title>
111124

112125
<para>
113-
First,it builds a temporary table which contains all of the OIDs of the
114-
large objects inthat database.
126+
First,<application>vacuumlo</> builds a temporary table which contains all
127+
of the OIDs of thelarge objects inthe selected database.
115128
</para>
116129

117130
<para>
118131
It then scans through all columns in the database that are of type
119132
<type>oid</> or <type>lo</>, and removes matching entries from the
120-
temporary table.
133+
temporary table. (Note: only types with these names are considered;
134+
in particular, domains over them are not considered.)
121135
</para>
122136

123137
<para>
124-
The remaining entries in thetemp table identify orphaned LOs.
138+
The remaining entries in thetemporary table identify orphaned LOs.
125139
These are removed.
126140
</para>
127141
</sect2>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp