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

Commit64c6048

Browse files
committed
Improve the -l (limit) option recently added to contrib/vacuumlo.
Instead of just stopping after removing an arbitrary subset of orphanedlarge objects, commit and start a new transaction after each -l objects.This is just as effective as the original patch at limiting the number oflocks used, and it doesn't require doing the OID collection processrepeatedly to get everything. Since the option no longer changes thefundamental behavior of vacuumlo, and it avoids a known server-sidelimitation, enable it by default (with a default limit of 1000 LOs pertransaction).In passing, be more careful about properly quoting the names of tablesand fields, and do some other cosmetic cleanup.
1 parent9d23a70 commit64c6048

File tree

2 files changed

+109
-48
lines changed

2 files changed

+109
-48
lines changed

‎contrib/vacuumlo/vacuumlo.c

Lines changed: 94 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -29,8 +29,7 @@
2929

3030
externchar*optarg;
3131
externintoptind,
32-
opterr,
33-
optopt;
32+
opterr;
3433

3534
enumtrivalue
3635
{
@@ -50,16 +49,16 @@ struct _param
5049
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,
@@ -72,6 +71,7 @@ vacuumlo(char *database, struct _param * param)
7271
boolnew_pass;
7372
boolsuccess= true;
7473

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

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

120120
if (param->verbose)
121121
{
122-
fprintf(stdout,"Connected to%s\n",database);
122+
fprintf(stdout,"Connected todatabase \"%s\"\n",database);
123123
if (param->dry_run)
124124
fprintf(stdout,"Test run: no large objects will be removed!\n");
125125
}
@@ -220,9 +220,21 @@ vacuumlo(char *database, struct _param * param)
220220
if (param->verbose)
221221
fprintf(stdout,"Checking %s in %s.%s\n",field,schema,table);
222222

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+
223235
snprintf(buf,BUFSIZE,
224236
"DELETE FROM vacuum_l "
225-
"WHERE lo IN (SELECT\"%s\" FROM\"%s\".\"%s\")",
237+
"WHERE lo IN (SELECT%s FROM%s.%s)",
226238
field,schema,table);
227239
res2=PQexec(conn,buf);
228240
if (PQresultStatus(res2)!=PGRES_COMMAND_OK)
@@ -236,23 +248,35 @@ vacuumlo(char *database, struct _param * param)
236248
return-1;
237249
}
238250
PQclear(res2);
251+
252+
PQfreemem(schema);
253+
PQfreemem(table);
254+
PQfreemem(field);
239255
}
240256
PQclear(res);
241257

242258
/*
243-
* Run the actual deletes in a single transaction.Note that this would
244-
* be a bad idea in pre-7.1 Postgres releases (since rolling back a table
245-
* 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.
246266
*/
247267
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+
}
248276
PQclear(res);
249277

250-
/*
251-
* Finally, those entries remaining in vacuum_l are orphans.
252-
*/
253278
buf[0]='\0';
254-
strcat(buf,"SELECT lo ");
255-
strcat(buf,"FROM vacuum_l");
279+
strcat(buf,"SELECT lo FROM vacuum_l");
256280
res=PQexec(conn,buf);
257281
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
258282
{
@@ -292,44 +316,76 @@ vacuumlo(char *database, struct _param * param)
292316
}
293317
else
294318
deleted++;
295-
if (param->transaction_limit!=0&&deleted >=param->transaction_limit)
296-
break;
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+
}
297345
}
298346
PQclear(res);
299347

300348
/*
301349
* That's all folks!
302350
*/
303-
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+
}
304360
PQclear(res);
305361

306362
PQfinish(conn);
307363

