Sample Header Ad - 728x90

Stored Procedure for Vacuum in PostgreSQL

0 votes
1 answer
3604 views
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?
Asked by TheDataGuy (1986 rep)
Apr 11, 2020, 08:27 PM
Last activity: Jun 9, 2025, 04:04 PM