Thread: vacuumdb --freeze
I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. I could also accomplish with with PGOPTIONs but this seem like a cleaner solution. -- 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.42 diff -c -c -r1.42 vacuumdb.sgml *** doc/src/sgml/ref/vacuumdb.sgml 11 Dec 2007 19:57:32 -0000 1.42 --- doc/src/sgml/ref/vacuumdb.sgml 17 Feb 2009 16:24:39 -0000 *************** *** 26,31 **** --- 26,32 ---- <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> *************** *** 37,42 **** --- 38,44 ---- <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> *************** *** 161,166 **** --- 163,178 ---- </para> </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> Index: src/bin/scripts/vacuumdb.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v retrieving revision 1.22 diff -c -c -r1.22 vacuumdb.c *** src/bin/scripts/vacuumdb.c 1 Jan 2009 17:23:55 -0000 1.22 --- src/bin/scripts/vacuumdb.c 17 Feb 2009 16:24:39 -0000 *************** *** 15,25 **** static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, ! const char *table, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo); ! static void vacuum_all_databases(bool full, bool verbose, bool analyze, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo, bool quiet); --- 15,25 ---- 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, bool 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, bool password, const char *progname, bool echo, bool quiet); *************** *** 39,44 **** --- 39,45 ---- {"quiet", no_argument, NULL, 'q'}, {"dbname", required_argument, NULL, 'd'}, {"analyze", no_argument, NULL, 'z'}, + {"freeze", no_argument, NULL, 'F'}, {"all", no_argument, NULL, 'a'}, {"table", required_argument, NULL, 't'}, {"full", no_argument, NULL, 'f'}, *************** *** 58,63 **** --- 59,65 ---- bool echo = false; bool quiet = false; bool analyze = false; + bool freeze = false; bool alldb = false; char *table = NULL; bool full = false; *************** *** 68,74 **** handle_help_version_opts(argc, argv, "vacuumdb", help); ! while ((c = getopt_long(argc, argv, "h:p:U:Weqd:zat:fv", long_options, &optindex)) != -1) { switch (c) { --- 70,76 ---- handle_help_version_opts(argc, argv, "vacuumdb", help); ! while ((c = getopt_long(argc, argv, "h:p:U:Weqd:zaFt:fv", long_options, &optindex)) != -1) { switch (c) { *************** *** 96,101 **** --- 98,106 ---- case 'z': analyze = true; break; + case 'F': + freeze = true; + break; case 'a': alldb = true; break; *************** *** 145,151 **** exit(1); } ! vacuum_all_databases(full, verbose, analyze, host, port, username, password, progname, echo, quiet); } --- 150,156 ---- exit(1); } ! vacuum_all_databases(full, verbose, analyze, freeze, host, port, username, password, progname, echo, quiet); } *************** *** 161,167 **** dbname = get_user_name(progname); } ! vacuum_one_database(dbname, full, verbose, analyze, table, host, port, username, password, progname, echo); } --- 166,172 ---- dbname = get_user_name(progname); } ! vacuum_one_database(dbname, full, verbose, analyze, freeze, table, host, port, username, password, progname, echo); } *************** *** 172,178 **** static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, ! const char *table, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo) --- 177,183 ---- 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, bool password, const char *progname, bool echo) *************** *** 190,195 **** --- 195,202 ---- appendPQExpBuffer(&sql, " VERBOSE"); if (analyze) appendPQExpBuffer(&sql, " ANALYZE"); + if (freeze) + appendPQExpBuffer(&sql, " FREEZE"); if (table) appendPQExpBuffer(&sql, " %s", table); appendPQExpBuffer(&sql, ";\n"); *************** *** 212,218 **** static void ! vacuum_all_databases(bool full, bool verbose, bool analyze, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo, bool quiet) --- 219,225 ---- static void ! vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo, bool quiet) *************** *** 235,241 **** fflush(stdout); } ! vacuum_one_database(dbname, full, verbose, analyze, NULL, host, port, username, password, progname, echo); } --- 242,248 ---- fflush(stdout); } ! vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL, host, port, username, password, progname, echo); } *************** *** 256,261 **** --- 263,269 ---- printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table only\n")); printf(_(" -f, --full do full vacuuming\n")); printf(_(" -z, --analyze update optimizer hints\n")); + printf(_(" -F, --freeze freeze row transaction information\n")); printf(_(" -e, --echo show the commands being sent to the server\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" -v, --verbose write a lot of output\n"));
Bruce Momjian <bruce@momjian.us> writes:
> I would like to add a --freeze parameter to vacuumdb for use by the
> binary upgrade utility, and for symmetry with the existing VACUUM
> options; patch attached.
Exactly what do you think the upgrade utility is going to do with it?
Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade
is going to be fast.
As far as I can see this is a solution looking for a problem.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > I would like to add a --freeze parameter to vacuumdb for use by the > > binary upgrade utility, and for symmetry with the existing VACUUM > > options; patch attached. > > Exactly what do you think the upgrade utility is going to do with it? > Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade > is going to be fast. > > As far as I can see this is a solution looking for a problem. I didn't go into the use-case. The way pg_migrator works is to copy the _schema_ from the old database and load it into the new database. We then need to run vacuum freeze on the schema-only databases because we then move pg_clog from the old database to the new one; so, it is needed, and it will not take long to run. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> > > I would like to add a --freeze parameter to vacuumdb for use by the > > > binary upgrade utility, and for symmetry with the existing VACUUM > > > options; patch attached. > > > > Exactly what do you think the upgrade utility is going to do with it? > > Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade > > is going to be fast. > > > > As far as I can see this is a solution looking for a problem. > > I didn't go into the use-case. The way pg_migrator works is to copy the > _schema_ from the old database and load it into the new database. We > then need to run vacuum freeze on the schema-only databases because we > then move pg_clog from the old database to the new one; so, it is > needed, and it will not take long to run. My first impulse was the same as Tom's, thanks for the explanation. To the filled database case: Would it make sense to enhance --table to allow wildcards and remove the "cannot vacuum a specific table in all databases" check ? One more question I have though is: How do you make sure noone (e.g. autovacuum analyze) unfreezes tuples after the vacuum freeze ? Andreas
On Tue, 2009-02-17 at 18:52 -0500, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > I would like to add a --freeze parameter to vacuumdb for use by the > > > binary upgrade utility, and for symmetry with the existing VACUUM > > > options; patch attached. > > > > Exactly what do you think the upgrade utility is going to do with it? > > Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade > > is going to be fast. > > > > As far as I can see this is a solution looking for a problem. > > I didn't go into the use-case. The way pg_migrator works is to copy the > _schema_ from the old database and load it into the new database. We > then need to run vacuum freeze on the schema-only databases because we > then move pg_clog from the old database to the new one; so, it is > needed, and it will not take long to run. So you don't actually want to VACUUM the whole database anyway, just the system tables? I'd like to see VACUUM SYSTEM, just like we have REINDEX SYSTEM. That way you can then do a --freeze --system on vacuumdb, which is all you want to do anyway. If the code is there for REINDEX SYSTEM it should be pretty easy to move it across to VACUUM. I've had times when I just wanted to VACUUM the catalog tables, so to go through them all one by one is tedious and missing one isn't possible with a special command. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Bruce Momjian wrote: > I would like to add a --freeze parameter to vacuumdb for use by the > binary upgrade utility, and for symmetry with the existing VACUUM > options; patch attached. > > I could also accomplish with with PGOPTIONs but this seem like a cleaner > solution. Applied. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Zeugswetter Andreas OSB sIT wrote: > > > > > I would like to add a --freeze parameter to vacuumdb for use by the > > > > binary upgrade utility, and for symmetry with the existing VACUUM > > > > options; patch attached. > > > > > > Exactly what do you think the upgrade utility is going to do with it? > > > Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade > > > is going to be fast. > > > > > > As far as I can see this is a solution looking for a problem. > > > > I didn't go into the use-case. The way pg_migrator works is to copy the > > _schema_ from the old database and load it into the new database. We > > then need to run vacuum freeze on the schema-only databases because we > > then move pg_clog from the old database to the new one; so, it is > > needed, and it will not take long to run. > > My first impulse was the same as Tom's, thanks for the explanation. > > To the filled database case: > > Would it make sense to enhance --table to allow wildcards and remove the > "cannot vacuum a specific table in all databases" check ? > > One more question I have though is: > How do you make sure noone (e.g. autovacuum analyze) > unfreezes tuples after the vacuum freeze ? I will start a new thread to answer this question, but the short answer is that the freeze only needs to happen in a fresh initdb database, and once clog is copied over, new transactions can be created normally. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
The FREEZ option is deprecated. See http://www.postgresql.org/docs/8.3/interactive/sql-vacuum.html you should use vacuum_freeze_min_age instead. On other side it breaks vacuumdb backward compatibility which we did not declare, but it could be fine. Zdenek Bruce Momjian píše v út 17. 02. 2009 v 11:58 -0500: > I would like to add a --freeze parameter to vacuumdb for use by the > binary upgrade utility, and for symmetry with the existing VACUUM > options; patch attached. > > I could also accomplish with with PGOPTIONs but this seem like a cleaner > solution. > > Prostý textový dokument příloha (/pgpatches/vacuumdb) > Index: doc/src/sgml/ref/vacuumdb.sgml > =================================================================== > RCS file: /cvsroot/pgsql/doc/src/sgml/ref/vacuumdb.sgml,v > retrieving revision 1.42 > diff -c -c -r1.42 vacuumdb.sgml > *** doc/src/sgml/ref/vacuumdb.sgml 11 Dec 2007 19:57:32 -0000 1.42 > --- doc/src/sgml/ref/vacuumdb.sgml 17 Feb 2009 16:24:39 -0000 > *************** > *** 26,31 **** > --- 26,32 ---- > <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> > *************** > *** 37,42 **** > --- 38,44 ---- > <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> > > *************** > *** 161,166 **** > --- 163,178 ---- > </para> > </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> > > Index: src/bin/scripts/vacuumdb.c > =================================================================== > RCS file: /cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v > retrieving revision 1.22 > diff -c -c -r1.22 vacuumdb.c > *** src/bin/scripts/vacuumdb.c 1 Jan 2009 17:23:55 -0000 1.22 > --- src/bin/scripts/vacuumdb.c 17 Feb 2009 16:24:39 -0000 > *************** > *** 15,25 **** > > > static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, > ! const char *table, > const char *host, const char *port, > const char *username, bool password, > const char *progname, bool echo); > ! static void vacuum_all_databases(bool full, bool verbose, bool analyze, > const char *host, const char *port, > const char *username, bool password, > const char *progname, bool echo, bool quiet); > --- 15,25 ---- > > > 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, bool 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, bool password, > const char *progname, bool echo, bool quiet); > *************** > *** 39,44 **** > --- 39,45 ---- > {"quiet", no_argument, NULL, 'q'}, > {"dbname", required_argument, NULL, 'd'}, > {"analyze", no_argument, NULL, 'z'}, > + {"freeze", no_argument, NULL, 'F'}, > {"all", no_argument, NULL, 'a'}, > {"table", required_argument, NULL, 't'}, > {"full", no_argument, NULL, 'f'}, > *************** > *** 58,63 **** > --- 59,65 ---- > bool echo = false; > bool quiet = false; > bool analyze = false; > + bool freeze = false; > bool alldb = false; > char *table = NULL; > bool full = false; > *************** > *** 68,74 **** > > handle_help_version_opts(argc, argv, "vacuumdb", help); > > ! while ((c = getopt_long(argc, argv, "h:p:U:Weqd:zat:fv", long_options, &optindex)) != -1) > { > switch (c) > { > --- 70,76 ---- > > handle_help_version_opts(argc, argv, "vacuumdb", help); > > ! while ((c = getopt_long(argc, argv, "h:p:U:Weqd:zaFt:fv", long_options, &optindex)) != -1) > { > switch (c) > { > *************** > *** 96,101 **** > --- 98,106 ---- > case 'z': > analyze = true; > break; > + case 'F': > + freeze = true; > + break; > case 'a': > alldb = true; > break; > *************** > *** 145,151 **** > exit(1); > } > > ! vacuum_all_databases(full, verbose, analyze, > host, port, username, password, > progname, echo, quiet); > } > --- 150,156 ---- > exit(1); > } > > ! vacuum_all_databases(full, verbose, analyze, freeze, > host, port, username, password, > progname, echo, quiet); > } > *************** > *** 161,167 **** > dbname = get_user_name(progname); > } > > ! vacuum_one_database(dbname, full, verbose, analyze, table, > host, port, username, password, > progname, echo); > } > --- 166,172 ---- > dbname = get_user_name(progname); > } > > ! vacuum_one_database(dbname, full, verbose, analyze, freeze, table, > host, port, username, password, > progname, echo); > } > *************** > *** 172,178 **** > > static void > vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, > ! const char *table, > const char *host, const char *port, > const char *username, bool password, > const char *progname, bool echo) > --- 177,183 ---- > > 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, bool password, > const char *progname, bool echo) > *************** > *** 190,195 **** > --- 195,202 ---- > appendPQExpBuffer(&sql, " VERBOSE"); > if (analyze) > appendPQExpBuffer(&sql, " ANALYZE"); > + if (freeze) > + appendPQExpBuffer(&sql, " FREEZE"); > if (table) > appendPQExpBuffer(&sql, " %s", table); > appendPQExpBuffer(&sql, ";\n"); > *************** > *** 212,218 **** > > > static void > ! vacuum_all_databases(bool full, bool verbose, bool analyze, > const char *host, const char *port, > const char *username, bool password, > const char *progname, bool echo, bool quiet) > --- 219,225 ---- > > > static void > ! vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze, > const char *host, const char *port, > const char *username, bool password, > const char *progname, bool echo, bool quiet) > *************** > *** 235,241 **** > fflush(stdout); > } > > ! vacuum_one_database(dbname, full, verbose, analyze, NULL, > host, port, username, password, > progname, echo); > } > --- 242,248 ---- > fflush(stdout); > } > > ! vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL, > host, port, username, password, > progname, echo); > } > *************** > *** 256,261 **** > --- 263,269 ---- > printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table only\n")); > printf(_(" -f, --full do full vacuuming\n")); > printf(_(" -z, --analyze update optimizer hints\n")); > + printf(_(" -F, --freeze freeze row transaction information\n")); > printf(_(" -e, --echo show the commands being sent to the server\n")); > printf(_(" -q, --quiet don't write any messages\n")); > printf(_(" -v, --verbose write a lot of output\n"));
Zdenek Kotala wrote: > The FREEZ option is deprecated. See > > http://www.postgresql.org/docs/8.3/interactive/sql-vacuum.html I mentioned PGOPTIONS as a suggestion when I posted but on one said that was a good idea; they just wanted to know why I needed this functionality. > you should use vacuum_freeze_min_age instead. On other side it breaks > vacuumdb backward compatibility which we did not declare, but it could > be fine. How does adding a flag break backward compatibiity? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian píše v čt 19. 02. 2009 v 08:08 -0500:
> > you should use vacuum_freeze_min_age instead. On other side it
> breaks
> > vacuumdb backward compatibility which we did not declare, but it
> could
> > be fine.
>
> How does adding a flag break backward compatibiity?
>
I meant that vacuum_freeze_min_age was introduced in 8.2 and cannot be
used for older database. if you use it you cannot run vacuumdb freeze on
8.1 and older. See Peter's list of backward compatible applications.
Zdenek
Zdenek Kotala wrote: > > Bruce Momjian p??e v ?t 19. 02. 2009 v 08:08 -0500: > > > you should use vacuum_freeze_min_age instead. On other side it > > breaks > > > vacuumdb backward compatibility which we did not declare, but it > > could > > > be fine. > > > > How does adding a flag break backward compatibiity? > > > > I meant that vacuum_freeze_min_age was introduced in 8.2 and cannot be > used for older database. if you use it you cannot run vacuumdb freeze on > 8.1 and older. See Peter's list of backward compatible applications. Oh, I can only upgrade 8.3+ so that would not be a problem. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> > One more question I have though is: > > How do you make sure noone (e.g. autovacuum analyze) > > unfreezes tuples after the vacuum freeze ? > > I will start a new thread to answer this question, but the short answer > is that the freeze only needs to happen in a fresh initdb database, and > once clog is copied over, new transactions can be created normally. Yes. I am still answering here because my question was related to upgrade. I think you need to turn off autovacuum before freezing to avoid a later analyze that unfreezes pg_class (or the stats table). Also to really have all tables frozen I think the order of the freezes is crutial. Consider the order: freeze pg_class freeze pg_columns --> won't this unfreeze pg_class ? And what about shared catalogs ? Or does heap_update not change the xid ? Or can it use a frozen xid ? Or does it all work when done in one large transaction ? I think I am confused, sorry :-( Andreas
Zeugswetter Andreas OSB sIT <Andreas.Zeugswetter@s-itsolutions.at> writes:
> I am still answering here because my question was related to upgrade.
> I think you need to turn off autovacuum before freezing to avoid a later analyze
> that unfreezes pg_class (or the stats table).
vacuum analyze doesn't unfreeze pg_class. It could create unfrozen
tuples in pg_statistic, perhaps, but we could easily fix that by
truncating pg_statistic afterwards (its not like there will be useful
data there...)
The end goal is going to be to have all this work happen in a standalone
backend, rather than risk firing up the postmaster while the database is
in an unstable state. So I would counsel spending as little effort as
possible on filing off rough edges that are related to the
using-a-postmaster scenario.
regards, tom lane
Tom Lane wrote: > Zeugswetter Andreas OSB sIT <Andreas.Zeugswetter@s-itsolutions.at> writes: > > I am still answering here because my question was related to upgrade. > > I think you need to turn off autovacuum before freezing to avoid a later analyze > > that unfreezes pg_class (or the stats table). > > vacuum analyze doesn't unfreeze pg_class. It could create unfrozen > tuples in pg_statistic, perhaps, but we could easily fix that by > truncating pg_statistic afterwards (its not like there will be useful > data there...) I have added --analyze to the vacuumdb command and documented its purpose. > The end goal is going to be to have all this work happen in a standalone > backend, rather than risk firing up the postmaster while the database is > in an unstable state. So I would counsel spending as little effort as > possible on filing off rough edges that are related to the > using-a-postmaster scenario. Any idea how to do that? Would we have to leave the libpq API? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> vacuum analyze doesn't unfreeze pg_class. It could create unfrozen
>> tuples in pg_statistic, perhaps, but we could easily fix that by
>> truncating pg_statistic afterwards (its not like there will be useful
>> data there...)
> I have added --analyze to the vacuumdb command and documented its
> purpose.
Surely that's backwards? What's the point of doing analyze work you'll
have to throw away?
>> The end goal is going to be to have all this work happen in a standalone
>> backend,
> Any idea how to do that?
I didn't say it was easy.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> vacuum analyze doesn't unfreeze pg_class. It could create unfrozen > >> tuples in pg_statistic, perhaps, but we could easily fix that by > >> truncating pg_statistic afterwards (its not like there will be useful > >> data there...) > > > I have added --analyze to the vacuumdb command and documented its > > purpose. > > Surely that's backwards? What's the point of doing analyze work you'll > have to throw away? Hmmm, that is true; removed. > >> The end goal is going to be to have all this work happen in a standalone > >> backend, > > > Any idea how to do that? > > I didn't say it was easy. Well, seeing as we want something for 8.4, I figured you had a plan. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > Tom Lane wrote: > > >> vacuum analyze doesn't unfreeze pg_class. It could create unfrozen > > >> tuples in pg_statistic, perhaps, but we could easily fix that by > > >> truncating pg_statistic afterwards (its not like there will be useful > > >> data there...) > > > > > I have added --analyze to the vacuumdb command and documented its > > > purpose. > > > > Surely that's backwards? What's the point of doing analyze work you'll > > have to throw away? > > Hmmm, that is true; removed. I thought a little more and it seems best to make valid pg_statistics entries rather than have entries who's xid status changes after moving the new clog into place. Hopefully autovacuum will pick up on analyzing the new database, and I will add a mention to the README: Optimizer statistcs information is not transfered as part of the upgradeso you should run 'vacuumdb --all --analyze'. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +