[Pgpool-general] Pgpool probelms

Fri Jun 19 04:31:40 UTC 2009

Hi Tatsuo,

Can you let me know, what might be wrong here?

Regards,
Nimesh.

On 6/17/09, Nimesh Satam <nimesh.satam at gmail.com> wrote:
>
> 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 <ishii at sraoss.co.jp> 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.
>>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20090619/773a5832/attachment.html>