Thread: temporarily stop autovacuum
Hi, Is there any way to stop autovacuum temporarily?(other than edit postgresql.conf and reload it) Pgpool-II does not want autovacuum running while doing "onlie recovery". -- Tatsuo Ishii SRA OSS, Inc. Japan
On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote: > Hi, > > Is there any way to stop autovacuum temporarily?(other than edit > postgresql.conf and reload it) Pgpool-II does not want autovacuum > running while doing "onlie recovery". It would be a significant hack but you could update pg_autovacuum to set all relations to false. Joshua D. Drake > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
> > Is there any way to stop autovacuum temporarily?(other than edit > > postgresql.conf and reload it) Pgpool-II does not want autovacuum > > running while doing "onlie recovery". > > It would be a significant hack but you could update pg_autovacuum to set > all relations to false. Thanks. Maybe it will be easier to talk to the autovacuum launcher daemon process directly? I will look in to the code. -- Tatsuo Ishii SRA OSS, Inc. Japan
Tatsuo Ishii wrote: > Hi, > > Is there any way to stop autovacuum temporarily?(other than edit > postgresql.conf and reload it) Hmm, no, that's the only way. I'm not sure that this calls for a change in autovacuum itself; it seems to be that whatwe really need is the ability to change postgresql.conf settings from the SQL interface. This has been discussed at length elsewhere, and I think we need to bite the bullet eventually.
Joshua D. Drake wrote: > On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote: >> Hi, >> >> Is there any way to stop autovacuum temporarily?(other than edit >> postgresql.conf and reload it) Pgpool-II does not want autovacuum >> running while doing "onlie recovery". > > It would be a significant hack but you could update pg_autovacuum to set > all relations to false. Which will no longer work in 8.4. More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility.
On Tue, Feb 10, 2009 at 8:53 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > I'm not sure that this calls for a change in autovacuum itself; it seems > to be that whatwe really need is the ability to change postgresql.conf > settings from the SQL interface. This has been discussed at length > elsewhere, and I think we need to bite the bullet eventually. I'd like to take a crack at identifying the bullet that needs to be bitten here: comments. People like to use comments to document old settings that they may once have had, and why they changed them, and we also ship comments that document the meaning of many of our settings. IIRC, much of the last round of this discussion centered on where new settings would be inserted into the file (which might involve trying to identify the commented-out version of that setting), whether to comment out the old line for a particular setting and insert a new line (or just replace the old line), what to do about comments on the same line as the GUC, etc. Any solution that we attempt to engineer this problem is unlikely to be able to pass the Turing test, and so it's likely to get some cases "wrong", as judged by the human intelligence of the person who wrote the comment that got masticated. If we resign ourselves to the fact that this will not work very well unless our postgresql.conf file is intended to be read and written primarily by machines, and only secondarily by humans when necessary to recover from a bad situation, we can make some progress. ...Robert
Peter Eisentraut wrote: > Joshua D. Drake wrote: >> It would be a significant hack but you could update pg_autovacuum to set >> all relations to false. > > Which will no longer work in 8.4. > > More generally, it was pointed out to me that users apparently do > updates of pg_autovacuum to change settings on a bunch of tables at > once. We might get some complaints if we remove that facility. Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that Itagaki-san was proposing. There's a problem however; for pg_autovacuum you used to need to insert some -1 values on columns on which you wanted to keep as defaults. On the new code you need to skip the value altogether, and a -1 is rejected with an error. Not sure how would we translate that.
On Wed, 2009-02-11 at 14:21 -0300, Alvaro Herrera wrote: > Peter Eisentraut wrote: > > Joshua D. Drake wrote: > > >> It would be a significant hack but you could update pg_autovacuum to set > >> all relations to false. > > > > Which will no longer work in 8.4. > > > > More generally, it was pointed out to me that users apparently do > > updates of pg_autovacuum to change settings on a bunch of tables at > > once. We might get some complaints if we remove that facility. I got plenty of complaints that aren't being fixed :). pg_dump doesn't even know how to deal with pg_autovacuum, changing scripts to handle their autovacuum modifications won't take much. > Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that > Itagaki-san was proposing. > I don't think so. A clean cut is the way to go. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Peter Eisentraut wrote:
>> More generally, it was pointed out to me that users apparently do
>> updates of pg_autovacuum to change settings on a bunch of tables at
>> once. We might get some complaints if we remove that facility.
> Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that
> Itagaki-san was proposing.
AFAIR we pointed out from day one that pg_autovacuum was a temporary
API that we were not promising to keep around. Anybody who was coding
against it with the expectation that they'd not have to change that code
later was willfully ignoring the warning label.
> There's a problem however; for pg_autovacuum you used to need to insert
> some -1 values on columns on which you wanted to keep as defaults. On
> the new code you need to skip the value altogether, and a -1 is rejected
> with an error. Not sure how would we translate that.
Maybe use a real table with an ON INSERT trigger that could contain some
actual logic? But it'd probably still have to be custom-tailored to
whatever application code was inserting things into pg_autovacuum,
so it's not clear there's much point to writing that instead of fixing
the application.
regards, tom lane
On Wed, Feb 11, 2009 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Peter Eisentraut wrote: >>> More generally, it was pointed out to me that users apparently do >>> updates of pg_autovacuum to change settings on a bunch of tables at >>> once. We might get some complaints if we remove that facility. > >> Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that >> Itagaki-san was proposing. > > AFAIR we pointed out from day one that pg_autovacuum was a temporary > API that we were not promising to keep around. Anybody who was coding > against it with the expectation that they'd not have to change that code > later was willfully ignoring the warning label. > >> There's a problem however; for pg_autovacuum you used to need to insert >> some -1 values on columns on which you wanted to keep as defaults. On >> the new code you need to skip the value altogether, and a -1 is rejected >> with an error. Not sure how would we translate that. > > Maybe use a real table with an ON INSERT trigger that could contain some > actual logic? But it'd probably still have to be custom-tailored to > whatever application code was inserting things into pg_autovacuum, > so it's not clear there's much point to writing that instead of fixing > the application. In any case it's not difficult to write a script that loops over all of your tables with ALTER TABLE. It's probably not as fast as a single UPDATE statement, but I suspect you'd need to have an enormous number of tables for that to matter much. ...Robert
Alvaro Herrera <alvherre@commandprompt.com> wrote: > I'm not sure that this calls for a change in autovacuum itself; it seems > to be that whatwe really need is the ability to change postgresql.conf > settings from the SQL interface. Sure. 'SET GLOBAL autovacuum = off' is a TODO item. I have another idea that autovacuum will use 'autovacuum role' to process tables. We don't need to add syntax because we already have per-database and per-role settings. Something like: ALTER ROLE autovacuum SET autovacuum = off; We also need to adjust those variable can be set on-the-fly, though. The current version of postgres doesn't allow to set them. ERROR: parameter "..." cannot be changed now Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
On Wednesday 11 February 2009 20:10:46 Tom Lane wrote: > AFAIR we pointed out from day one that pg_autovacuum was a temporary > API that we were not promising to keep around. Anybody who was coding > against it with the expectation that they'd not have to change that code > later was willfully ignoring the warning label. Indeed. I'm just saying, there is now no way to conveniently change the settings for many tables at once. This is perhaps the same kind of issue as GRANT SELECT ON ALL TABLES etc. that people occassionally ask for. Doing DDL on a group of tables at once.
--On Mittwoch, Februar 11, 2009 13:18:11 -0500 Robert Haas
<robertmhaas@gmail.com> wrote:
> In any case it's not difficult to write a script that loops over all
> of your tables with ALTER TABLE. It's probably not as fast as a
> single UPDATE statement, but I suspect you'd need to have an enormous
> number of tables for that to matter much.
Agreed, we often recommend this for all kinds of GRANTs, REVOKEs and so on.
But while we don't have (yet) any facility to achieve this behavior with
these commands, for autovacuum, a possible solution exists, and although a
crude temporarily one, i know people seeing pg_autovacuum as a feature to
do exactly this kind of maintenance.
-- Thanks
Bernd