[pgpool-general: 5887] Re: Intermittent query failed when using pgpool2

Tatsuo Ishii ishii at sraoss.co.jp
Wed Jan 24 12:42:11 JST 2018


Our nasty security gateway refuses to accept your attachment.
Can you please upload the log file somewhere?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: Goh Geok Bee <gbgoh at ntu.edu.sg>
Subject: RE: [pgpool-general: 5853] Intermittent query failed when using pgpool2
Date: Wed, 24 Jan 2018 03:31:55 +0000
Message-ID: <0EBD780AF852FB459A97A6CCF113FFEDECD46932 at EXCHMBOX31.staff.main.ntu.edu.sg>

> Hi Tatsuo,
> 
> Apologize for taking some time to get the debug log. Attached is the log as follow : 
> 
> At 14:34:41, there is a failed error in the application log which point to topic 3121. May be you can search for "topic = 3121" in the attached log. 
> 
> After 14:34, we run the same function again and it works. 
> 
> You may want to check the attached log at 14:38:22 which has "topic = 3121" too.
> 
> Hope you can find something from the log. Thanks!
> 
> Best Rdgs,
> GB
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
> Sent: Thursday, 18 January 2018 1:18 PM
> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 5853] Intermittent query failed when using pgpool2
> 
> Hi GB,
> 
> Is it possible to provide pgpool debug log for both cases (1 row returned, 0 row returned)? I want to compare them.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Hi Tatsuo,
>> 
>> Yes, it return 1 row via pgpool-II. Just to clarify, it is not consistent that it will always return 1 row. Sometimes it will return 0 zero which is being logged in the apache log. That's the problem. We do not have this problem if the app access the database directly but only via pgpool that this happened. Hope you can find something. Thanks!
>> 
>> Best Rdgs,
>> GB
>> 
>> 
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>> Sent: Wednesday, 17 January 2018 10:20 AM
>> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
>> Cc: pgpool-general at pgpool.net
>> Subject: Re: [pgpool-general: 5853] Intermittent query failed when 
>> using pgpool2
>> 
>> I meant you execute "SELECT count(version) FROM qtemplates WHERE qtemplate = 17958;" via Pgpool-II. Did you try that?
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>>> Hi Tatsuo,
>>> 
>>> Both nodes return 1 row. So there is something wrong with pgpool2? Thanks!
>>> 
>>> Best Rdgs,
>>> GB
>>>  
>>> 
>>> -----Original Message-----
>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>> Sent: Wednesday, 17 January 2018 8:23 AM
>>> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
>>> Cc: ishii at sraoss.co.jp; pgpool-general at pgpool.net
>>> Subject: Re: [pgpool-general: 5853] Intermittent query failed when 
>>> using pgpool2
>>> 
>>> But the log shows "SELECT version FROM qtemplates WHERE qtemplate = 17958;" returns no rows on DB node 1. If you issue a query something like "SELECT count(version) FROM qtemplates WHERE qtemplate = 17958;"
>>> and see no rows returned, then there's something wrong with Pgpool-II.
>>> If it returns 1 row, then Pgpool-II is ok and probably you should check your PostgreSQL cluster on node 1.
>>> 
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>>> 
>>>> Hi Tatsuo,
>>>> 
>>>> Those query are select query which old data that are already exist in both primary node and secondary nodes. So it should not be the case. 
>>>> 
>>>> Best Rdgs,
>>>> GB
>>>> 
>>>> 
>>>> -----Original Message-----
>>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>>> Sent: Tuesday, 16 January 2018 3:04 PM
>>>> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
>>>> Cc: pgpool-general at pgpool.net
>>>> Subject: Re: [pgpool-general: 5853] Intermittent query failed when 
>>>> using pgpool2
>>>> 
>>>> That's pretty normal because streaming replication is essentially async process. A modification made on the primary node takes a while before it is applied to the standbys. If Pgpool-II redirects the query to standby, it is possible that it does not return the row you want to retrieve.
>>>> 
>>>> Best regards,
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese:http://www.sraoss.co.jp
>>>> 
>>>>> Hi Tatsuo,
>>>>> 
>>>>> Yes, it just returns no row. 
>>>>> 
>>>>> Best Rdgs,
>>>>> GB
>>>>> -----Original Message-----
>>>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>>>> Sent: Tuesday, 16 January 2018 2:33 PM
>>>>> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
>>>>> Cc: pgpool-general at pgpool.net
>>>>> Subject: Re: [pgpool-general: 5853] Intermittent query failed when 
>>>>> using pgpool2
>>>>> 
>>>>> After checking pgpool.conf and pgpool log, it seems everything is ok to me.
>>>>> 
>>>>>>> We are using pgpool2 version 3.6.7. However, we encountered intermittent query failed on our application and we are using parameterized query.
>>>>> 
>>>>> What did you see exactly by "query failed"? Any errors from your application? Or just it returns no row?
>>>>> 
>>>>> Best regards,
>>>>> --
>>>>> Tatsuo Ishii
>>>>> SRA OSS, Inc. Japan
>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>> Japanese:http://www.sraoss.co.jp
>>>>> 
>>>>>> Hi Tatsuo,
>>>>>> 
>>>>>> Attached is the pgpool.conf. Looking forward to your reply. Thanks!
>>>>>> 
>>>>>> Best Rdgs,
>>>>>> GB
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> -----Original Message-----
>>>>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>>>>> Sent: Friday, 12 January 2018 6:19 AM
>>>>>> To: Goh Geok Bee <gbgoh at ntu.edu.sg>
>>>>>> Cc: pgpool-general at pgpool.net
>>>>>> Subject: Re: [pgpool-general: 5853] Intermittent query failed when 
>>>>>> using pgpool2
>>>>>> 
>>>>>>> We are using pgpool2 version 3.6.7. However, we encountered intermittent query failed on our application and we are using parameterized query.
>>>>>>>
>>>>>>> We noticed there is similar issue reported in https://github.com/brianc/node-postgres/issues/513 too.
>>>>>>>
>>>>>>> We are using master/slave mode for pgpool with 1 master and 1 slave postgresql 9.6 servers and only load balance mode is enabled on pgpool2. I have attached the pgpool log file. Pls check the log for 10:57:16 onwards. The query that did not returned result is "SELECT version FROM qtemplates WHERE qtemplate = 17958;" . However, if the same query is run again, it maybe successful.
>>>>>>>
>>>>>>> Anyone has encountered this before? Appreciate any advice. Thanks!
>>>>>> 
>>>>>> Can you share pgpool.conf? Hard to tell what's going on without configuration data.
>>>>>> 
>>>>>> Best regards,
>>>>>> --
>>>>>> Tatsuo Ishii
>>>>>> SRA OSS, Inc. Japan
>>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>>> Japanese:http://www.sraoss.co.jp
>>>>>> ________________________________
>>>>>> CONFIDENTIALITY: This email is intended solely for the person(s) named and may be confidential and/or privileged. If you are not the intended recipient, please delete it, notify us and do not copy, use, or disclose its contents.
>>>>>> Towards a sustainable earth: Print only when necessary. Thank you.


More information about the pgpool-general mailing list