<div dir="ltr"><div>I am not sure if this is related. By in my node.js setup (pg 6.1.5, Postgres 9.6 and pgpool-II 3.6.1), parameterized queries just don't work with pg_pool caching. Queries that use parameters like $1 etc run the first time (when they are not cached) and then subsequent ones just hang.</div><div>-AJ</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 17, 2017 at 4:44 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"><div class="HOEnZb"><div class="h5">> Hello.<br>
><br>
> If I try to establish connection between pgpool and erlang (using 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 = ANY($1::varchar[])<br>
> parameters: $1 = '{hstore,geometry}'<br>
> If erlang application connects to PgSQL server directly, then connection 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 = 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 log when erlang library is connecting, but I have no idea what other lines are significant.<br>
> May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-1] pgpool2 DEBUG: decide where to send the queries<br>
> May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-2] pgpool2 DETAIL: destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname = ANY($1::varchar[])"<br>
> May 17 18:02:02 ip-172-31-20-153 [23121]: [432087-1] pgpool2 DEBUG: system catalog walker, checking relation "pg_type"<br>
> May 17 18:02:02 ip-172-31-20-153 [23121]: [432134-1] pgpool2 DEBUG: do_query: extended:1 query:"SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace = 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 psql:<br>
> May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-1] pgpool2 DEBUG: decide where to send the queries<br>
> May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-2] pgpool2 DETAIL: destination = 2 for query= "SELECT typname, oid::int4, typarray::int4 FROM 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: pool_query_context.c:429<br>
> May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-1] pgpool2 DEBUG: system catalog walker, checking relation "pg_type"<br>
> May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-2] pgpool2 LOCATION: pool_select_walker.c:374<br>
> May 17 18:12:24 ip-172-31-20-153 [23539]: [432781-1] pgpool2 DEBUG: do_query: extended:0 query:"SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = to_regclass('"pg_type"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'"<br>
><br>
> There is a difference - when request is sent from erlang, then "extended:1", but when i ru query from psql, then "extended:0". Perhaps this is important?<br>
<br>
</div></div>Maybe. erlang seems to use "extended query", which is also used in Java.<br>
<span class=""><br>
> I understand that it looks like the problem on the erlang library side, but perhaps you, basing on your pgpool knowledge, could suggest what is causing this incompatibility between erlang pgsql library and pgpool?<br>
> Perhaps this old thread has something in common with my problem: <a href="http://lists.pgfoundry.org/pipermail/pgpool-general/2011-February/003409.html" rel="noreferrer" target="_blank">http://lists.pgfoundry.org/<wbr>pipermail/pgpool-general/2011-<wbr>February/003409.html</a> ?<br>
<br>
</span>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>
<div class="HOEnZb"><div class="h5">______________________________<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>
</div></div></blockquote></div><br></div>