Re: vacuumlo - use a cursor

Is there a reason this patch was not applied?

---------------------------------------------------------------------------

On Mon, Nov 12, 2012 at 05:14:57PM -0500, Andrew Dunstan wrote:
> 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


-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +