<div dir="ltr"><div>On Wed, Apr 10, 2013 at 5:18 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br></div><div class="gmail_extra"><div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Bottom line is you cannot avoid the sleep.<br></blockquote><div><br></div><div style>In my case, it's irrelevant because I only use pgpool for failover (master_slave_mode). I would think that in failover configuration, the sleep does not help with anything. As I said, it's causing very serious problems with my J2EE server, so I had to cut it out of the source code, at least for now.</div>
<div style><br></div><div style>Also, though WebLogic isn't behaving nicely (I don't think it should be canceling all the statements), it's within its rights to do what it's doing, I then have to turn around and really blame this on postgres JDBC driver, who issues unconditional cancelQuery() for any statement that is associated with the connection, whether any statement is running or not. This is where I would have fixed it, but it's a lot more complicated (than remove sleep(1) from pgpool source, sorry :) )</div>
<div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Background:<br>
Query canceling works as following(this is required by PostgreSQL<br>
protocol, not pgpool implementation). </blockquote><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
1) pgpool receives cancel request packet from client<br>
2) check if it's a valid one (by checking valid cancel key)<br>
3) open new connection to a backend<br>
4) send cacnel packet to the bacjend<br>
5) sleep 1 sec to ensure that other node alreay starts to execute the<br>
query to be canceld. query cancel won't be accepted by PostgreSQL<br>
if the query has not started yet<br></blockquote><div><br></div><div style>I wonder how is it known that 1 second is enough to know that another node started to execute the query, i.e. what's so magical about 1 second.</div>
<div style><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
6) if other backend remains goto #3<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<div class="HOEnZb"><div class="h5"><br>
> Hi.<br>
><br>
> Is there any reason for pgpool to sleep when processing query cancellation?<br>
> Is it possible to unconfigure this sleep (set it to 0)?<br>
><br>
> I see explicit attempt to sleep on processing cancellation request here<br>
> (strace):<br>
><br>
> # received connection from client, recognized it's cancellation request<br>
> 24626 0.070666 accept(5, {sa_family=AF_INET, sin_port=htons(64885),<br>
> sin_addr=inet_addr("172.18.10.42")}, [16]) = 6<br>
> 24626 0.000066 read(6, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 1024) =<br>
> 16<br>
> # connecting to postgres and relaying<br>
> 24626 0.000063 connect(7, {sa_family=AF_INET, sin_port=htons(5432),<br>
> sin_addr=inet_addr("172.18.10.230")}, 16) = 0<br>
> 24626 0.000060 write(7, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 16) = 16<br>
> # closing connection to postgres<br>
> 24626 0.000186 shutdown(7, 1 /* send */) = 0<br>
> 24626 0.000105 close(7) = 0<br>
> # sleeping for 1 sec (!)<br>
> 24626 0.000061 nanosleep({1, 0}, 0x7fff93d85090) = 0<br>
> # closing connection to client<br>
> 24626 1.000350 shutdown(6, 1 /* send */) = 0<br>
> 24626 0.000290 close(6) = 0<br>
><br>
> It's a HUGE problem for me, as I'm using pgpool with WebLogic server.<br>
> WebLogic cancels all cached statements when<br>
> - rolling back (an XA?) transaction<br>
> - closing the pool<br>
><br>
> The closure is done on a single thread, so for large pools it takes long<br>
> minutes to close, and often a shared lock is held (as there is apparently<br>
> no expectation that cancellation would take a long amount of time) Postgres<br>
> JDBC driver also doesn't know better and keeps calling cancelQuery() on the<br>
> same connection, even if multiple statements are being closed.<br>
><br>
> Thank you,<br>
> Pawel.<br>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br>With best of best regards<br>Pawel S. Veselov
</div></div>