[pgpool-hackers: 2672] Query cache and portal suspended

[Tatsuo Ishii]

Currently Pgpool-II's query cache works well if an execute message
returns whole rows from PostgreSQL.

However I think there's a problem if an execute message does not
return whole rows.

If the execute message specifies the maximum number of rows to return
and the number of returned rows reached to the limit, a cache entry is
created with the number of rows. If this is it, it will be
fine. However, it is possible for the user to issue another execute
message to retrieve the rest of the rows with the same number of max
rows, then the cached result can be returned. This is a problem.

Moreover, if the subsequent execute message specifies the max rows to
0, which means "no limit", then another cache entry will be created
because the parameters for the execute message are different and this
may cause problems later on.

Here is an example:

Suppose there is a table includes 1000 rows.

1. execute (max rows = 500)

2. a cache entry for #1 is created (rows = 500).

3. execute (max rows = 500)

4. the result is retrieved from cache #2. Number of rows is correct
   but the contents of rows are not correct (problem!).

5. execute (max rows = 0)

6. a cache entry for #5 is created (rows = 500, because another 500
   rows have been already retrieved by #1).

7. Session ends. Another session starts.

8. execute (max rows = 0)

9. the result is retrieved from cache #6. Number of rows is incorrect
   (problem!).
   
Any idea how to deal with the problem?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp