<div dir="ltr">It just a regular SQL level query. Something like "SELECT * from table where id=$1" along with a parameters array that get substituted for $1. SQL PREPARED STATEMENTS use parametrized query. I've reported this issue to the node.js community as well here (<a href="https://github.com/brianc/node-postgres/issues/1255">https://github.com/brianc/node-postgres/issues/1255</a>) , but it seems like this is not node.js or the node sql adapter issue. When I run the same code bypassing pgpool everything runs fine. I have a really simple application so I could easily modify all queries in my code to not use parameters, but I think its a pretty serious limitation.<div><br></div><div>-AJ</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 17, 2017 at 5:37 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'm not familiar with node.js so I would like to ask you what<br>
"parameterized queries" means here. Is it SQL level ones (PREPARE,<br>
EXECUTE) or using extended query protocol? The info needed to<br>
reproduce the problem.<br>
<span class="im HOEnZb"><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><div class="HOEnZb"><div class="h5">> I am not sure if this is related. By in my node.js setup (pg 6.1.5,<br>
> Postgres 9.6 and pgpool-II 3.6.1), parameterized queries just don't work<br>
> with pg_pool caching. Queries that use parameters like $1 etc run the first<br>
> time (when they are not cached) and then subsequent ones just hang.<br>
> -AJ<br>
><br>
> On Wed, May 17, 2017 at 4:44 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> > Hello.<br>
>> ><br>
>> > If I try to establish connection between pgpool and erlang (using<br>
>> pgapp library <a href="https://github.com/epgsql/pgapp" rel="noreferrer" target="_blank">https://github.com/epgsql/<wbr>pgapp</a> )<br>
>> > then session simply hangs after erlang client sends command<br>
>> > SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname =<br>
>> ANY($1::varchar[])<br>
>> > parameters: $1 = '{hstore,geometry}'<br>
>> > If erlang application connects to PgSQL server directly, then connection<br>
>> is established without problems.<br>
>> ><br>
>> > However, if i connect via psql to pgpool and issue the same statement -<br>
>> > SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname =<br>
>> ANY('{hstore,geometry}'::<wbr>varchar[]);<br>
>> > then this request is processed normally:<br>
>> > typname | oid | typarray<br>
>> > ---------+-------+----------<br>
>> > hstore | 19898 | 19903<br>
>> > (1 row)<br>
>> ><br>
>> ><br>
>> > i've turned on "debug5" messages and I can see these lines in the pgpool<br>
>> log when erlang library is connecting, but I have no idea what other lines<br>
>> are significant.<br>
>> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-1] pgpool2 DEBUG:<br>
>> decide where to send the queries<br>
>> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-2] pgpool2 DETAIL:<br>
>> destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM<br>
>> pg_type WHERE typname = ANY($1::varchar[])"<br>
>> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432087-1] pgpool2 DEBUG:<br>
>> system catalog walker, checking relation "pg_type"<br>
>> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432134-1] pgpool2 DEBUG:<br>
>> do_query: extended:1 query:"SELECT count(*) FROM pg_class AS c,<br>
>> pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace<br>
>> = n.oid AND n.nspname = 'pg_catalog'"<br>
>> ><br>
>> > and these are lines from the log file if i run this query by hand from<br>
>> psql:<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-1] pgpool2 DEBUG:<br>
>> decide where to send the queries<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-2] pgpool2 DETAIL:<br>
>> destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM<br>
>> pg_type WHERE typname = ANY('{hstore,geometry}'::<wbr>varchar[]);"<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-3] pgpool2 LOCATION:<br>
>> pool_query_context.c:429<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-1] pgpool2 DEBUG:<br>
>> system catalog walker, checking relation "pg_type"<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-2] pgpool2 LOCATION:<br>
>> pool_select_walker.c:374<br>
>> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432781-1] pgpool2 DEBUG:<br>
>> do_query: extended:0 query:"SELECT count(*) FROM pg_class AS c,<br>
>> pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace<br>
>> = n.oid AND n.nspname = 'pg_catalog'"<br>
>> ><br>
>> > There is a difference - when request is sent from erlang, then<br>
>> "extended:1", but when i ru query from psql, then "extended:0". Perhaps<br>
>> this is important?<br>
>><br>
>> Maybe. erlang seems to use "extended query", which is also used in Java.<br>
>><br>
>> > I understand that it looks like the problem on the erlang library side,<br>
>> but perhaps you, basing on your pgpool knowledge, could suggest what is<br>
>> causing this incompatibility between erlang pgsql library and pgpool?<br>
>> > Perhaps this old thread has something in common with my problem:<br>
>> <a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2011-" rel="noreferrer" target="_blank">http://lists.pgfoundry.org/<wbr>pipermail/pgpool-general/2011-</a><br>
>> February/003409.html ?<br>
>><br>
>> Not sure. Extended query is pretty complex. Extended query users for<br>
>> Pgpool-II are mostly Java. So it is possible that erlang (or pgapp)<br>
>> reveleas new bug with Pgpool-II. Your Pgpool-II log is imcoplete. To<br>
>> invesigate the problem, I need complete Pgpool-II log: starting from<br>
>> the query issued to the point Pgpool-II hangs.<br>
>><br>
>> Also please let us know what version of Pgpool-II you using exactly.<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>
>> ______________________________<wbr>_________________<br>
>> pgpool-general mailing list<br>
>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/<wbr>listinfo/pgpool-general</a><br>
>><br>
</div></div></blockquote></div><br></div>