I downloaded and compiled pgpool-II-2.3.2.2 and observed the following behavior:
replication_mode = true load_balance_mode = true replicate_select = true The select statement is sent to both servers and the sync message is sent to one server. Deadlock occurs. replication_mode = true load_balance_mode = true replicate_select = false The parse message is sent to both servers, the select statement is sent to one server, and the sync message is sent to one server. Deadlock occurs. replication_mode = true load_balance_mode = false replicate_select = false The parse message is sent to one server, the select statement is sent to one server, and the sync message is sent to one server. Test is successful, i.e. no deadlock. I also downloaded, compiled, and tested pgpool 3.4.1 and it does not exhibit the bug. Therefore, I think I have discovered a serious bug in pgpool-II that is caused when both replication and load balance modes are enabled. I looked over the source code and it is not obvious to me where the bug is. I'm guessing there is a logic error in the handling of the global variable in_load_balance. Any help would be greatly appreciated. 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
