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
Last activity: Jun 9, 2025, 04:04 PM