From b69f2e36402aaa222ed03c1769b3de6d5be5f302 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 8 Aug 2011 09:12:16 -0400
Subject: [PATCH] Teach vacuumlo to limit number of removals, via new -l
option.
Also, handle failure better: don't just blindly keep trying to delete
stuff after the transaction has already failed.
Tim Lewis, reviewed by Josh Kupershmidt, with further hacking by me.
---
contrib/vacuumlo/vacuumlo.c | 43 +++++++++++++++++++++++++++++++------
doc/src/sgml/vacuumlo.sgml | 10 +++++++++
2 files changed, 47 insertions(+), 6 deletions(-)
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index f6e2a28cf3..6818d4ac43 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -48,6 +48,7 @@ struct _param
char *pg_host;
int verbose;
int dry_run;
+ long transaction_limit;
};
int vacuumlo(char *, struct _param *);
@@ -65,11 +66,12 @@ vacuumlo(char *database, struct _param * param)
PGresult *res,
*res2;
char buf[BUFSIZE];
- int matched;
- int deleted;
+ long matched;
+ long deleted;
int i;
static char *password = NULL;
bool new_pass;
+ bool success = true;
if (param->pg_prompt == TRI_YES && password == NULL)
password = simple_prompt("Password: ", 100, false);
@@ -280,12 +282,19 @@ vacuumlo(char *database, struct _param * param)
{
fprintf(stderr, "\nFailed to remove lo %u: ", lo);
fprintf(stderr, "%s", PQerrorMessage(conn));
+ if (PQtransactionStatus(conn) == PQTRANS_INERROR)
+ {
+ success = false;
+ break;
+ }
}
else
deleted++;
}
else
deleted++;
+ if (param->transaction_limit != 0 && deleted >= param->transaction_limit)
+ break;
}
PQclear(res);
@@ -298,10 +307,20 @@ vacuumlo(char *database, struct _param * param)
PQfinish(conn);
if (param->verbose)
- fprintf(stdout, "\r%s %d large objects from %s.\n",
- (param->dry_run ? "Would remove" : "Removed"), deleted, database);
+ {
+ if (param->dry_run)
+ fprintf(stdout, "\rWould remove %ld large objects from %s.\n",
+ deleted, database);
+ else if (success)
+ fprintf(stdout,
+ "\rSuccessfully removed %ld large objects from %s.\n",
+ deleted, database);
+ else
+ fprintf(stdout, "\rRemoval from %s failed at object %ld of %ld.\n",
+ database, deleted, matched);
+ }
- return 0;
+ return ((param->dry_run || success) ? 0 : -1);
}
void
@@ -311,6 +330,7 @@ usage(const char *progname)
printf("Usage:\n %s [OPTION]... DBNAME...\n\n", progname);
printf("Options:\n");
printf(" -h HOSTNAME database server host or socket directory\n");
+ printf(" -l LIMIT stop after removing LIMIT large objects\n");
printf(" -n don't remove large objects, just show what would be done\n");
printf(" -p PORT database server port\n");
printf(" -U USERNAME user name to connect as\n");
@@ -342,6 +362,7 @@ main(int argc, char **argv)
param.pg_port = NULL;
param.verbose = 0;
param.dry_run = 0;
+ param.transaction_limit = 0;
if (argc > 1)
{
@@ -359,7 +380,7 @@ main(int argc, char **argv)
while (1)
{
- c = getopt(argc, argv, "h:U:p:vnwW");
+ c = getopt(argc, argv, "h:l:U:p:vnwW");
if (c == -1)
break;
@@ -395,6 +416,16 @@ main(int argc, char **argv)
}
param.pg_port = strdup(optarg);
break;
+ case 'l':
+ param.transaction_limit = strtol(optarg, NULL, 10);
+ if (param.transaction_limit < 0)
+ {
+ fprintf(stderr,
+ "%s: transaction limit must not be negative (0 disables)\n",
+ progname);
+ exit(1);
+ }
+ break;
case 'h':
param.pg_host = strdup(optarg);
break;
diff --git a/doc/src/sgml/vacuumlo.sgml b/doc/src/sgml/vacuumlo.sgml
index 471a6ca32b..0ae39c71d2 100644
--- a/doc/src/sgml/vacuumlo.sgml
+++ b/doc/src/sgml/vacuumlo.sgml
@@ -56,6 +56,16 @@ vacuumlo [options] database [database2 ... databaseN]
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-l</option> <replaceable>limit</></term>
+ <listitem>
+ <para>
+ Stop after removing LIMIT large objects. Useful to avoid
+ exceeding <xref linkend="guc-max-locks-per-transaction">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-w</></term>
<term><option>--no-password</></term>
--
2.40.0