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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Jan 30 13:11:43 JST 2018


> Hi Tatsuo,
> 
> Thank you so much for your help. Your advice to set synchronous_commit = remote_apply works. 
> 
> For my learning, can you show what is the log entry that show :
> 
> if the query is sent to node 0, it returns 1 row, while if it is sent to node 1, it returns no row.

Sure. When PostgreSQL returns rows, following messages are coming to
Pgpool-II: 'T' (Row description, information for row meta data, such
as column names and their data types), 'D' (Data row, actual row data,
1 message/1 row), 'C' (Command complete, indicating the query
successfully finishes and there's no more data row.)

In the log file,

2018-01-05 10:57:16: pid 31839: DEBUG:  reading backend data packet kind
2018-01-05 10:57:16: pid 31839: DETAIL:  backend:1 kind:'T'

indicates that a row description message arrives from backend 1.

If you see after 'T' something like:

2018-01-05 10:57:16: pid 31839: DEBUG:  reading backend data packet kind
2018-01-05 10:57:16: pid 31839: DETAIL:  backend:1 kind:'D'

then it shows that backend 1 returns a row.

While if you see 'C' without 'D' message, that indicates no row is
returned.

Hope this helps,

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

> Appreciate your help. Thank you!
> 
> Best Rdgs,
> GB
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
> Sent: Thursday, 25 January 2018 11:43 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
> 
> Hi Goh,
> 
>> Hi Tatsuo,
>> 
>> 1. Both node 0 and node 1 are in sync as I have checked that the wal receiver process is running and run the following query on the slave node  which returned replication_lag is zero, which means both node 0 and node 1 are in sync.
> 
> Not necessarily. It just says WAL position is in sync, which does not guarantee that a SELECT retrieves same data on both nodes. This is because until the WAL is applied to the database, SELECT won't see the new data on standby. i.e. there is a time lag between WAL is arrived and the WAL is applied.
> 
> If you want to see exact same data all time in both nodes, you have to set synchronous_commit = remote_apply (assuming that you are using PostgreSQL 9.6 or greater version).
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> $  ps aux | egrep 'wal\sreceiver'
>> postgres  1712  0.0  0.0 337424 11964 ?        Ss    2017  26:46 postgres: wal receiver process   streaming 6/9D838A00
>> 
>> postgres=#
>> postgres=# SELECT
>> postgres-# CASE
>> postgres-# WHEN pg_last_xlog_receive_location() = 
>> pg_last_xlog_replay_location() THEN 0 postgres-#  ELSE EXTRACT (EPOCH 
>> FROM now() - pg_last_xact_replay_timestamp())::INTEGER
>> postgres-# END
>> postgres-# AS replication_lag;
>>  replication_lag
>> -----------------
>>                0
>> (1 row)
>> 
>> 
>> 2. When I run the same query "SELECT topic, course, title, visibility, position, archived FROM topics WHERE topic = 3121;" on both nodes, they returned the same result.
>> 
>> 3. Basically the function of the application that result with this intermittent query is to copy the data from one course into another. Therefore all the SELECT queries should be successful for both nodes as the data already in the database for some time. 
>> 
>> Pls help to investigate further. Thank you!
>> 
>> Best Rdgs,
>> GB
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>> Sent: Wednesday, 24 January 2018 9:44 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 looking into the log file, I realized that if the query is sent to node 0, it returns 1 row, while if it is sent to node 1, it returns no row. Apparently, there's data inconsistency between node 0 and node 1.
>> 
>> I would say, this is not a Pgpool-II problem.
>> 
>> 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,
>>> 
>>> The query failed is intermittent, therefore we cannot reproduce for the same query. For this time round, it failed at this select statement - "SELECT topic, course, title, visibility, position, archived
>>>                      FROM topics
>>>                      WHERE topic = 3121;"
>>> 
>>> Will you be able to use this to investigate instead since it has a failed query and successful query log.
>>> 
>>> Thanks,
>>> GB
>>> 
>>> 
>>> 
>>> -----Original Message-----
>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>> Sent: Wednesday, 24 January 2018 4:59 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
>>> 
>>> I told you that I need "SELECT count(..." type query but the query you sent was "SELECT topic, course, title, visibility, position, archived
>>>                      FROM topics
>>>                      WHERE topic = 3121;"
>>> 
>>> which does not help me much.
>>> 
>>> 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,
>>>> 
>>>> I have send you the link to download the log file. Let me know if you are unable to do it. 
>>>> 
>>>> Best Rdgs,
>>>> GB
>>>> 
>>>> -----Original Message-----
>>>> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
>>>> Sent: Wednesday, 24 January 2018 11:42 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
>>>> 
>>>> 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.
>>>> e
>>>> d
>>>> u.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