From 67ccbb080d87a5379dffc1cdff046c4caf534a6c Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 15 Jul 2013 10:48:44 -0400
Subject: [PATCH] vacuumlo: Use a cursor to limit client-side memory usage.

This prevents the client from gobbling up too much memory when the
number of large objects to be removed is very large.

Andrew Dunstan, reviewed by Josh Kupershmidt
---
 contrib/vacuumlo/vacuumlo.c | 111 ++++++++++++++++++++++--------------
 1 file changed, 69 insertions(+), 42 deletions(-)

diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index 70f7ea70ba..134fd9d9c8 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -290,74 +290,101 @@ vacuumlo(const char *database, const struct _param * param)
 	PQclear(res);
 
 	buf[0] = '\0';
-	strcat(buf, "SELECT lo FROM vacuum_l");
+	strcat(buf,
+		   "DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l");
 	res = PQexec(conn, buf);
-	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	if (PQresultStatus(res) != PGRES_COMMAND_OK)
 	{
-		fprintf(stderr, "Failed to read temp table:\n");
-		fprintf(stderr, "%s", PQerrorMessage(conn));
+		fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
 		PQclear(res);
 		PQfinish(conn);
 		return -1;
 	}
+	PQclear(res);
+
+	snprintf(buf, BUFSIZE, "FETCH FORWARD %ld IN myportal",
+			 param->transaction_limit > 0 ? param->transaction_limit : 1000L);
 
-	matched = PQntuples(res);
 	deleted = 0;
-	for (i = 0; i < matched; i++)
+
+	while (1)
 	{
-		Oid			lo = atooid(PQgetvalue(res, i, 0));
+		res = PQexec(conn, buf);
+		if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			fprintf(stderr, "FETCH FORWARD failed: %s", PQerrorMessage(conn));
+			PQclear(res);
+			PQfinish(conn);
+			return -1;
+		}
 
-		if (param->verbose)
+		matched = PQntuples(res);
+		if (matched <= 0)
 		{
-			fprintf(stdout, "\rRemoving lo %6u   ", lo);
-			fflush(stdout);
+			/* at end of resultset */
+			PQclear(res);
+			break;
 		}
 
-		if (param->dry_run == 0)
+		for (i = 0; i < matched; i++)
 		{
-			if (lo_unlink(conn, lo) < 0)
+			Oid			lo = atooid(PQgetvalue(res, i, 0));
+
+			if (param->verbose)
+			{
+				fprintf(stdout, "\rRemoving lo %6u   ", lo);
+				fflush(stdout);
+			}
+
+			if (param->dry_run == 0)
 			{
-				fprintf(stderr, "\nFailed to remove lo %u: ", lo);
-				fprintf(stderr, "%s", PQerrorMessage(conn));
-				if (PQtransactionStatus(conn) == PQTRANS_INERROR)
+				if (lo_unlink(conn, lo) < 0)
 				{
-					success = false;
-					break;
+					fprintf(stderr, "\nFailed to remove lo %u: ", lo);
+					fprintf(stderr, "%s", PQerrorMessage(conn));
+					if (PQtransactionStatus(conn) == PQTRANS_INERROR)
+					{
+						success = false;
+						PQclear(res);
+						break;
+					}
 				}
+				else
+					deleted++;
 			}
 			else
 				deleted++;
-		}
-		else
-			deleted++;
-		if (param->transaction_limit > 0 &&
-			(deleted % param->transaction_limit) == 0)
-		{
-			res2 = PQexec(conn, "commit");
-			if (PQresultStatus(res2) != PGRES_COMMAND_OK)
+
+			if (param->transaction_limit > 0 &&
+				(deleted % param->transaction_limit) == 0)
 			{
-				fprintf(stderr, "Failed to commit transaction:\n");
-				fprintf(stderr, "%s", PQerrorMessage(conn));
+				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);
-				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));
+				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);
-				PQfinish(conn);
-				return -1;
 			}
-			PQclear(res2);
 		}
+
+		PQclear(res);
 	}
-	PQclear(res);
 
 	/*
 	 * That's all folks!
-- 
2.40.0