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

[Tatsuo Ishii]

> 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).
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

  I get the same error with either of the following (when
> supplying the same values for all 9 parameters):
> 
> pg_execute($db,a1a3a2f461a0efc2eee2f60f940df6b77eb552f9,"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") ;
> 
> 
> 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") ;