Redshift VACUUM cannot run inside a transaction block on SQLWorkbenchJ

Question

I have got a:

VACUUM cannot run inside a transaction block

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

Answer 1

For me this worked.

END TRANSACTION;
VACCUM <TABLENAME>;

Answer 2

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"

Answer 3

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.