Re: pg_migrator issues - Mailing list pgsql-hackers

Alvaro Herrera wrote:
> > 3)  There is no easy way to analyze all databases.  vacuumdb --analyze
> > does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> > unnecessary vacuum.  Right now I recommend ANALYZE in every database,
> > but it would be nice if there were a single command which did this.
>
> +1 for vacuumdb --analyze-only

OK, I have implemented this using --only-analyze to avoid having the
'--anal' option spelling be ambiguous, which might confuse/frustrate
users.

I also moved the --freeze option documention mention into a more logical
place.

Patch attached.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/vacuumdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/vacuumdb.sgml,v
retrieving revision 1.45
diff -c -c -r1.45 vacuumdb.sgml
*** doc/src/sgml/ref/vacuumdb.sgml    27 Nov 2009 17:41:26 -0000    1.45
--- doc/src/sgml/ref/vacuumdb.sgml    4 Jan 2010 17:21:34 -0000
***************
*** 24,32 ****
     <command>vacuumdb</command>
     <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
     <group><arg>--full</arg><arg>-f</arg></group>
     <group><arg>--verbose</arg><arg>-v</arg></group>
     <group><arg>--analyze</arg><arg>-z</arg></group>
!    <group><arg>--freeze</arg><arg>-F</arg></group>
     <arg>--table | -t <replaceable>table</replaceable>
      <arg>( <replaceable class="parameter">column</replaceable> [,...] )</arg>
     </arg>
--- 24,33 ----
     <command>vacuumdb</command>
     <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
     <group><arg>--full</arg><arg>-f</arg></group>
+    <group><arg>--freeze</arg><arg>-F</arg></group>
     <group><arg>--verbose</arg><arg>-v</arg></group>
     <group><arg>--analyze</arg><arg>-z</arg></group>
!    <group><arg>--only-analyze</arg><arg>-o</arg></group>
     <arg>--table | -t <replaceable>table</replaceable>
      <arg>( <replaceable class="parameter">column</replaceable> [,...] )</arg>
     </arg>
***************
*** 36,44 ****
     <arg rep="repeat"><replaceable>connection-options</replaceable></arg>
     <group><arg>--all</arg><arg>-a</arg></group>
     <group><arg>--full</arg><arg>-f</arg></group>
     <group><arg>--verbose</arg><arg>-v</arg></group>
     <group><arg>--analyze</arg><arg>-z</arg></group>
!    <group><arg>--freeze</arg><arg>-F</arg></group>
    </cmdsynopsis>
   </refsynopsisdiv>

--- 37,46 ----
     <arg rep="repeat"><replaceable>connection-options</replaceable></arg>
     <group><arg>--all</arg><arg>-a</arg></group>
     <group><arg>--full</arg><arg>-f</arg></group>
+    <group><arg>--freeze</arg><arg>-F</arg></group>
     <group><arg>--verbose</arg><arg>-v</arg></group>
     <group><arg>--analyze</arg><arg>-z</arg></group>
!    <group><arg>--only-analyze</arg><arg>-o</arg></group>
    </cmdsynopsis>
   </refsynopsisdiv>

***************
*** 56,63 ****
    <para>
     <application>vacuumdb</application> is a wrapper around the SQL
     command <xref linkend="SQL-VACUUM" endterm="SQL-VACUUM-title">.
!    There is no effective difference between vacuuming databases via
!    this utility and via other methods for accessing the server.
    </para>

   </refsect1>
--- 58,66 ----
    <para>
     <application>vacuumdb</application> is a wrapper around the SQL
     command <xref linkend="SQL-VACUUM" endterm="SQL-VACUUM-title">.
!    There is no effective difference between vacuuming and analyzing
!    databases via this utility and via other methods for accessing the
!    server.
    </para>

   </refsect1>
***************
*** 117,122 ****
--- 120,145 ----
       </varlistentry>

       <varlistentry>
+       <term><option>-F</option></term>
+       <term><option>--freeze</option></term>
+       <listitem>
+        <para>
+         Aggressively <quote>freeze</quote> tuples.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
+       <term><option>-o</option></term>
+       <term><option>--only-analyze</option></term>
+       <listitem>
+        <para>
+         Only calculate statistics for use by the optimizer (no vacuum).
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>-q</></term>
        <term><option>--quiet</></term>
        <listitem>
