<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from text --><style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<div>Tatsuo,<br>
I believe I'll find the real cause of the problem. The logic which fight to now is very complex. Is not a single request but a series of transactions through a single connection. Obviously some messages violate the protocol.<br>
<br>
<br>
Sergey.<br>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> Tatsuo Ishii <ishii@sraoss.co.jp><br>
<b>Sent:</b> Friday, April 7, 2017 9:05:31 AM<br>
<b>To:</b> Sergey Kim<br>
<b>Cc:</b> Boris Voskanov; pgpool-hackers@pgpool.net<br>
<b>Subject:</b> Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created</font>
<div> </div>
</div>
</div>
<font size="2"><span style="font-size:10pt;">
<div class="PlainText">Sergey,<br>
<br>
> Tatsuo,<br>
> <br>
> I'm struggling to reproduce the problem, but can you please take a look at the attached pgpool log.<br>
> The log clearly says that PARSE message and BIND (B) messages were sent to the backend 0.<br>
<br>
Right.<br>
<br>
> 1) The request was identified in Postgres and strace says it waits 'epoll_wait(3':<br>
> <br>
> [root@OAA-a01149b7a559 ~]# sudo -i -u postgres psql --port=15432<br>
> psql (9.6.2)<br>
> Type "help" for help.<br>
> <br>
> postgres=# select * from pg_stat_activity where pid = 29108;<br>
> datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change<br>
> | wait_event_type | wait_event | state | backend_xid | backend_xmin | query<br>
> -------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------<br>
> +-----------------+------------+--------+-------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
> 16389 | oss | 29108 | 16390 | oss | | 127.0.0.1 | | 37570 | 2017-04-06 11:33:37.428804+03 | 2017-04-06 11:34:03.018548+03 | 2017-04-06 11:34:03.018738+03 | 2017-04-06 11:34:03.01874+03<br>
> | | | active | | 1835366 | SELECT 1 FROM ui_locked_users WHERE LOWER(username) = LOWER( $1 ) AND (device_tag IS NULL OR device_tag = $2 ) AND (unlock_on IS NULL OR unlock_on >= $3 )<br>
> (1 row)<br>
> <br>
> 2) The DB application stuck on the execution of SELECT query. It was blocked until the corresponding pgpool process was killed.<br>
> <br>
> 3) Pgpool log is attached.<br>
> <br>
> <br>
> Is it possible that pgpool didn't forward EXECUTE command to postgres?<br>
<br>
Yes, if Pgpool-II is waiting for something from either frontend or<br>
backend.<br>
<br>
> The problem is reproduced reliably if balancing is enabled, but I'm failing to reproduce it in a standalone test so far.<br>
> <br>
> <br>
> Sergey.<br>
> ________________________________<br>
> From: Tatsuo Ishii <ishii@sraoss.co.jp><br>
> Sent: Thursday, April 6, 2017 3:48 AM<br>
> To: Sergey Kim<br>
> Cc: Boris Voskanov; pgpool-hackers@pgpool.net<br>
> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created<br>
> <br>
>> Tatsuo,<br>
>><br>
>><br>
>> the symptoms of the "hanged" pgpool are following:<br>
>> 1) DB applications fail with 2 types of errors:<br>
>> - failed to prepare statement (PARSE)<br>
>> - failed to extract ResultSet - failed selects<br>
>><br>
>> 2) The problem is reproduced within a single JDBC connection, series of transactions are passed into it before the problem appears.<br>
>><br>
>> I'm deeply analysed pgpool logs from the series of failures and revealed a sample case (i.e. pgpool process). It's logs are attached.<br>
>> I'm not sure I'm right but it seems the balancing logic is broken it its sequence of messages - how the are processed. I'am triggered by the fact it was sending PARSE messages to the both backends.<br>
> <br>
> Please provide a reproducable test case. I do not see any error<br>
> message in the log.<br>
> <br>
>> P/S: I was trying to attach the debugger to pgpool but I'm failed to detect a piece of code which I should focus at. Also it is a little bit hard to detect a pgpool process which should be debugged.<br>
> <br>
> Use ps to find a pgpool process which shows 'idle' state. Or you could<br>
> set num_init_children to 1. In this case there's only one pgpool child<br>
> process.<br>
> <br>
> Best regards,<br>
> --<br>
> Tatsuo Ishii<br>
> SRA OSS, Inc. Japan<br>
> English: <a href="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a><br>
> Japanese:http://www.sraoss.co.jp<br>
> <br>
>> Sergey.<br>
>> ________________________________<br>
>> From: Sergey Kim<br>
>> Sent: Wednesday, April 5, 2017 1:48 PM<br>
>> To: Tatsuo Ishii<br>
>> Cc: Boris Voskanov; pgpool-hackers@pgpool.net; Sergey Onuchin<br>
>> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created<br>
>><br>
>><br>
>> ... also it happens only when balancing is enabled in pgpool.<br>
>><br>
>><br>
>> ________________________________<br>
>> From: Sergey Kim<br>
>> Sent: Wednesday, April 5, 2017 1:34:45 PM<br>
>> To: Tatsuo Ishii<br>
>> Cc: Boris Voskanov; pgpool-hackers@pgpool.net; Sergey Onuchin<br>
>> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created<br>
>><br>
>><br>
>> Tatsuo,<br>
>><br>
>><br>
>> the 2nd day of tests revealed a problem in pgpool which I trying to reproduce in a standalone test.<br>
>><br>
>><br>
>> Sometimes PARSE messages hang in pgpool and DB clients hang due to this reason. I'm just wanted to notify you on the problem. I'm fighting to reproduce it artificially.<br>
>><br>
>><br>
>> Sergey.<br>
>><br>
>><br>
>> ...the primary message was cut<br>
</div>
</span></font>
</body>
</html>