Stored Procedure for Vacuum in PostgreSQL

Question

I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6).

The problem Im facing is, I can't run the vacuum inside the BEGIN/END so there is a workaround, we can use set autocommit=on.

But when I add this autocommit inside the stored procedure, its throwing error.

Sample SP:

create procedure test(table_list varchar(1000))
LANGUAGE plpgsql 
AS 
$$ 
DECLARE 
*** some values ***
BEGIN 
EXECUTE 'set autocommit=on';
*** Rest of the command to get the table names one by one and vacuum in a loop***
END;
$$

Is this possible? Or any alternate way?

Answer 1

vacuum cannot run in a transaction and cannot run in a function or in a procedure.

You can try instead to generate the vacuum statements with the list of tables to be processed with psql CLI and \gexec internal command to run them.

With:

select format('vacuum %s.%s', schemaname, tablename) 
from (select schemaname, tablename
     from pg_tables where tablename like 't%' 
) lt;
\gexec

Execution says:

select format('vacuum %s.%s', schemaname, tablename) 
from (select schemaname, tablename
     from pg_tables where tablename like 't%' 
) lt;
           format           
----------------------------
 vacuum public.ta
 vacuum private.t
 vacuum public.test
 vacuum s1.t
 vacuum s2.t
 vacuum public.tasks2020_04
 vacuum public.t1
 vacuum public.t2
 vacuum public.t0
 vacuum public.t3
 vacuum public.tts
 vacuum public.t0_0
 vacuum public.tasks
 vacuum public.tasks2020_05
 vacuum public.tasks2020_06
 vacuum public.tasks2020_07
 vacuum pub.t
 vacuum public.t
 vacuum public.tb
(19 rows)

select format('vacuum %s.%s', schemaname, tablename) 
from (select schemaname, tablename
     from pg_tables where tablename like 't%' 
) lt;
vacuum public.ta
VACUUM
vacuum private.t
VACUUM
vacuum public.test
VACUUM
vacuum s1.t
VACUUM
vacuum s2.t
VACUUM
vacuum public.tasks2020_04
VACUUM
vacuum public.t1
VACUUM
vacuum public.t2
VACUUM
vacuum public.t0
VACUUM
vacuum public.t3
VACUUM
vacuum public.tts
VACUUM
vacuum public.t0_0
VACUUM
vacuum public.tasks
VACUUM
vacuum public.tasks2020_05
VACUUM
vacuum public.tasks2020_06
VACUUM
vacuum public.tasks2020_07
VACUUM
vacuum pub.t
VACUUM
vacuum public.t
VACUUM
vacuum public.tb
VACUUM