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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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