13.06.2019, 13:57, "Michael Paquier" <mich...@paquier.xyz>:
> On Thu, Jun 13, 2019 at 01:25:38PM +0600, Timur Birsh wrote:
>> Just to be sure, a new command line argument needs to be added for
>> reporting the numbers? Should it implies --verbose argument?
>
> Nope. I mean that running a SELECT count(*) can be costly for many
> items.
Understood, thanks.
I found a way to get the number of LOs that will be removed without
the SELECT count(*) - PQcmdTuples(). Please find attached patch v2.
I fixed some indentation in the variable declaration blocks.
There is a database with tables that have a lot of tuples with large objects:
# select count(*) from pg_largeobject_metadata;
count
----------
44707424
(1 row)
An application that uses this database from time to time deletes and adds a lot
of rows, it happens that more than 10,000,000 orphaned LOs remain in the
database. Removing such a number of items takes a long time.
I guess, it would be helpful to know how many LOs going to be removed and
report deleted percentage.
Thanks,
Timur
From 2350a42ec46ab511020060b3f4427848628b0ecb Mon Sep 17 00:00:00 2001
From: Timur Birsh <t...@linukz.org>
Date: Fri, 14 Jun 2019 04:31:28 +0000
Subject: [PATCH] Report the number of large objects going to be removed
---
contrib/vacuumlo/vacuumlo.c | 29 +++++++++++++++++++----------
1 file changed, 19 insertions(+), 10 deletions(-)
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 73c06a043e..77e91ff7ad 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -60,17 +60,18 @@ static void usage(const char *progname);
static int
vacuumlo(const char *database, const struct _param *param)
{
- PGconn *conn;
- PGresult *res,
- *res2;
+ PGconn *conn;
+ PGresult *res,
+ *res2;
char buf[BUFSIZE];
+ long to_delete = 0;
long matched;
long deleted;
- int i;
+ int i;
bool new_pass;
bool success = true;
- static bool have_password = false;
- static char password[100];
+ static bool have_password = false;
+ static char password[100];
/* Note: password can be carried over from a previous call */
if (param->pg_prompt == TRI_YES && !have_password)
@@ -171,6 +172,7 @@ vacuumlo(const char *database, const struct _param *param)
PQfinish(conn);
return -1;
}
+ to_delete = strtol(PQcmdTuples(res), NULL, 10);
PQclear(res);
/*
@@ -221,9 +223,10 @@ vacuumlo(const char *database, const struct _param *param)
for (i = 0; i < PQntuples(res); i++)
{
- char *schema,
- *table,
- *field;
+ char *schema,
+ *table,
+ *field;
+ long excluded;
schema = PQgetvalue(res, i, 0);
table = PQgetvalue(res, i, 1);
@@ -268,7 +271,9 @@ vacuumlo(const char *database, const struct _param *param)
PQfreemem(field);
return -1;
}
+ excluded = strtol(PQcmdTuples(res2), NULL, 10);
PQclear(res2);
+ to_delete -= excluded;
PQfreemem(schema);
PQfreemem(table);
@@ -276,6 +281,9 @@ vacuumlo(const char *database, const struct _param *param)
}
PQclear(res);
+ if (param->verbose)
+ fprintf(stdout, "%ld large objects will be removed\n", to_delete);
+
/*
* Now, those entries remaining in vacuum_l are orphans. Delete 'em.
*
@@ -339,7 +347,8 @@ vacuumlo(const char *database, const struct _param *param)
if (param->verbose)
{
- fprintf(stdout, "\rRemoving lo %6u ", lo);
+ fprintf(stdout, "\rRemoving lo %6u\t(%3.f%%)", lo,
+ ((float) deleted / (float) to_delete) * 100);
fflush(stdout);
}
--
2.17.1
