vacuumlo is rather simpleminded about dealing with the list of LOs
to be removed - it just fetches them as a straight resultset. For
one of my our this resulted in an out of memory condition. The
attached patch tries to remedy that by using a cursor instead. If
this is wanted I will add it to the next commitfest. The actualy
changes are very small - most of the patch is indentation changes
due to the introduction of an extra loop.
cheers
andrew
*** a/contrib/vacuumlo/vacuumlo.c
--- b/contrib/vacuumlo/vacuumlo.c
***************
*** 290,362 **** vacuumlo(const char *database, const struct _param * param)
PQclear(res);
buf[0] = '\0';
! strcat(buf, "SELECT lo FROM vacuum_l");
! res = PQexec(conn, buf);
! if (PQresultStatus(res) != PGRES_TUPLES_OK)
! {
! fprintf(stderr, "Failed to read temp table:\n");
! fprintf(stderr, "%s", PQerrorMessage(conn));
! PQclear(res);
PQfinish(conn);
return -1;
! }
- matched = PQntuples(res);
deleted = 0;
! for (i = 0; i < matched; i++)
{
! Oid lo = atooid(PQgetvalue(res, i, 0));
! if (param->verbose)
{
! fprintf(stdout, "\rRemoving lo %6u ", lo);
! fflush(stdout);
}
! if (param->dry_run == 0)
{
! if (lo_unlink(conn, lo) < 0)
{
! 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) == 0)
! {
! res2 = PQexec(conn, "commit");
! if (PQresultStatus(res2) != PGRES_COMMAND_OK)
{
! fprintf(stderr, "Failed to commit
transaction:\n");
! fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
! PQclear(res);
! PQfinish(conn);
! return -1;
! }
! PQclear(res2);
! res2 = PQexec(conn, "begin");
! if (PQresultStatus(res2) != PGRES_COMMAND_OK)
! {
! fprintf(stderr, "Failed to start
transaction:\n");
! fprintf(stderr, "%s", PQerrorMessage(conn));
PQclear(res2);
- PQclear(res);
- PQfinish(conn);
- return -1;
}
- PQclear(res2);
}
}
PQclear(res);
/*
--- 290,389 ----
PQclear(res);
buf[0] = '\0';
! strcat(buf,
! "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM
vacuum_l");
! res = PQexec(conn, buf);
! if (PQresultStatus(res) != PGRES_COMMAND_OK)
! {
! fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
! PQclear(res);
PQfinish(conn);
return -1;
! }
! PQclear(res);
!
! snprintf(buf, BUFSIZE, "FETCH FORWARD " INT64_FORMAT " IN myportal",
! param->transaction_limit > 0 ?
param->transaction_limit : 1000);
deleted = 0;
!
! while (1)
{
! res = PQexec(conn, buf);
! if (PQresultStatus(res) != PGRES_TUPLES_OK)
! {
! fprintf(stderr, "Failed to read temp table:\n");
! fprintf(stderr, "%s", PQerrorMessage(conn));
! PQclear(res);
! PQfinish(conn);
! return -1;
! }
! matched = PQntuples(res);
!
! if (matched <= 0)
{
! /* at end of resultset */
! break;
}
! for (i = 0; i < matched; i++)
{
! Oid lo = atooid(PQgetvalue(res, i,
0));
!
! if (param->verbose)
! {
! fprintf(stdout, "\rRemoving lo %6u ", lo);
! fflush(stdout);
! }
!
! if (param->dry_run == 0)
{
! if (lo_unlink(conn, lo) < 0)
{
! 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) == 0)
{
! res2 = PQexec(conn, "commit");
! if (PQresultStatus(res2) != PGRES_COMMAND_OK)
! {
! fprintf(stderr, "Failed to commit
transaction:\n");
! fprintf(stderr, "%s",
PQerrorMessage(conn));
! PQclear(res2);
! PQclear(res);
! PQfinish(conn);
! return -1;
! }
PQclear(res2);
! res2 = PQexec(conn, "begin");
! if (PQresultStatus(res2) != PGRES_COMMAND_OK)
! {
! fprintf(stderr, "Failed to start
transaction:\n");
! fprintf(stderr, "%s",
PQerrorMessage(conn));
! PQclear(res2);
! PQclear(res);
! PQfinish(conn);
! return -1;
! }
PQclear(res2);
}
}
}
+
PQclear(res);
/*
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
