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.
