"Idle in Transaction" revisited. - Mailing list pgsql-jdbc

Our applications are factory floor messaging systems, mostly in Java, that
have persistence measured in weeks and even months, they run 24/7.  This
isn't WebApplet stuff, nor is it JavaBean stuff, its simple java programs
running on servers doing socket messaging and such.

Recently, I've steered the Java developers in my group towards Postgres on
Linux for specific applications rather than the $$$$ Sun/Oracle platforms my
department has traditionally used...

We're using locally-compiled pgsql 7.4.5 with the latest released jdbc, java
1.4.x on linux 2.4.x (typically redhat enterprise 2.1 or 3).

We've been running into a *lot* of problems with "Idle in Transaction"
threads causing long term blockage of VACUUM's ability to free up dead rows.
Our applications tend to be heavily multithreaded, often using a dozen or
more SQL connections for various purposes.

It took me quite a bit of detective work, with some assistance from the
pgsql-bugs list, and the #postgresql folks on freenode to get to the bottom
of this Idle in Transaction thing causing a problem when a JDBC thread was
doing nothing but periodic SELECT * FROM event_master;  without any commits
(and auto_commit=off).    Sorted that case out, but now are running into yet
more related issues...

One case in point, as a SQL form of 'mutex', one of these developers was
used to doing a 'UPDATE' on a particular row of a table without ever
committing to leave a row lock in place so that other threads won't attempt
to 'subscribe' to the same 'topic' on a corporate intranet messaging system.
We can't do this in Postgres (works great in Oracle) due to the 'idle in
transaction' issue...  as other threads and programs and databases on this
system are doing lots and LOTS of updates, we need hourly vacuuming or some
of our tables really slow down (one thread was doing around 60-90 updates
per second of the same 20 or so rows in a table to maintain highwater marks
to prevent duplicate record processing in cases of aborts, failures, and
abrupt restarts).


I've scanned and searched the messages on this forum, and only found a few
threads from back in April dealing with this sort of thing.

I'm curious what common practice is for threads that do nothing but
SELECTS... do folks just enable auto_commit, thereby preventing pgJDBC from
doing BEGIN; ?  Do they lace their code with COMMIT() calls?

What about a resource locking table like I described above, where we've got
a few dozen arbitrarily named resources we need to gain a mutex on?   When I
suggested 'select for update' to see if the resource was available, then
'update' to mark it in use, and commit, the developer said that leaves the
resource locked if the app crashes or the box is rebooted or whatever, while
his row lock would be cleared automatically.