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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>