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.

Arcuate

After turn on 'Autocommit' at connection window, 'vacuum' can running normally. – Arcuate

Answer 1

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"

Bill

Hi , I got a error message when I run these 3 sql commands in SQLWorkBenchJ : ERROR: syntax error at or near "on" Position: 17 – Arcuate

@TingJia: works for me. Maybe you are using an old version of SQL Workbench. – Bill

Supposedly I am not doing any kind of transaction & I want to execute only VACCUM & ANALYZE through Redshift JDBC, is it possible?? – Limousine

@Akki: As I said: turn on autocommit – Bill

'set autocommit on;' is invalid syntax, at least on redshift. instead use: " commit; vacuum; commit; " in one line – Jalapa

@Chris.Caldwell: but SQL Workbench supports that (see the link in my answer) – Bill

I got the " ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "on" " error. – Educe

Answer 2

For me this worked.

END TRANSACTION;
VACCUM <TABLENAME>;

Willow

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.

Jalapa