Re: vacuumlo - use a cursor - Mailing list pgsql-hackers

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. +