<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<p><br>
</p>
Tatsuo,</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
I'm struggling to reproduce the problem, but can you please take a look at the attached pgpool log.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
The log clearly says that PARSE message and BIND (B) messages were sent to the backend 0.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<i><br>
</i></div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
1) The request was identified in Postgres and strace says it waits 'epoll_wait(3':</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">[root@OAA-a01149b7a559 ~]# sudo -i -u postgres psql --port=15432</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">psql (9.6.2)</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">Type "help" for help.</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><br>
</i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">postgres=# select * from pg_stat_activity where pid = 29108;</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;"> datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change
</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">| wait_event_type | wait_event | state | backend_xid | backend_xmin | query
</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">+-----------------+------------+--------+-------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------</span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;"> 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 </span></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">| | | active | | 1835366 | </span><b><span style="color: rgb(255, 0, 0); font-size: 11pt;">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 )</span></b></i></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 16px;">
<i><span style="font-size: 11pt;">(1 row)</span></i></div>
<div><i><br>
</i></div>
2) The DB application stuck on the execution of SELECT query. It was blocked until the corresponding pgpool process was killed.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
3) Pgpool log is attached.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
Is it possible that pgpool didn't forward EXECUTE command to postgres? The problem is reproduced reliably if balancing is enabled, but I'm failing to reproduce it in a standalone test so far.</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
<br>
</div>
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;" dir="ltr">
Sergey.</div>
<div style="color: rgb(0, 0, 0);">
<div>
<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> Thursday, April 6, 2017 3:48 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">> 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" id="LPlnk826684" previewremoved="true">
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></div>
</div>
</div>
</body>
</html>