0

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?

  • set autocommit=on is a psql command, you can use that inside a PL/pgSQL function – a_horse_with_no_name Apr 11 at 20:43
  • How about vacuum? Can we use this inside the procedure? – Bhuvanesh Apr 12 at 4:41
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.