308364
if (param->verbose)
309365
{
310366
if (param->dry_run)
311-
fprintf(stdout,"\rWould remove %ld large objects from%s.\n",
367+
fprintf(stdout,"\rWould remove %ld large objects fromdatabase \"%s\".\n",
312368
deleted,database);
313369
elseif (success)
314370
fprintf(stdout,
315-
"\rSuccessfully removed %ld large objects from%s.\n",
371+
"\rSuccessfully removed %ld large objects fromdatabase \"%s\".\n",
316372
deleted,database);
317373
else
318-
fprintf(stdout,"\rRemoval from%s failed at object %ld of %ld.\n",
374+
fprintf(stdout,"\rRemoval fromdatabase \"%s\" failed at object %ld of %ld.\n",
319375
database,deleted,matched);
320376
}
321377

322378
return ((param->dry_run||success) ?0 :-1);
323379
}
324380

325-
void
381+
staticvoid
326382
usage(constchar*progname)
327383
{
328384
printf("%s removes unreferenced large objects from databases.\n\n",progname);
329385
printf("Usage:\n %s [OPTION]... DBNAME...\n\n",progname);
330386
printf("Options:\n");
331387
printf(" -h HOSTNAME database server host or socket directory\n");
332-
printf(" -l LIMITstop after removing LIMIT large objects\n");
388+
printf(" -l LIMITcommit after removing each LIMIT large objects\n");
333389
printf(" -n don't remove large objects, just show what would be done\n");
334390
printf(" -p PORT database server port\n");
335391
printf(" -U USERNAME user name to connect as\n");
@@ -354,15 +410,16 @@ main(int argc, char **argv)
354410

355411
progname=get_progname(argv[0]);
356412

357-
/*Parameter handling */
413+
/*Set default parameter values */
358414
param.pg_user=NULL;
359415
param.pg_prompt=TRI_DEFAULT;
360416
param.pg_host=NULL;
361417
param.pg_port=NULL;
362418
param.verbose=0;
363419
param.dry_run=0;
364-
param.transaction_limit=0;
420+
param.transaction_limit=1000;
365421

422+
/* Process command-line arguments */
366423
if (argc>1)
367424
{
368425
if (strcmp(argv[1],"--help")==0||strcmp(argv[1],"-?")==0)
@@ -397,6 +454,16 @@ main(int argc, char **argv)
397454
param.dry_run=1;
398455
param.verbose=1;
399456
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;
400467
case'U':
401468
param.pg_user=strdup(optarg);
402469
break;
@@ -415,16 +482,6 @@ main(int argc, char **argv)
415482
}
416483
param.pg_port=strdup(optarg);
417484
break;
418-
case'l':
419-
param.transaction_limit=strtol(optarg,NULL,10);
420-
if (param.transaction_limit<0)
421-
{
422-
fprintf(stderr,
423-
"%s: transaction limit must not be negative (0 disables)\n",
424-
progname);
425-
exit(1);
426-
}
427-
break;
428485
case'h':
429486
param.pg_host=strdup(optarg);
430487
break;
@@ -435,7 +492,7 @@ main(int argc, char **argv)
435492
if (optind >=argc)
436493
{
437494
fprintf(stderr,"vacuumlo: missing required argument: database name\n");
438-
fprintf(stderr,"Try'vacuumlo -?' forhelp.\n");
495+
fprintf(stderr,_("Try\"%s --help\" formore information.\n"),progname);
439496
exit(1);
440497
}
441498

‎doc/src/sgml/vacuumlo.sgml

Lines changed: 15 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -50,19 +50,22 @@ vacuumlo [options] database [database2 ... databaseN]
5050
</varlistentry>
5151

5252
<varlistentry>
53-
<term><option>-U</option> <replaceable>username</></term>
53+
<term><option>-l</option> <replaceable>limit</></term>
5454
<listitem>
55-
<para>User name to connect as.</para>
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>
5662
</listitem>
5763
</varlistentry>
5864

5965
<varlistentry>
60-
<term><option>-l</option> <replaceable>limit</></term>
66+
<term><option>-U</option> <replaceable>username</></term>
6167
<listitem>
62-
<para>
63-
Stop after removing LIMIT large objects. Useful to avoid
64-
exceeding <xref linkend="guc-max-locks-per-transaction">.
65-
</para>
68+
<para>User name to connect as.</para>
6669
</listitem>
6770
</varlistentry>
6871

@@ -120,18 +123,19 @@ vacuumlo [options] database [database2 ... databaseN]
120123
<title>Method</title>
121124

122125
<para>
123-
First,it builds a temporary table which contains all of the OIDs of the
124-
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.
125128
</para>
126129

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

133137
<para>
134-
The remaining entries in thetemp table identify orphaned LOs.
138+
The remaining entries in thetemporary table identify orphaned LOs.
135139
These are removed.
136140
</para>
137141
</sect2>

0 commit comments

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