Correction, I am using postgresql.x86_64 8.3.7-1.fc9 and postgresql-pgpool-II.x86_64 2.2.2-1.fc9 on Fedora 9.
On Thu, Feb 25, 2010 at 2:41 PM, Mike Bresnahan <[email protected]> wrote: > 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(); > } > } > _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
