Re: vacuumlo - use a cursor

Nobody seemed interested. But I do think it's a good idea still.

cheers

andrew



On 06/29/2013 11:23 AM, Bruce Momjian wrote:
> 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
>