[pgpool-general: 1593] Re: (3.1.3 hatsuiboshi) sleep on query cancel

[Tatsuo Ishii]

Bottom line is you cannot avoid the sleep.

Background:
Query canceling works as following(this is required by PostgreSQL
protocol, not pgpool implementation).

1) pgpool receives cancel request packet from client
2) check if it's a valid one (by checking valid cancel key)
3) open new connection to a backend
4) send cacnel packet to the bacjend
5) sleep 1 sec to ensure that other node alreay starts to execute the
   query to be canceld. query cancel won't be accepted by PostgreSQL
   if the query has not started yet
6) if other backend remains goto #3
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi.
> 
> Is there any reason for pgpool to sleep when processing query cancellation?
> Is it possible to unconfigure this sleep (set it to 0)?
> 
> I see explicit attempt to sleep on processing cancellation request here
> (strace):
> 
> # received connection from client, recognized it's cancellation request
> 24626      0.070666 accept(5, {sa_family=AF_INET, sin_port=htons(64885),
> sin_addr=inet_addr("172.18.10.42")}, [16]) = 6
> 24626      0.000066 read(6, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 1024) =
> 16
> # connecting to postgres and relaying
> 24626      0.000063 connect(7, {sa_family=AF_INET, sin_port=htons(5432),
> sin_addr=inet_addr("172.18.10.230")}, 16) = 0
> 24626      0.000060 write(7, "\0\0\0\20\4\322\26.\0\0o.z\360\243?", 16) = 16
> # closing connection to postgres
> 24626      0.000186 shutdown(7, 1 /* send */) = 0
> 24626      0.000105 close(7)            = 0
> # sleeping for 1 sec (!)
> 24626      0.000061 nanosleep({1, 0}, 0x7fff93d85090) = 0
> # closing connection to client
> 24626      1.000350 shutdown(6, 1 /* send */) = 0
> 24626      0.000290 close(6)            = 0
> 
> It's a HUGE problem for me, as I'm using pgpool with WebLogic server.
> WebLogic cancels all cached statements when
> - rolling back (an XA?) transaction
> - closing the pool
> 
> The closure is done on a single thread, so for large pools it takes long
> minutes to close, and often a shared lock is held (as there is apparently
> no expectation that cancellation would take a long amount of time) Postgres
> JDBC driver also doesn't know better and keeps calling cancelQuery() on the
> same connection, even if multiple statements are being closed.
> 
> Thank you,
>   Pawel.