Tatsuo, I am getting this on pgpool-II 2.2.2. I don't have any case studies ready at this moment. But here are the data_logs below in the mail which might help you. Three statements were passed S_1, S_2, S_3, but when the user disconnected only S_1, S_3 got DEALLOCATED. Same happened at the end when S_1 and S_3 were prepared after disconnection only S_1 got DEALLOCATED. Will this be causing the problem.
*2009-06-17 03:15:07 PDT [11025]: [3-1] LOG: duration: 0.437 ms parse S_1: BEGIN* 2009-06-17 03:15:07 PDT [11025]: [4-1] LOG: duration: 0.009 ms bind S_1: BEGIN *2009-06-17 03:15:07 PDT [11025]: [13-1] LOG: duration: 0.009 ms parse S_2: COMMIT* 2009-06-17 03:15:13 PDT [11025]: [14-1] LOG: duration: 0.042 ms bind S_1: BEGIN *2009-06-17 03:16:10 PDT [11025]: [8071-1] LOG: duration: 0.010 ms parse S_3: ROLLBACK* 2009-06-17 03:20:12 PDT [11025]: [43176-1] LOG: duration: 0.025 ms statement: BEGIN 2009-06-17 03:20:12 PDT [11025]: [43177-1] LOG: duration: 0.019 ms statement: RESET ALL 2009-06-17 03:20:12 PDT [11025]: [43178-1] LOG: duration: 0.106 ms statement: COMMIT 2009-06-17 03:20:12 PDT [11025]: [43179-1] LOG: duration: 0.009 ms statement: BEGIN 2009-06-17 03:20:12 PDT [11025]: [43180-1] LOG: duration: 0.021 ms statement: SET SESSION AUTHORIZATION DEFAULT 2009-06-17 03:20:12 PDT [11025]: [43181-1] LOG: duration: 0.011 ms statement: COMMIT 2009-06-17 03:20:12 PDT [11025]: [43182-1] LOG: duration: 0.007 ms statement: BEGIN *2009-06-17 03:20:12 PDT [11025]: [43183-1] LOG: duration: 0.019 ms statement: DEALLOCATE "S_1"* 2009-06-17 03:20:12 PDT [11025]: [43184-1] LOG: duration: 0.009 ms statement: COMMIT 2009-06-17 03:20:12 PDT [11025]: [43185-1] LOG: duration: 0.007 ms statement: BEGIN *2009-06-17 03:20:12 PDT [11025]: [43186-1] LOG: duration: 0.010 ms statement: DEALLOCATE "S_3"* 2009-06-17 03:20:12 PDT [11025]: [43187-1] LOG: duration: 0.009 ms statement: COMMIT 2009-06-17 03:20:15 PDT [11025]: [43188-1] LOG: duration: 0.023 ms parse S_1: BEGIN 2009-06-17 03:20:15 PDT [11025]: [43189-1] LOG: duration: 0.003 ms bind S_1: BEGIN* 2009-06-17 03:20:16 PDT [11025]: [43286-1] ERROR: prepared statement "S_2" already exists* 2009-06-17 03:20:17 PDT [11025]: [43288-1] LOG: duration: 0.009 ms bind S_1: BEGIN 2009-06-17 03:20:21 PDT [11025]: [45950-1] LOG: duration: 0.010 ms parse S_3: COMMIT 2009-06-17 03:20:31 PDT [11025]: [45951-1] LOG: duration: 0.027 ms bind S_1: BEGIN 2009-06-17 03:25:58 PDT [11025]: [70834-1] LOG: duration: 0.034 ms bind S_1: BEGIN *2009-06-17 03:25:58 PDT [11025]: [70888-1] LOG: duration: 0.012 ms statement: DEALLOCATE "S_1"* We have kept the *connection_cache = true *and the *reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'* Regards, Nimesh. On Wed, Jun 17, 2009 at 12:15 PM, Tatsuo Ishii <[email protected]> wrote: > If you are getting this with pgpool-II 2.2.2, please send a > self-contained test case if possible. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Hi, > > > > We are using pgpool-II in Master-slave, Loadbalance mode and are facing > > issues with the same. Can anybody lets us know what's going wrong here? > > > > 1) We are getting many entries in the database logs with following > errors: > > 2009-06-16 21:42:47 PDT [11040]: [2486-1] ERROR: prepared statement > "S_2" > > already exists > > 2009-06-16 21:43:04 PDT [11040]: [2535-1] ERROR: prepared statement > "S_3" > > already exists > > 2009-06-16 21:43:48 PDT [11040]: [2546-1] ERROR: prepared statement > "S_4" > > already exists > > 2009-06-16 21:43:49 PDT [11040]: [2564-1] ERROR: prepared statement > "S_5" > > already exists > > 2009-06-16 21:44:49 PDT [11040]: [2576-1] ERROR: prepared statement > "S_6" > > already exists > > > > This causes the application to fail. {This was supposed to be fixed in > 2.2.1 > > and later versions?} > > > > 2) We are seeing stuck queries when connecting through pgpool. ps U > postgres > > shows all the queries in bind state: > > 5003 ? Ss 0:52 postgres: ck rpro xxx.xx.xx.xx(52218) BIND > > 5004 ? Ss 0:28 postgres: ck rpro xxx.xx.xx.xx(52220) BIND > > 18961 ? Ss 0:54 postgres: ck rpro xxx.xx.xx.xx(34508) BIND > > 18962 ? Ss 0:39 postgres: ck rpro xxx.xx.xx.xx(34510) BIND > > > > This queries are not performing any activity and seem to have hanged. > > > > Both applications in question are Java applications and we are using the > > postgres version 8.3.3 > > > > Any help here would be much appreciated. > > > > Regards, > > Nimesh. >
_______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
