[pgpool-general: 5534] Re: Issue with parameterized query

Tatsuo Ishii ishii at sraoss.co.jp
Sat May 27 05:32:32 JST 2017


I think I found the cause of the problem.
It seems node.js sends a query in following pattern:

Parse
Bind
Describe
Execute
Flush
Sync

The "Flush" message is actually unnecessary: it just requests backend
(in this case Pgpool-II) to send any result pending in the
backend. However, which will be accomplished by the subsequent "Sync"
message. So the Flush message is completely redundant here.

Anyway, the pattern is not supposed in Pgpool-II.
Can you please try the attached fix?

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

> Hi Tatsuo,
> Thanks for looking into it, I am just coping you and my team-mates instead
> of spamming the whole group.
> 
> I have attached more details, hopefully this helps you. I have attached a
> node.js script (*pgpool_test.js.txt, *I had to add a .txt extension because
> gmail would not allow me to attach a file with a .js extension) which
> creates a table* public.test *with just one column (id) and one row (id=1).
> It then runs "select  * from public.test where id=1" twice using a
> non-parametrized query and then again twice using a parameterized query.
> 
>  As you can see in *output.log*, the table creation and the first two non
> parametrized queries run fine. The first parameterized query also runs fine
> (because it wasn't cached). The second parametrized query never returns and
> node script just hangs.
> 
> I am also attaching the pgpool output log (*pgpool.log*) and the tcp packet
> capture (*dump.out*, viewable in wireshark) that captured all the tcp
> traffic between the client and the machine running pgpool. Let me know if
> you need anything else.
> 
> -AJ
> 
> If you want to run the node.js script yourself, here are the instructions
> to do that (on a linux machine)
> 
>    - Install node (the instructions would be machine specific)
>    - npm install pg
>    - npm install seq
>    - PGPORT=<PGPOOL_PORT> PGDATABASE=<DATABASE_NAME> PGHOST=<PH_HOST_NAME>
>    PGUSER=<PG_USERNAME> node  pgpool_test.js
> 
> 
> On Fri, May 19, 2017 at 12:30 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi,
>> > I am having an issue with using parameterized queries with pgpool. My
>> setup
>> > involves using node.js client with pgpool (3.6.4) and postgresl 9.6. I am
>> > using pgpool just for caching using shmem.
>> >
>> > I have a simple example with the following query "SELECT * FROM
>> > master.employer where employer_id=$1".  The query returns fine the first
>> > time and then it gets cached. When I run it again it hits the cache, but
>> > returns an empty resultset.
>> >
>> > Let me know if I can provide with any other details. I have attached the
>> > entire pgpool log.
>>
>> In the log I see:
>>
>> 2017-05-18 21:13:30: pid 22781: DEBUG:  memcache: sending cached messages:
>> 'D' len: 4917
>> 2017-05-18 21:13:30: pid 22781: DEBUG:  memcache: sending cached messages:
>> 'C' len: 13
>>
>> which means first Pgpool-II sends 'D' (actual row data) in 4917 bytes
>> then 'C' (Indicating select command completed). This is perfectly sane
>> from the protocol's point of view. So as far as the log says,
>> Pgpool-II successfully returns cached data to client. So the remaining
>> possibility is the returned data is broken. I don't what kind of debug
>> method node.js has, but is it possible to capture/dump the packet data
>> using wireshark?
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: node.js.diff
Type: text/x-patch
Size: 1916 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170527/26e4ce1c/attachment.bin>


More information about the pgpool-general mailing list