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

[Tatsuo Ishii]

Oh, ok.

Then your problem is totally different from erlang problems because
erlang seems to not use SQL level PREPARED statements.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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
>> >>
>>