[pgpool-general: 5493] Re: pgpool and erlang - request hangs

[Ankur Jain]

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 (
https://github.com/brianc/node-postgres/issues/1255) , 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.

-AJ

On Wed, May 17, 2017 at 5:37 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> I'm not familiar with node.js so I would like to ask you what
> "parameterized queries" means here. Is it SQL level ones (PREPARE,
> EXECUTE) or using extended query protocol? The info needed to
> reproduce the problem.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > 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.
> > -AJ
> >
> > On Wed, May 17, 2017 at 4:44 PM, Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >
> >> > Hello.
> >> >
> >> > If  I  try to establish connection between pgpool and erlang (using
> >> pgapp library https://github.com/epgsql/pgapp  )
> >> > then  session  simply  hangs after erlang  client  sends command
> >> > SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname =
> >> ANY($1::varchar[])
> >> > parameters: $1 = '{hstore,geometry}'
> >> > If erlang application connects to PgSQL server directly, then
> connection
> >> is established without problems.
> >> >
> >> > However, if i connect via psql to pgpool and issue the same statement
> -
> >> > SELECT typname, oid::int4, typarray::int4 FROM pg_type WHERE typname =
> >> ANY('{hstore,geometry}'::varchar[]);
> >> > then this request is processed normally:
> >> >  typname |  oid  | typarray
> >> > ---------+-------+----------
> >> >  hstore  | 19898 |    19903
> >> > (1 row)
> >> >
> >> >
> >> > 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.
> >> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432086-1] pgpool2 DEBUG:
> >> decide where to send the queries
> >> > 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[])"
> >> > May 17 18:02:02 ip-172-31-20-153 [23121]: [432087-1] pgpool2 DEBUG:
> >> system catalog walker, checking relation "pg_type"
> >> > 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'"
> >> >
> >> > and these are lines from the log file if i run this query by hand from
> >> psql:
> >> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-1] pgpool2 DEBUG:
> >> decide where to send the queries
> >> > 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}'::varchar[]);"
> >> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432779-3] pgpool2 LOCATION:
> >> pool_query_context.c:429
> >> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-1] pgpool2 DEBUG:
> >> system catalog walker, checking relation "pg_type"
> >> > May 17 18:12:24 ip-172-31-20-153 [23539]: [432780-2] pgpool2 LOCATION:
> >> pool_select_walker.c:374
> >> > 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'"
> >> >
> >> > 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?
> >>
> >> Maybe. erlang seems to use "extended query", which is also used in Java.
> >>
> >> > 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?
> >> > Perhaps this old thread has something in common with my problem:
> >> http://lists.pgfoundry.org/pipermail/pgpool-general/2011-
> >> February/003409.html ?
> >>
> >> Not sure. Extended query is pretty complex. Extended query users for
> >> Pgpool-II are mostly Java. So it is possible that erlang (or pgapp)
> >> reveleas new bug with Pgpool-II. Your Pgpool-II log is imcoplete. To
> >> invesigate the problem, I need complete Pgpool-II log: starting from
> >> the query issued to the point Pgpool-II hangs.
> >>
> >> Also please let us know what version of Pgpool-II you using exactly.
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >> _______________________________________________
> >> pgpool-general mailing list
> >> pgpool-general at pgpool.net
> >> http://www.pgpool.net/mailman/listinfo/pgpool-general
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170517/a31aba4b/attachment.html>