Robert,
VACUUM requires an exclusive lock on a table. This exclusive lock
includes reads (i.e. selects) as well as writes. Thus if you have
issued a SELECT statement and not committed after the select, the select
will lock the table and thus prevent the VACUUM from running. (I know
this because I just went through my code cleaning up this same problem).
As for the difference between 'idle' and 'idle in transaction', if
autocommit if false, then when the commit() method is called it
automatically starts the next transaction with a begin (it essentially
does: commit; begin;) thus 'idle in transaction' because a new
transaction is started. With autocommit true, there are no explicit
transactions (i.e. begin calls), thus it is just in an 'idle' state.
thanks,
--Barry
Robert Berger wrote:
> I have some code that's doing connection pooling for some servlets. I
> turn autoCommit off and let each caller determine when to commit.
>
> Hoever, commit() does not seem to be releasing read locks, causing
> my periodic VACUUM task to block.
>
> In the following code, after commit() is called, ps shoes the backend
> state as "idle in transaction". If I turn autocommit on, the state is
> "idle" Why the difference?
>
> import java.sql.*;
>
> public class ctest {
> public static void main(String argv[]) {
> try {
> String driverName = "org.postgresql.Driver";
> Class.forName(driverName).newInstance();
>
> Connection con = DriverManager.getConnection(
> "jdbc:postgresql://127.0.0.1/jackson","db","zzzz");
>
> con.setAutoCommit(false);
>
> Statement stmt = con.createStatement();
>
> ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM s_image");
> rs.next();
> int count = rs.getInt(1);
> rs.close();
>
> con.commit();
>
> System.out.println(count);
>
> Thread.sleep(30000);
>
>
> } catch (Exception e) { e.printStackTrace(); }
> }
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
