<div dir="ltr">Hi Tatsuo,<div>I just tried the patch on 3.6.4 and it works. Thanks! </div><div>When do you think this will become available in the yum repositories?</div><div>-AJ</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, May 26, 2017 at 1:32 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I think I found the cause of the problem.<br>
It seems node.js sends a query in following pattern:<br>
<br>
Parse<br>
Bind<br>
Describe<br>
Execute<br>
Flush<br>
Sync<br>
<br>
The "Flush" message is actually unnecessary: it just requests backend<br>
(in this case Pgpool-II) to send any result pending in the<br>
backend. However, which will be accomplished by the subsequent "Sync"<br>
message. So the Flush message is completely redundant here.<br>
<br>
Anyway, the pattern is not supposed in Pgpool-II.<br>
Can you please try the attached fix?<br>
<span class=""><br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
<br>
</span><span class="">> Hi Tatsuo,<br>
> Thanks for looking into it, I am just coping you and my team-mates instead<br>
> of spamming the whole group.<br>
><br>
> I have attached more details, hopefully this helps you. I have attached a<br>
</span>> node.js script (*pgpool_test.js.txt, *I had to add a .txt extension because<br>
<span class="">> gmail would not allow me to attach a file with a .js extension) which<br>
</span>> creates a table* public.test *with just one column (id) and one row (id=1).<br>
<span class="">> It then runs "select * from public.test where id=1" twice using a<br>
> non-parametrized query and then again twice using a parameterized query.<br>
><br>
</span>> As you can see in *output.log*, the table creation and the first two non<br>
<span class="">> parametrized queries run fine. The first parameterized query also runs fine<br>
> (because it wasn't cached). The second parametrized query never returns and<br>
> node script just hangs.<br>
><br>
</span>> I am also attaching the pgpool output log (*pgpool.log*) and the tcp packet<br>
> capture (*dump.out*, viewable in wireshark) that captured all the tcp<br>
<span class="">> traffic between the client and the machine running pgpool. Let me know if<br>
> you need anything else.<br>
><br>
> -AJ<br>
><br>
> If you want to run the node.js script yourself, here are the instructions<br>
> to do that (on a linux machine)<br>
><br>
</span>> - Install node (the instructions would be machine specific)<br>
> - npm install pg<br>
> - npm install seq<br>
> - PGPORT=<PGPOOL_PORT> PGDATABASE=<DATABASE_NAME> PGHOST=<PH_HOST_NAME><br>
<div class="HOEnZb"><div class="h5">> PGUSER=<PG_USERNAME> node pgpool_test.js<br>
><br>
><br>
> On Fri, May 19, 2017 at 12:30 AM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> > Hi,<br>
>> > I am having an issue with using parameterized queries with pgpool. My<br>
>> setup<br>
>> > involves using node.js client with pgpool (3.6.4) and postgresl 9.6. I am<br>
>> > using pgpool just for caching using shmem.<br>
>> ><br>
>> > I have a simple example with the following query "SELECT * FROM<br>
>> > master.employer where employer_id=$1". The query returns fine the first<br>
>> > time and then it gets cached. When I run it again it hits the cache, but<br>
>> > returns an empty resultset.<br>
>> ><br>
>> > Let me know if I can provide with any other details. I have attached the<br>
>> > entire pgpool log.<br>
>><br>
>> In the log I see:<br>
>><br>
>> 2017-05-18 21:13:30: pid 22781: DEBUG: memcache: sending cached messages:<br>
>> 'D' len: 4917<br>
>> 2017-05-18 21:13:30: pid 22781: DEBUG: memcache: sending cached messages:<br>
>> 'C' len: 13<br>
>><br>
>> which means first Pgpool-II sends 'D' (actual row data) in 4917 bytes<br>
>> then 'C' (Indicating select command completed). This is perfectly sane<br>
>> from the protocol's point of view. So as far as the log says,<br>
>> Pgpool-II successfully returns cached data to client. So the remaining<br>
>> possibility is the returned data is broken. I don't what kind of debug<br>
>> method node.js has, but is it possible to capture/dump the packet data<br>
>> using wireshark?<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" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
>><br>
</div></div><br>diff --git a/src/query_cache/pool_<wbr>memqcache.c b/src/query_cache/pool_<wbr>memqcache.c<br>
index fdef8c1..b8a5a5a 100644<br>
--- a/src/query_cache/pool_<wbr>memqcache.c<br>
+++ b/src/query_cache/pool_<wbr>memqcache.c<br>
@@ -645,43 +645,9 @@ POOL_STATUS pool_fetch_from_memory_cache(<wbr>POOL_CONNECTION *frontend,<br>
pfree(qcache);<br>
<br>
/*<br>
- * If we are doing extended query, forward sync message from frontend to<br>
- * backend. This is necessary to prevent receiving Sync message after<br>
- * Sending Ready for query.<br>
+ * Send a "READY FOR QUERY" if not in extended query.<br>
*/<br>
- if (pool_is_doing_extended_query_<wbr>message())<br>
- {<br>
- char kind;<br>
- int32 len;<br>
- POOL_SESSION_CONTEXT *session_context;<br>
- POOL_CONNECTION *target_backend;<br>
- char buf[5];<br>
-<br>
- if (pool_flush(frontend))<br>
- return POOL_END;<br>
- if (pool_read(frontend, &kind, 1))<br>
- return POOL_END;<br>
-<br>
- ereport(DEBUG2,<br>
- (errmsg("memcache: fetching from memory cache: expecting sync: kind '%c'", kind)));<br>
- if (pool_read(frontend, &len, sizeof(len)))<br>
- return POOL_END;<br>
-<br>
- /* Forward "Sync" message to backend */<br>
- session_context = pool_get_session_context(true)<wbr>;<br>
- target_backend = CONNECTION(backend, session_context->load_balance_<wbr>node_id);<br>
- pool_write(target_backend, &kind, 1);<br>
- pool_write_and_flush(target_<wbr>backend, &len, sizeof(len));<br>
-<br>
- /* Read and discard "Ready for query" message from backend */<br>
- pool_read(target_backend, &kind, 1);<br>
- pool_read(target_backend, buf, sizeof(buf));<br>
- }<br>
-<br>
- /*<br>
- * send a "READY FOR QUERY"<br>
- */<br>
- if (MAJOR(backend) == PROTO_MAJOR_V3)<br>
+ if (!pool_is_doing_extended_<wbr>query_message() && MAJOR(backend) == PROTO_MAJOR_V3)<br>
{<br>
signed char state;<br>
<br>
@@ -691,10 +657,7 @@ POOL_STATUS pool_fetch_from_memory_cache(<wbr>POOL_CONNECTION *frontend,<br>
state = MASTER(backend)->tstate;<br>
send_message(frontend, 'Z', 5, (char *)&state);<br>
}<br>
- else<br>
- {<br>
- pool_write(frontend, "Z", 1);<br>
- }<br>
+<br>
if (pool_flush(frontend))<br>
{<br>
return POOL_END;<br>
<br></blockquote></div><br></div>