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.

After turn on 'Autocommit' at connection window, 'vacuum' can running normally. — Ting Jia, Nov 30 '15 at 08:39

score 21 · Accepted Answer · edited Dec 25 '19 at 20:21

21

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"

edited Dec 25 '19 at 20:21

GilZ

6,418
5
30
40

answered Nov 30 '15 at 08:45

2

Hi , I got a error message when I run these 3 sql commands in SQLWorkBenchJ : ERROR: syntax error at or near "on" Position: 17 – Ting Jia Dec 01 '15 at 09:53
@TingJia: works for me. Maybe you are using an old version of SQL Workbench. – Dec 02 '15 at 09:50
Supposedly I am not doing any kind of transaction & I want to execute only VACCUM & ANALYZE through Redshift JDBC, is it possible?? – Akki May 26 '17 at 12:20
@Akki: As I said: turn on autocommit – May 26 '17 at 12:22
'set autocommit on;' is invalid syntax, at least on redshift. instead use: " commit; vacuum; commit; " in one line – Chris.Caldwell Sep 19 '17 at 21:05
@Chris.Caldwell: but SQL Workbench supports that (see the link in my answer) – Sep 20 '17 at 05:44
I got the " ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "on" " error. – Jie Aug 20 '19 at 21:59

score 13 · Answer 2 · answered Jul 31 '17 at 18:20

13

For me this worked.

END TRANSACTION;
VACCUM <TABLENAME>;

answered Jul 31 '17 at 18:20

user 923227

2,528
4
27
46

Chris.Caldwell · Answer 3 · 2017-09-19T21:07:42.620

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.

Redshift VACUUM cannot run inside a transaction block on SQLWorkbenchJ

3 Answers3