[pgpool-general: 5517] Re: pgpool and erlang - request hangs

PVY balroga3 at yandex.ru
Mon May 22 07:19:04 JST 2017


Hello.

I  was  experimenting  with   older  versions of pgpool and I've found that 3.4 branch (including 3.4.11) is free of this bug with hanging request. 
3.5.0 and later versions are affected - connections from erlang library "epgsql" hang up if pgpool is running with "master_slave_mode = on".
Please  do  not  hesitate to request any additional info from me which could  help  you  to  nail  this  bug.  
Perhaps I should create a test environment with erlang client, pgpool and two postgresql servers and give you ssh access to it?

>> I'm  sorry for the inconvenience.  Pgpool-ii 3.6.4 log attached.

>> I was viewing these packet dumps with wireshark for windows, and  there  were  a  lot  of pgsql packets.
>> However,  the  packet with  this  troublemaking  query  is the last to pass between erlang  client  and  pgpool (see screenshot https://gyazo.com/201c961febc32d6b22fc051acfc8d9c4 ). 
>> I see in another dump that this request was forwarded by pgpool to postgresql master and some kind of response was received, but (my guess) it was ignored/discarded by pgpool.

> Not sure but in general that's possible. PostgreSQL's extended query
> protocol requires Pgpool-II to store responses from PostgreSQL until
> client sends sync or flush messages.

>> Here are the screenshots from tcpdump between pgpool (172.31.20.153)  and postgresql server (172.31.13.108):
>> https://gyazo.com/e775891a4c266772741c0e7ad00a81a2  ">P" 
>> https://gyazo.com/42b5ad3181ea39f56c67352f36b21f15  ">H"
>> https://gyazo.com/b678df3fdf94976b3338440f49d3130a  ">D"
>> https://gyazo.com/bf27d57394b23ce258cf992031d83e5a  ">H"
>> https://gyazo.com/95c7fbea1a760d392d14ad6edcc9dfb9  "<1" first server response packet
>> https://gyazo.com/0ba49991eb44e9ef5521d8265ffb6ced  "<t/T" 2nd server response
>> https://gyazo.com/bff3085d807ff5a1d2b33e0279d45ed1  server retransmits its last "<t/T" message. Why?  Perhaps pgpool didn't respond as pgsql expected?

> As long as that is a TCP/IP level re-transmission, Pgpool-II can't
> recognize it since it happens inside the kernel TCP/IP stack.

>> And then after some time there is an auth request from pgpool, i guess it is related to another request.

> I guess so.

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

>> Вы писали 19 мая 2017 г., 2:22:13:

>>> Unfortunately, I could not find useful information using wireshark
>>> using erlang-pgpool-2nodes-hang.pcap. When I apply filter "pgsql" I
>>> only found startup packet info.

>>> Can you provide Pgpool-II debug log something like
>>> pgpool-erlang-master-slave-hang.log.gz in [pgpool-general: 5487] using
>>> Pgpool-II 3.6.4?

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

>>>> Thank you for providing the log. I will look into it.

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

>>>>> Hello.

>>>>> Thank you.

>>>>> I've compiled 3.6.4 from source, but still no luck :(

>>>>> I'm  attaching  tcpdump  packet captures between erlang and pgpool and
>>>>> between  pgpool  and  postgresql  server  for  both  variants  -  when
>>>>> there is only one node and connection is established and when there is
>>>>> also a slave node and connection hangs. Hope it'll shed some light.

>>>>> File names:
>>>>> erlang-pgpool-1node-ok.pcap - traffic between erlang client and pgpool when connection is established
>>>>> pgpool-pgsql-1node-ok.pcap - traffic between pgpool and postgresql server when connection is established

>>>>> erlang-pgpool-2nodes-hang.pcap - traffic between erlang client and pgpool when connection hangs
>>>>> pgpool-pgsql-2nodes-master-hang.pcap - traffic between pgpool and postgresql master server when connection hangs
>>>>> pgpool-pgsql-2nodes-slave-hang.pcap - traffic between pgpool and postgresql slave server when connection hangs


>>>>>> I recommend you to upgrade to 3.6.4.  For now I'm not sure what is the
>>>>>> real cause of the problem, but 3.6.4 solved many similar problems:
>>>>>> client hangs in extended query protocol, which is used in erlang
>>>>>> applications. (3.6.3 also solved the problem but Unfortunately it
>>>>>> added nasty new problem which was fixed in 3.6.4).

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

>>>>>>> Sorry, forgot to tell my pgpool-II version. It's 3.6.2

>>>>>>>> Hello.

>>>>>>>> Thank you very much for your response.

>>>>>>>> I'm attaching two logs, hope there is enough info. If not, I'll do
>>>>>>>> my best to provide additional data.

>>>>>>>> But, first of all, this is what I've accidentally found today:
>>>>>>>> if  pgpool has only one backend configured (which becomes, of
>>>>>>>> course, a primary backend), then erlang connects without problem!
>>>>>>>> This is log file pgpool-erlang-1master-ok.log
>>>>>>>> But as soon as I add a slave to the pgpool-II config, then request
>>>>>>>> hangs. This is pgpool-erlang-master-slave-hang.log file

>>>>>>>> You wrote:
>>>>>>>>> Your Pgpool-II log is imcoplete. To
>>>>>>>>> invesigate the problem, I need complete Pgpool-II log: starting from
>>>>>>>>> the query issued to the point Pgpool-II hangs.

>>>>>>>> Perhaps I was not clear, pgpool-II itself is fine, it does not hang
>>>>>>>> up. It's the erlang connection to pgpool that hangs.



>>>>>>>>>> Hello.

>>>>>>>>>> If  I  try to establish connection between pgpool and erlang (using pgapp library https://github.com/epgsql/pgapp  )
>>>>>>>>>> then  session  simply  hangs after erlang  client  sends command 
>>>>>>>>>> SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname = ANY($1::varchar[]) 
>>>>>>>>>> parameters: $1 = '{hstore,geometry}'
>>>>>>>>>> If erlang application connects to PgSQL server directly, then connection is established without problems.

>>>>>>>>>> However, if i connect via psql to pgpool and issue the same statement - 
>>>>>>>>>> SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname = ANY('{hstore,geometry}'::varchar[]);
>>>>>>>>>> then this request is processed normally:
>>>>>>>>>>  typname |  oid  | typarray
>>>>>>>>>> ---------+-------+----------
>>>>>>>>>>  hstore  | 19898 |    19903
>>>>>>>>>> (1 row)


>>>>>>>>>> i've turned on "debug5" messages and I can see these lines in the pgpool log when erlang library is connecting, but I have no idea what other lines are significant.
>>>>>>>>>> May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-1] pgpool2 DEBUG:  decide where to send the queries
>>>>>>>>>> May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-2] pgpool2 DETAIL:  destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname = ANY($1::varchar[])"
>>>>>>>>>> May 17 18:02:02 ip-172-31-20-153 [23121]: [432087-1] pgpool2 DEBUG:  system catalog walker, checking relation "pg_type"
>>>>>>>>>> May 17 18:02:02 ip-172-31-20-153 [23121]: [432134-1] pgpool2 DEBUG:  do_query: extended:1 query:"SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'"

