[pgpool-general: 5541] Re: Issue with parameterized query
Tatsuo Ishii
ishii at sraoss.co.jp
Wed May 31 10:11:54 JST 2017
BTW, which PostgreSQL driver are you using? It is possible the author
of the library misunderstand the frontend/backend protocol.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From: Ankur Jain <ankur.jain at obeohealth.com>
Subject: Re: [pgpool-general: 5511] Issue with parameterized query
Date: Tue, 30 May 2017 16:40:12 -0700
Message-ID: <CACvFWAq_TkVphnYySJYFRp+8JHeQ7L_BYAHyZE7opa7JkVXTyA at mail.gmail.com>
> Hi Tatsuo,
> I just tried the patch on 3.6.4 and it works. Thanks!
> When do you think this will become available in the yum repositories?
> -AJ
>
> On Fri, May 26, 2017 at 1:32 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> 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
>> >>
>>
>> diff --git a/src/query_cache/pool_memqcache.c b/src/query_cache/pool_
>> memqcache.c
>> index fdef8c1..b8a5a5a 100644
>> --- a/src/query_cache/pool_memqcache.c
>> +++ b/src/query_cache/pool_memqcache.c
>> @@ -645,43 +645,9 @@ POOL_STATUS pool_fetch_from_memory_cache(POOL_CONNECTION
>> *frontend,
>> pfree(qcache);
>>
>> /*
>> - * If we are doing extended query, forward sync message from
>> frontend to
>> - * backend. This is necessary to prevent receiving Sync message
>> after
>> - * Sending Ready for query.
>> + * Send a "READY FOR QUERY" if not in extended query.
>> */
>> - if (pool_is_doing_extended_query_message())
>> - {
>> - char kind;
>> - int32 len;
>> - POOL_SESSION_CONTEXT *session_context;
>> - POOL_CONNECTION *target_backend;
>> - char buf[5];
>> -
>> - if (pool_flush(frontend))
>> - return POOL_END;
>> - if (pool_read(frontend, &kind, 1))
>> - return POOL_END;
>> -
>> - ereport(DEBUG2,
>> - (errmsg("memcache: fetching from memory
>> cache: expecting sync: kind '%c'", kind)));
>> - if (pool_read(frontend, &len, sizeof(len)))
>> - return POOL_END;
>> -
>> - /* Forward "Sync" message to backend */
>> - session_context = pool_get_session_context(true);
>> - target_backend = CONNECTION(backend,
>> session_context->load_balance_node_id);
>> - pool_write(target_backend, &kind, 1);
>> - pool_write_and_flush(target_backend, &len, sizeof(len));
>> -
>> - /* Read and discard "Ready for query" message from backend
>> */
>> - pool_read(target_backend, &kind, 1);
>> - pool_read(target_backend, buf, sizeof(buf));
>> - }
>> -
>> - /*
>> - * send a "READY FOR QUERY"
>> - */
>> - if (MAJOR(backend) == PROTO_MAJOR_V3)
>> + if (!pool_is_doing_extended_query_message() && MAJOR(backend) ==
>> PROTO_MAJOR_V3)
>> {
>> signed char state;
>>
>> @@ -691,10 +657,7 @@ POOL_STATUS pool_fetch_from_memory_cache(POOL_CONNECTION
>> *frontend,
>> state = MASTER(backend)->tstate;
>> send_message(frontend, 'Z', 5, (char *)&state);
>> }
>> - else
>> - {
>> - pool_write(frontend, "Z", 1);
>> - }
>> +
>> if (pool_flush(frontend))
>> {
>> return POOL_END;
>>
>>
More information about the pgpool-general
mailing list