Redshift VACUUM cannot run inside a transaction block on SQLWorkbenchJ

I have got a:

VACUUM cannot run inside a transaction block

error on SQLWorkbenchJ in Redshift, but I already commit all transactions before this.

3 Answers

You don't need to change the connection profile, you can change the autocommit property inside your SQL script "on-the-fly" with set autocommit

set autocommit on; vacuum; set autocommit off; 

You can also toggle the current autocommit state through the menu "SQL -> Autocommit"

For me this worked.

END TRANSACTION; VACCUM <TABLENAME>; 

turning autocommit on and off seems like a hacky solution particularly if you have a long script punctuated with commits and vacuums (ie lots of very large temp tables). Instead, try (in one line). Also, many are reporting redshift does not like the syntax. Instead,

COMMIT;VACUUM;COMMIT; 

The problem is that vacuum not only wants to be the first command in a transaction block, it wants the block to be explicitly committed after.