>>>>>>>>>> and these are lines from the log file if i run this query by hand from psql:
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-1] pgpool2 DEBUG:  decide where to send the queries
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-2] pgpool2 DETAIL:  destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname = ANY('{hstore,geometry}'::varchar[]);"
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-3] pgpool2 LOCATION:  pool_query_context.c:429
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-1] pgpool2 DEBUG:  system catalog walker, checking relation "pg_type"
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-2] pgpool2 LOCATION:  pool_select_walker.c:374
>>>>>>>>>> May 17 18:12:24 ip-172-31-20-153 [23539]: [432781-1] pgpool2 DEBUG:  do_query: extended:0 query:"SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'"

>>>>>>>>>> There is a difference - when request is sent from erlang, then "extended:1", but when i ru query from psql, then "extended:0". Perhaps this is important?

>>>>>>>>> Maybe. erlang seems to use "extended query", which is also used in Java.

>>>>>>>>>> I understand that it looks like the problem on the erlang library side, but perhaps you, basing on your pgpool knowledge, could suggest what is causing this incompatibility between erlang pgsql library and pgpool?
>>>>>>>>>> Perhaps this old thread has something in common with my problem: http://lists.pgfoundry.org/pipermail/pgpool-general/2011-February/003409.html ?

>>>>>>>>> Not sure. Extended query is pretty complex. Extended query users for
>>>>>>>>> Pgpool-II are mostly Java. So it is possible that erlang (or pgapp)
>>>>>>>>> reveleas new bug with Pgpool-II. Your Pgpool-II log is imcoplete. To
>>>>>>>>> invesigate the problem, I need complete Pgpool-II log: starting from
>>>>>>>>> the query issued to the point Pgpool-II hangs.

>>>>>>>>> Also please let us know what version of Pgpool-II you using exactly.

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






>>>>>>> -- 
>>>>>>> С уважением,
>>>>>>>  PVY                          mailto:balroga3 at yandex.ru

>>>>>>> _______________________________________________
>>>>>>> pgpool-general mailing list
>>>>>>> pgpool-general at pgpool.net
>>>>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general



>>>>> -- 
>>>>> С уважением,
>>>>>  PVY                          mailto:balroga3 at yandex.ru
>>>> _______________________________________________
>>>> pgpool-general mailing list
>>>> pgpool-general at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general



>> -- 
>> С уважением,
>>  PVY                          mailto:balroga3 at yandex.ru



-- 
С уважением,
 PVY                          mailto:balroga3 at yandex.ru



More information about the pgpool-general mailing list