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

[Lonni J Friedman]

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.  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") ;