Re: PostgreSQL Query Speed Issues

On 2/22/2013 7:29 AM, Kevin Grittner wrote:
> If you haven't already done so, run VACUUM ANALYZE at the database
> level. If most of your data was loaded at about the same time, run
> VACUUM FREEZE ANALYZE instead, to prevent a read-and-rewrite pass
> of your entire database at peak OLTP load. Vacuum and analyze are
> routine maintenance that are necessary for the database to perform
> well. Autovacuum can often handle everything for you, but if you
> have an "off-hours" period when load is lower it is often a good
> idea to run a database VACUUM ANALYZE on a daily or weekly basis to
> shift maintenance load to time when it has the least impact. Never
> leave autovacuum disabled beyond a short maintenance or load
> window.
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

This is the error we have had that Tom posted about before.
(http://www.postgresql.org/message-id/5115B188.6090308@gmail.com) I
don't think Tom fully understood the issue, and nor did we. Over the
past few weeks, our team has been looking into this issue where our
application would just stop and hang. For the past couple months we've
been dealing with this, our solution was to go to everybody's
SquirrelSQL sessions and hit rollback. This fixes the issue every time.
Usually, users' who's session caused the hang couldn't remember what he
was doing, so we still don't know what exactly is causing the issue.

We did however figure out what the root cause is, as you can see above.
Every statement in the JDBC driver is ran inside of a transaction. This
is not a SquirrelSQL problem since a simple test program that only uses
the JDBC driver gives the same error. Yet when I run it in on the
command line (psql) it has no issues.

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.

Did I miss anything, maybe a setting somewhere in the JDBC driver that
can fix this issue? It makes it extremely difficult to run ad-hoc
queries since at anytime there is a possibility that some queries stop
responding.