***************
*** 133,139 ****
         <para>
          Clean or analyze <replaceable class="parameter">table</replaceable> only.
          Column names can be specified only in conjunction with
!         the <option>--analyze</option> option.
         </para>
         <tip>
          <para>
--- 156,162 ----
         <para>
          Clean or analyze <replaceable class="parameter">table</replaceable> only.
          Column names can be specified only in conjunction with
!         the <option>--analyze</option> or <option>--only-analyze</option> options.
         </para>
         <tip>
          <para>
***************
*** 164,178 ****
        </listitem>
       </varlistentry>

-      <varlistentry>
-       <term><option>-F</option></term>
-       <term><option>--freeze</option></term>
-       <listitem>
-        <para>
-         Aggressively <quote>freeze</quote> tuples.
-        </para>
-       </listitem>
-      </varlistentry>
      </variablelist>
     </para>

--- 187,192 ----
Index: src/bin/scripts/vacuumdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v
retrieving revision 1.28
diff -c -c -r1.28 vacuumdb.c
*** src/bin/scripts/vacuumdb.c    2 Jan 2010 16:58:00 -0000    1.28
--- src/bin/scripts/vacuumdb.c    4 Jan 2010 17:21:34 -0000
***************
*** 14,25 ****
  #include "common.h"


! static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
!                     bool freeze, const char *table,
!                     const char *host, const char *port,
                      const char *username, enum trivalue prompt_password,
                      const char *progname, bool echo);
! static void vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze,
                       const char *host, const char *port,
                       const char *username, enum trivalue prompt_password,
                       const char *progname, bool echo, bool quiet);
--- 14,26 ----
  #include "common.h"


! static void vacuum_one_database(const char *dbname, bool full, bool verbose,
!                     bool and_analyze, bool only_analyze, bool freeze,
!                     const char *table, const char *host, const char *port,
                      const char *username, enum trivalue prompt_password,
                      const char *progname, bool echo);
! static void vacuum_all_databases(bool full, bool verbose, bool and_analyze,
!                      bool only_analyze, bool freeze,
                       const char *host, const char *port,
                       const char *username, enum trivalue prompt_password,
                       const char *progname, bool echo, bool quiet);
***************
*** 40,45 ****
--- 41,47 ----
          {"quiet", no_argument, NULL, 'q'},
          {"dbname", required_argument, NULL, 'd'},
          {"analyze", no_argument, NULL, 'z'},
+         {"only-analyze", no_argument, NULL, 'o'},
          {"freeze", no_argument, NULL, 'F'},
          {"all", no_argument, NULL, 'a'},
          {"table", required_argument, NULL, 't'},
***************
*** 59,65 ****
      enum trivalue prompt_password = TRI_DEFAULT;
      bool        echo = false;
      bool        quiet = false;
!     bool        analyze = false;
      bool        freeze = false;
      bool        alldb = false;
      char       *table = NULL;
--- 61,68 ----
      enum trivalue prompt_password = TRI_DEFAULT;
      bool        echo = false;
      bool        quiet = false;
!     bool        and_analyze = false;
!     bool        only_analyze = false;
      bool        freeze = false;
      bool        alldb = false;
      char       *table = NULL;
***************
*** 100,106 ****
                  dbname = optarg;
                  break;
              case 'z':
!                 analyze = true;
                  break;
              case 'F':
                  freeze = true;
--- 103,112 ----
                  dbname = optarg;
                  break;
              case 'z':
!                 and_analyze = true;
!                 break;
!             case 'o':
!                 only_analyze = true;
                  break;
              case 'F':
                  freeze = true;
***************
*** 139,144 ****
--- 145,167 ----

      setup_cancel_handler();

+     if (only_analyze)
+     {
+         if (full)
+         {
+             fprintf(stderr, _("%s: cannot use the \"full\" option when performing only analyze\n"),
+                     progname);
+             exit(1);
+         }
+         if (freeze)
+         {
+             fprintf(stderr, _("%s: cannot use the \"freeze\" option when performing only analyze\n"),
+                     progname);
+             exit(1);
+         }
+         /* ignore 'and_analyze' */
+     }
+
      if (alldb)
      {
          if (dbname)
***************
*** 154,160 ****
              exit(1);
          }

