[pgpool-general: 1082] Re: using prepared statements when memory_cache_enabled=on

[Lonni J Friedman]

 On Sun, Sep 30, 2012 at 12:42 PM, Lonni J Friedman <netllama at gmail.com> wrote:
> On Sat, Sep 29, 2012 at 5:07 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> On Sun, Sep 16, 2012 at 12:18 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>> On Fri, Sep 14, 2012 at 3:37 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>>> On Fri, Sep 14, 2012 at 3:09 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>>>>>>>>> On Thu, Sep 13, 2012 at 6:35 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>>>>>>> pg_* functions.  Stuff like pg_prepare, pg_exec, pg_query.
>>>>>>>>>>>
>>>>>>>>>>> Can you show us pg_prepare example? I'm interested in what statement
>>>>>>>>>>> names you are using.
>>>>>>>>>>
>>>>>>>>>> pg_prepare($db, $preparedStatementKey, "SELECT
>>>>>>>>>> id,pass,fail,filtercount,info,current_status,last_update,os,arch,branch,gpu,build_type,subtest,osversion
>>>>>>>>>> FROM cudasmoke WHERE (last_update > $1 AND last_update < $2) AND os=$3
>>>>>>>>>> AND arch=$4 AND branch=$5 AND build_type= $6 AND subtest=$7 AND
>>>>>>>>>> osversion=$8 AND gpu=$9 ORDER BY last_update DESC LIMIT 1" )
>>>>>>>>>
>>>>>>>>> So what's the content of $preparedStatementKey?
>>>>>>>>
>>>>>>>> Its a unique identifier string for the prepared statement, comprised
>>>>>>>> of the concatenated sha1sum and md5sum of the prepared statement.
>>>>>>>>
>>>>>>>> So it ends up looking something like this:
>>>>>>>> pg_execute($db,a1a3a2f461a0efc2eee2f60f940df6b77eb552f9f5a985165c3ab1665384fa9cce8b1b62,"SELECT
>>>>>>>> id,pass,fail,filtercount,info,current_status,last_update,os,arch,branch,gpu,build_type,subtest,osversion
>>>>>>>> FROM cudasmoke WHERE (last_update > $1 AND last_update < $2) AND os=$3
>>>>>>>> AND arch=$4 AND branch=$5 AND build_type= $6 AND subtest=$7 AND
>>>>>>>> osversion=$8 AND gpu=$9 ORDER BY last_update DESC LIMIT 1);
>>>>>>>
>>>>>>> The statement name is 72 byte long. This is interesting because
>>>>>>> PostgreSQL (and pgpool) does not suppose that statement name is longer
>>>>>>> than 64.  Not sure how this affects to the problem though.
>>>>>>>
>>>>>>> src/include/pg_config_manual.h:
>>>>>>> /*
>>>>>>>  * Maximum length for identifiers (e.g. table names, column names,
>>>>>>>  * function names).  Names actually are limited to one less byte than this,
>>>>>>>  * because the length must include a trailing zero byte.
>>>>>>>  *
>>>>>>>  * Changing this requires an initdb.
>>>>>>>  */
>>>>>>> #define NAMEDATALEN 64
>>>>>>
>>>>>> That is interesting, and I was unaware of this limitation.  Anyway,
>>>>>> even if, for the purposes of experimentation, I only use the sha1sum
>>>>>> (and drop the md5sum) such that the identifier is reduced in size to
>>>>>> 40 bytes, I still see the same failure.  However, one thing that I
>>>>>> noticed is that pgpool doesn't seem to realize that I've attempted to
>>>>>> use a new prepared statement identifier (now just the sha1sum, without
>>>>>> md5sum), and still tries to use the previously cached cached one.
>>>>>>
>>>>>> I'd expect that if I'm preparing a statement with an unused (new)
>>>>>> identifier, it shouldn't pull it from the cache, but it appears to do
>>>>>> so anyway.
>>>>>
>>>>> Why do you think so? If SELECT(and with same parameters) is identical,
>>>>> it should return exactly same results regardress the statement name(of
>>>>> courese I do not account underlying tables get modified).
>>>>
>>>> BTW, we have recently fixed a bug which can cause buffer overrun error:
>>>> http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=3964d8204373473e3130a9ba5b260049420dafb6
>>>>
>>>> Because it's a memory destruction error, it may or may not be related to your problem. Can you try with pgpool-II 3.2-stable head?
>>>
>>> ok, I've done that now, but the same problem is still present.
>>
>> I seem to find the cause of problem and created patch. Please take a
>> look at bugtrack #21.
>>
>> http://www.pgpool.net/mantisbt/view.php?id=21
>>
>> The patch is here:
>> http://www.pgpool.net/mantisbt/file_download.php?file_id=28&type=bug
>
> I applied the patch, rebuilt everything, enabled memcache, restarted
> pgpool, and retested.  Unfortunately, nothing has changed or improved,
> and the same error persists.

Where do we stand at this point, since your patch did not help?  Is
there additional information that I can collect via enhanced logging,
or some other mechanism?