Re: Improved vacuumlo command - Mailing list pgsql-patches

Patch applied.  Thanks.

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


Mario Weilguni wrote:
> I've improved the contributed vacuumlo command, now it behaves like all other
> postgres command line utilites e.g. supports -U, -p, -h, -?, -v, password
> prompt and has a "test mode". In test mode, no large objects are removed,
> just reported.
>
>
> Here's the patch:
> --- ../../../postgresql-7.2.1-orig/contrib/vacuumlo/vacuumlo.c    Mon Sep 17
> 04:30:54 2001
> +++ vacuumlo.c    Tue Apr 16 13:44:05 2002
> @@ -12,10 +12,16 @@
>   *
>   *-------------------------------------------------------------------------
>   */
> +
> +#include <pg_config.h>
>  #include <stdio.h>
>  #include <stdlib.h>
>  #include <string.h>
>
> +#ifdef HAVE_TERMIOS_H
> +#include <termios.h>
> +#endif
> +
>  #include <sys/types.h>
>  #include <sys/stat.h>
>  #include <fcntl.h>
> @@ -28,24 +34,163 @@
>
>  #define BUFSIZE            1024
>
> -int            vacuumlo(char *, int);
> +extern char *optarg;
> +extern int optind, opterr, optopt;
> +
> +struct _param {
> +        char *pg_user;
> +    int  pg_prompt;
> +    char *pg_port;
> +    char *pg_host;
> +    int  verbose;
> +    int  dry_run;
> +};
> +
> +int    vacuumlo(char *, struct _param *);
> +char    *simple_prompt(const char *prompt, int , int);
> +void    usage(void);
> +
> +
> +/*
> + * simple_prompt
> + *
> + * Generalized function especially intended for reading in usernames and
> + * password interactively. Reads from /dev/tty or stdin/stderr.
> + *
> + * prompt:        The prompt to print
> + * maxlen:        How many characters to accept
> + * echo:        Set to 0 if you want to hide what is entered (for passwords)
> + *
> + * Returns a malloc()'ed string with the input (w/o trailing newline).
> + */
> +static int prompt_state = 0;
> +
> +char *
> +simple_prompt(const char *prompt, int maxlen, int echo)
> +{
> +    int            length;
> +    char       *destination;
> +    FILE       *termin,
> +               *termout;
> +
> +#ifdef HAVE_TERMIOS_H
> +    struct termios t_orig,
> +                t;
> +#endif
> +
> +    destination = (char *) malloc(maxlen + 2);
> +    if (!destination)
> +        return NULL;
> +
> +    prompt_state = 1;        /* disable SIGINT */
> +
> +    /*
> +     * Do not try to collapse these into one "w+" mode file. Doesn't work
> +     * on some platforms (eg, HPUX 10.20).
> +     */
> +    termin = fopen("/dev/tty", "r");
> +    termout = fopen("/dev/tty", "w");
> +    if (!termin || !termout)
> +    {
> +        if (termin)
> +            fclose(termin);
> +        if (termout)
> +            fclose(termout);
> +        termin = stdin;
> +        termout = stderr;
> +    }
> +
> +#ifdef HAVE_TERMIOS_H
> +    if (!echo)
> +    {
> +        tcgetattr(fileno(termin), &t);
> +        t_orig = t;
> +        t.c_lflag &= ~ECHO;
> +        tcsetattr(fileno(termin), TCSAFLUSH, &t);
> +    }
> +#endif
> +
> +    if (prompt)
> +    {
> +        fputs(prompt, termout);
> +        fflush(termout);
> +    }
> +
> +    if (fgets(destination, maxlen, termin) == NULL)
> +        destination[0] = '\0';
> +
> +    length = strlen(destination);
> +    if (length > 0 && destination[length - 1] != '\n')
> +    {
> +        /* eat rest of the line */
> +        char        buf[128];
> +        int            buflen;
> +
> +        do
> +        {
> +            if (fgets(buf, sizeof(buf), termin) == NULL)
> +                break;
> +            buflen = strlen(buf);
> +        } while (buflen > 0 && buf[buflen - 1] != '\n');
> +    }
> +
> +    if (length > 0 && destination[length - 1] == '\n')
> +        /* remove trailing newline */
> +        destination[length - 1] = '\0';
> +
> +#ifdef HAVE_TERMIOS_H
> +    if (!echo)
> +    {
> +        tcsetattr(fileno(termin), TCSAFLUSH, &t_orig);
> +        fputs("\n", termout);
> +        fflush(termout);
> +    }
> +#endif
> +
> +    if (termin != stdin)
> +    {
> +        fclose(termin);
> +        fclose(termout);
> +    }
> +
> +    prompt_state = 0;        /* SIGINT okay again */
> +
> +    return destination;
> +}
> +
>
>
>  /*
>   * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
>   */
>  int
> -vacuumlo(char *database, int verbose)
> +vacuumlo(char *database, struct _param *param)
>  {
>      PGconn       *conn;
>      PGresult   *res,
> -               *res2;
> +           *res2;
>      char        buf[BUFSIZE];
> -    int            matched;
> -    int            deleted;
> -    int            i;
> +    int        matched;
> +    int        deleted;
> +    int        i;
> +    char            *password = NULL;
> +
> +        if(param->pg_prompt) {
> +                password = simple_prompt("Password: ", 32, 0);
> +        if(!password) {
> +                fprintf(stderr, "failed to get password\n");
> +                exit(1);
> +        }
> +    }
>
> -    conn = PQsetdb(NULL, NULL, NULL, NULL, database);
> +    conn = PQsetdbLogin( param->pg_host,
> +                         param->pg_port,
> +                             NULL,
> +                 NULL,
> +                 database,
> +                 param->pg_user,
> +                 password
> +                       );
>
>      /* check to see that the backend connection was successfully made */
>      if (PQstatus(conn) == CONNECTION_BAD)
> @@ -56,8 +201,11 @@
>          return -1;
>      }
>
> -    if (verbose)
> +    if (param->verbose) {
>          fprintf(stdout, "Connected to %s\n", database);
> +                if(param->dry_run)
> +                fprintf(stdout, "Test run: no large objects will be removed!\n");
> +    }
>
>      /*
>       * First we create and populate the LO temp table
> @@ -132,7 +280,7 @@
>          table = PQgetvalue(res, i, 0);
>          field = PQgetvalue(res, i, 1);
>
> -        if (verbose)
> +        if (param->verbose)
>              fprintf(stdout, "Checking %s in %s\n", field, table);
>
>          /*
> @@ -188,19 +336,22 @@
>      {
>          Oid            lo = atooid(PQgetvalue(res, i, 0));
>
> -        if (verbose)
> +        if (param->verbose)
>          {
>              fprintf(stdout, "\rRemoving lo %6u   ", lo);
>              fflush(stdout);
>          }
>
> -        if (lo_unlink(conn, lo) < 0)
> -        {
> -            fprintf(stderr, "\nFailed to remove lo %u: ", lo);
> -            fprintf(stderr, "%s", PQerrorMessage(conn));
> -        }
> -        else
> -            deleted++;
> +        if(param->dry_run == 0) {
> +                if (lo_unlink(conn, lo) < 0)
> +                {
> +                    fprintf(stderr, "\nFailed to remove lo %u: ", lo);
> +                    fprintf(stderr, "%s", PQerrorMessage(conn));
> +                }
> +                else
> +                    deleted++;
> +        } else
> +                deleted++;
>      }
>      PQclear(res);
>
> @@ -212,33 +363,95 @@
>
>      PQfinish(conn);
>
> -    if (verbose)
> -        fprintf(stdout, "\rRemoved %d large objects from %s.\n",
> -                deleted, database);
> +    if (param->verbose)
> +        fprintf(stdout, "\r%s %d large objects from %s.\n",
> +                (param->dry_run?"Would remove":"Removed"), deleted, database);
>
>      return 0;
>  }
>
> +void
> +usage(void) {
> +        fprintf(stdout, "vacuumlo removes unreferenced large objects from
> databases\n\n");
> +        fprintf(stdout, "Usage:\n  vacuumlo [options] dbname
> [dbnames...]\n\n");
> +        fprintf(stdout, "Options:\n");
> +    fprintf(stdout, "  -v\t\tWrite a lot of output\n");
> +    fprintf(stdout, "  -n\t\tDon't remove any large object, just show what would
> be done\n");
> +        fprintf(stdout, "  -U username\tUsername to connect as\n");
> +        fprintf(stdout, "  -W\t\tPrompt for password\n");
> +        fprintf(stdout, "  -h hostname\tDatabase server host\n");
> +    fprintf(stdout, "  -p port\tDatabase server port\n");
> +        fprintf(stdout, "  -p port\tDatabase server port\n\n");
> +}
> +
> +
>  int
>  main(int argc, char **argv)
>  {
> -    int            verbose = 0;
> -    int            arg;
>      int            rc = 0;
> -
> -    if (argc < 2)
> -    {
> -        fprintf(stderr, "Usage: %s [-v] database_name [db2 ... dbn]\n",
> -                argv[0]);
> +    struct _param           param;
> +    int                     c;
> +    int                     port;
> +
> +    /* Parameter handling */
> +        param.pg_user = NULL;
> +    param.pg_prompt = 0;
> +    param.pg_host = NULL;
> +    param.pg_port = 0;
> +        param.verbose = 0;
> +    param.dry_run = 0;
> +
> +        while( 1 ) {
> +      c = getopt(argc, argv, "?h:U:p:vnW");
> +      if(c == -1)
> +        break;
> +
> +      switch(c) {
> +            case '?':
> +          if(optopt == '?') {
> +            usage();
> +        exit(0);
> +          }
> +          exit(1);
> +        case ':':
> +          exit(1);
> +            case 'v':
> +          param.verbose = 1;
> +          break;
> +        case 'n':
> +          param.dry_run = 1;
> +          param.verbose = 1;
> +          break;
> +        case 'U':
> +          param.pg_user = strdup(optarg);
> +          break;
> +        case 'W':
> +          param.pg_prompt = 1;
> +          break;
> +        case 'p':
> +              port = strtol(optarg, NULL, 10);
> +          if( (port < 1) || (port > 65535)) {
> +            fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0],
> optarg);
>          exit(1);
> +          }
> +          param.pg_port = strdup(optarg);
> +          break;
> +        case 'h':
> +          param.pg_host = strdup(optarg);
> +          break;
> +      }
> +    }
> +
> +    /* No database given? Show usage */
> +    if(optind >= argc-1) {
> +      fprintf(stderr, "vacuumlo: missing required argument: database name\n");
> +      fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
> +      exit(1);
>      }
>
> -    for (arg = 1; arg < argc; arg++)
> -    {
> -        if (strcmp("-v", argv[arg]) == 0)
> -            verbose = !verbose;
> -        else
> -            rc += (vacuumlo(argv[arg], verbose) != 0);
> +        for(c = optind; c < argc; c++) {
> +      /* Work on selected database */
> +          rc += (vacuumlo(argv[c], ¶m) != 0);
>      }
>
>      return rc;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026