Re: PostgreSQL Query Speed Issues

Joseph Pravato wrote on 26.02.2013 18:19:
> We use SquirrelSQL to talk to our databases with the Postgres
> 9.2-1002 JDBC driver. When I tried to run `VACUUM FREEZE ANALYZE', It
> came back with this error:

>Error: ERROR: VACUUM cannot run inside a transaction block SQLState: 25001 ErrorCode: 0

Simply turn on "Autocommit" in Squirrel to get around this.

> What gets particularly confusing with this issue is when your query
> is wrong in any way, you are required to rollback the transaction.
> Worse yet, you have to re-run any prior statements in the transaction
> if you have any errors. The easiest solution is to turn auto-commit
> on and hope that you never seriously screw up data. But, we are not
> going to do that.

That can be solved by using savepoints. For my SQL tool (SQL Workbench/J) I have implemented this so that each statement that is run interactively is guarded with a savepoint. Upon an error, the tool automatically rolls back to the savepoint, leaving the transaction "intact" and letting you continue without the manual need to rollback (possibly losing changes you did before that).

Without autocommit you can however not run vacuum. The workaround for that would be to turn off autocommit temporarily inside your SQL client. I don't know if that is possible in Squirel though (again I added that ability to SQL Workbench/J using "set autocommit on/off" - which is not a standard Postgres statement. There, you could run the script:

set autocommit on;
VACUUM FREEZE ANALYZE;
set autcommit off;

If you want to look at my tool, here it is: http://www.sql-workbench.net

Regards
Thomas