!         vacuum_all_databases(full, verbose, analyze, freeze,
                               host, port, username, prompt_password,
                               progname, echo, quiet);
      }
--- 177,183 ----
              exit(1);
          }

!         vacuum_all_databases(full, verbose, and_analyze, only_analyze, freeze,
                               host, port, username, prompt_password,
                               progname, echo, quiet);
      }
***************
*** 170,176 ****
                  dbname = get_user_name(progname);
          }

!         vacuum_one_database(dbname, full, verbose, analyze, freeze, table,
                              host, port, username, prompt_password,
                              progname, echo);
      }
--- 193,200 ----
                  dbname = get_user_name(progname);
          }

!         vacuum_one_database(dbname, full, verbose, and_analyze, only_analyze,
!                             freeze, table,
                              host, port, username, prompt_password,
                              progname, echo);
      }
***************
*** 180,187 ****


  static void
! vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
!                     bool freeze, const char *table,
                      const char *host, const char *port,
                      const char *username, enum trivalue prompt_password,
                      const char *progname, bool echo)
--- 204,211 ----


  static void
! vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
!                     bool only_analyze, bool freeze, const char *table,
                      const char *host, const char *port,
                      const char *username, enum trivalue prompt_password,
                      const char *progname, bool echo)
***************
*** 192,206 ****

      initPQExpBuffer(&sql);

!     appendPQExpBuffer(&sql, "VACUUM");
!     if (full)
!         appendPQExpBuffer(&sql, " FULL");
!     if (freeze)
!         appendPQExpBuffer(&sql, " FREEZE");
      if (verbose)
          appendPQExpBuffer(&sql, " VERBOSE");
-     if (analyze)
-         appendPQExpBuffer(&sql, " ANALYZE");
      if (table)
          appendPQExpBuffer(&sql, " %s", table);
      appendPQExpBuffer(&sql, ";\n");
--- 216,235 ----

      initPQExpBuffer(&sql);

!     if (only_analyze)
!         appendPQExpBuffer(&sql, "ANALYZE");
!     else
!     {
!         appendPQExpBuffer(&sql, "VACUUM");
!         if (full)
!             appendPQExpBuffer(&sql, " FULL");
!         if (freeze)
!             appendPQExpBuffer(&sql, " FREEZE");
!         if (and_analyze)
!             appendPQExpBuffer(&sql, " ANALYZE");
!     }
      if (verbose)
          appendPQExpBuffer(&sql, " VERBOSE");
      if (table)
          appendPQExpBuffer(&sql, " %s", table);
      appendPQExpBuffer(&sql, ";\n");
***************
*** 223,230 ****


  static void
! vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze,
!                      const char *host, const char *port,
                       const char *username, enum trivalue prompt_password,
                       const char *progname, bool echo, bool quiet)
  {
--- 252,259 ----


  static void
! vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool only_analyze,
!                      bool freeze, const char *host, const char *port,
                       const char *username, enum trivalue prompt_password,
                       const char *progname, bool echo, bool quiet)
  {
***************
*** 246,253 ****
              fflush(stdout);
          }

!         vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL,
!                             host, port, username, prompt_password,
                              progname, echo);
      }

--- 275,282 ----
              fflush(stdout);
          }

!         vacuum_one_database(dbname, full, verbose, and_analyze, only_analyze,
!                             freeze, NULL, host, port, username, prompt_password,
                              progname, echo);
      }

***************
*** 267,272 ****
--- 296,302 ----
      printf(_("  -e, --echo                      show the commands being sent to the server\n"));
      printf(_("  -f, --full                      do full vacuuming\n"));
      printf(_("  -F, --freeze                    freeze row transaction information\n"));
+     printf(_("  -o, --only-analyze              only update optimizer hints\n"));
      printf(_("  -q, --quiet                     don't write any messages\n"));
      printf(_("  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table only\n"));
      printf(_("  -v, --verbose                   write a lot of output\n"));