[pgpool-general: 1011] Re: using prepared statements when memory_cache_enabled=on
ishii at postgresql.org
Sun Sep 16 16:18:20 JST 2012
>> 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
>>>>>> 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:
>>>> 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.
>>> * 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:
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?
SRA OSS, Inc. Japan
More information about the pgpool-general