[Pgpool-general] JDBC autocommit and hanging locks

[Mike Bresnahan]

I am using the PostgreSQL 8.4 JDBC driver to connect to a pgpool
instance in replication mode fronting a pair of PostgreSQL 8.4
servers. When autocommit is true (the default), select statements
leave shared locks hanging in PostgreSQL until the connection is
closed. The following test case illustrates the problem. Test1
succeeds, but test2 deadlocks. When I look in pg_locks when test2 is
deadlocked I see that connection1 is holding a shared lock on the
table "layer". It appears that the autocommit mechanism is not working
properly when going through pgpool. Any help would be greatly
appreciated.

import org.junit.*;
import org.postgresql.jdbc3.Jdbc3PoolingDataSource;

public class PGPoolBugTest {
	
	@Before
	public void before() throws Exception {
		Class.forName("org.postgresql.Driver");
		java.sql.Connection connection =
java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
"zzz", "qqq");
		java.sql.Statement statement = connection.createStatement();
		statement.executeUpdate("truncate table layer");
		statement.executeUpdate("insert into layer values (1,'foo',1,1)");
	}
	
	@Test
	public void test1() throws Exception {
		java.sql.Connection connection1 =
java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
"zzz", "qqq");
		java.sql.Connection connection2 =
java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
"zzz", "qqq");
		connection1.setAutoCommit(false);
		java.sql.Statement statement1 = connection1.createStatement();
		java.sql.Statement statement2 = connection2.createStatement();
		java.sql.ResultSet resultSet1 = statement1.executeQuery("select *
from layer");
		while (resultSet1.next());
		resultSet1.close();
		statement1.close();
		connection1.commit();
		statement2.executeUpdate("truncate table layer");
		connection1.close();
		connection2.close();
	}
	
	@Test
	public void test2() throws Exception {
		java.sql.Connection connection1 =
java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
"zzz", "qqq");
		java.sql.Connection connection2 =
java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
"zzz", "qqq");
		java.sql.Statement statement1 = connection1.createStatement();
		java.sql.Statement statement2 = connection2.createStatement();
		java.sql.ResultSet resultSet1 = statement1.executeQuery("select *
from layer");
		while (resultSet1.next());
		resultSet1.close();
		statement1.close();
		statement2.executeUpdate("truncate table layer");
		connection1.close();
		connection2.close();
	}
}