How to disable the autovacuum ?

## Esteban Zimanyi (ezimanyi@ulb.ac.be):

I have tried
alter system set autovacuum = off;
but it does not seem to work.

Did you reload the configuration ("SELECT pg_reload_conf()" etc) after
that? If not, that's your problem right there.

Regards,
Christoph

--
Spare Space

Dear Christoph

Many thanks for your prompt reply !

Is there a step-by-step procedure specified somewhere?

For example, before launching the tests there is a load.sql file that loads
all the test tables. The file starts as follows

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;

--
-- Name: tbl_tbool; Type: TABLE; Schema: public; Owner: -
--

DROP TABLE IF EXISTS public.tbl_tbool;
CREATE TABLE public.tbl_tbool (
k integer,
temp tbool
);
ALTER TABLE tbl_tbool SET (autovacuum_enabled = false);

[... many more table definitions added after which the load of these tables
starts ...]

COPY public.tbl_tbool (k,temp) FROM stdin;
1 f@2001-05-31 20:25:00+02
2 f@2001-06-13 00:50:00+02
[...]
\.

[... load of the other tables ...]

I wonder whether this is the best way to do it, or whether it is better to
disable the autovacuum at the beginning for all the tests

Thanks for your help !

On Mon, May 31, 2021 at 10:47 AM Christoph Moench-Tegeder <
cmt@burggraben.net> wrote:

Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:

Any idea how to disable the autovacuum during the regression and coverage
tests for the MobilityDB extension ?

TBH, this seems like a pretty bad idea. If your extension doesn't
behave stably with autovacuum it's not going to be much use in the
real world.

In the core tests, we sometimes disable autovac for individual
tables using a per-table storage option, but that's a last resort.

regards, tom lane

Many thanks Tom for your feedback. I appreciate it.

Actually the tests work in parallel with autovacuum, I just wanted to
minimize the test time since the autovacuum launches in the middle of the
many regression and robustness tests. But then I follow your advice.

Regards

Esteban

On Mon, May 31, 2021 at 3:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

## Esteban Zimanyi (ezimanyi@ulb.ac.be):

Is there a step-by-step procedure specified somewhere?

The first step is not to disable autovacuum... (why would you want to
do that?).

For example, before launching the tests there is a load.sql file that loads
all the test tables. The file starts as follows

SET statement_timeout = 0;

That's all session parameters.
The general principle here is: We have parameters which can be set
inside a session - "SET ..." - or even inside a transaction - "SET LOCAL"
- and reset again ("RESET ..."). These parameters are also in the
server configuration - I like to think of those settings as the defaults
for new sessions (except when overridden on a user/database/function
level, etc.).
Other parameters have to be set on the server level - that is, added
to the configuration file(s). ("ALTER SYSTEM" is just a way to add
configuration directives via the postgresql.auto.conf file). Changes
the the configuration files become active after a server reload (or
restart, of course).
Some of these settings can only be set on server start (not reload)
or even have to match the data directory.

In any case, the documentation is very clear if a restart is
required for changing a parameter, e.g. in
https://www.postgresql.org/docs/13/runtime-config-autovacuum.html
"This parameter can only be set in the postgresql.conf file or on the
server command line"

And some parameters can be set as storage parameters on a per-object
(table, index, ...) base, but that's the next can of worms.

The takeaways of this are:
1. ALTER SYSTEM only edits the configuration, reloads and restarts have
to be handled by the operator as usual.
Documentation: https://www.postgresql.org/docs/13/sql-altersystem.html
"Values set with ALTER SYSTEM will be effective after the next server
configuration reload, or after the next server restart in the case of
parameters that can only be changed at server start. A server
configuration reload can be commanded by calling the SQL function
pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal
to the main server process."
2. Different parameters can have different contexts, which you should
be aware of when changing them. Session and object ("storage")
parameters take their defaults from the server configuration.
Check parameter documentation and the pg_settings view (documented
here: https://www.postgresql.org/docs/13/view-pg-settings.html )
for parameter contexts.
3. Don't disable autovacuum unless you really know what you're doing.

Gruß,
Christoph

--
Spare Space