<div dir="ltr">I am using this: <a href="https://www.npmjs.com/package/pg">https://www.npmjs.com/package/pg</a></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, May 30, 2017 at 6:11 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">BTW, which PostgreSQL driver are you using? It is possible the author<br>
of the library misunderstand the frontend/backend protocol.<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>From: Ankur Jain <<a href="mailto:ankur.jain@obeohealth.com">ankur.jain@obeohealth.com</a>><br>
Subject: Re: [pgpool-general: 5511] Issue with parameterized query<br>
Date: Tue, 30 May 2017 16:40:12 -0700<br>
Message-ID: <<a href="mailto:CACvFWAq_TkVphnYySJYFRp%2B8JHeQ7L_BYAHyZE7opa7JkVXTyA@mail.gmail.com">CACvFWAq_TkVphnYySJYFRp+<wbr>8JHeQ7L_BYAHyZE7opa7JkVXTyA@<wbr>mail.gmail.com</a>><br>
<div class="HOEnZb"><div class="h5"><br>
> Hi Tatsuo,<br>
> I just tried the patch on 3.6.4 and it works. Thanks!<br>
> When do you think this will become available in the yum repositories?<br>
> -AJ<br>
><br>
> On Fri, May 26, 2017 at 1:32 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> 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>
>><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>
>> > Hi Tatsuo,<br>
>> > Thanks for looking into it, I am just coping you and my team-mates<br>
>> instead<br>
>> > of spamming the whole group.<br>
>> ><br>
>> > I have attached more details, hopefully this helps you. I have attached a<br>
>> > node.js script (*pgpool_test.js.txt, *I had to add a .txt extension<br>
>> because<br>
>> > gmail would not allow me to attach a file with a .js extension) which<br>
>> > creates a table* public.test *with just one column (id) and one row<br>
>> (id=1).<br>
>> > 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>
>> > As you can see in *output.log*, the table creation and the first two non<br>
>> > parametrized queries run fine. The first parameterized query also runs<br>
>> fine<br>
>> > (because it wasn't cached). The second parametrized query never returns<br>
>> and<br>
>> > node script just hangs.<br>
>> ><br>
>> > I am also attaching the pgpool output log (*pgpool.log*) and the tcp<br>
>> packet<br>
>> > capture (*dump.out*, viewable in wireshark) that captured all the tcp<br>
>> > 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>
>> > - Install node (the instructions would be machine specific)<br>
>> > - npm install pg<br>
>> > - npm install seq<br>
>> > - PGPORT=<PGPOOL_PORT> PGDATABASE=<DATABASE_NAME><br>
>> PGHOST=<PH_HOST_NAME><br>
>> > 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>><br>
>> 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.<br>
>> 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<br>
>> first<br>
>> >> > time and then it gets cached. When I run it again it hits the cache,<br>
>> but<br>
>> >> > returns an empty resultset.<br>
>> >> ><br>
>> >> > Let me know if I can provide with any other details. I have attached<br>
>> 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<br>
>> messages:<br>
>> >> 'D' len: 4917<br>
>> >> 2017-05-18 21:13:30: pid 22781: DEBUG: memcache: sending cached<br>
>> 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>
>><br>
>> diff --git a/src/query_cache/pool_<wbr>memqcache.c b/src/query_cache/pool_<br>
>> 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<br>
>> *frontend,<br>
>> pfree(qcache);<br>
>><br>
>> /*<br>
>> - * If we are doing extended query, forward sync message from<br>
>> frontend to<br>
>> - * backend. This is necessary to prevent receiving Sync message<br>
>> 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<br>
>> 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,<br>
>> 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>
>> */<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) ==<br>
>> PROTO_MAJOR_V3)<br>
>> {<br>
>> signed char state;<br>
>><br>
>> @@ -691,10 +657,7 @@ POOL_STATUS pool_fetch_from_memory_cache(<wbr>POOL_CONNECTION<br>
>> *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>
>><br>
</div></div></blockquote></div><br></div>