[pgpool-general: 2143] Re: query cache?

Tatsuo Ishii ishii at postgresql.org
Wed Sep 18 05:34:33 JST 2013


> Hi,
> 
> I am trying to utilize pgpool as query cache. My question is how to
> invalidate the cache. Current invalidation methods seem to work quite
> unreliable.
> 
> These 5 commands given as one line
> 
>   select * from torsten.xx;
>   insert into torsten.xx values(4, 44, 'dd');
>   select * from torsten.xx;
>   select pg_sleep(11), clock_timestamp();
>   select * from torsten.xx;
> 
> produce this output:
> 
>  id | id2 | txt
> ----+-----+-----
>   1 |  11 | aa
>   2 |  22 | bb
>   3 |  33 | cc
> (3 rows)
> 
> Time: 0.757 ms
> INSERT 0 1
> Time: 1.118 ms
>  id | id2 | txt
> ----+-----+-----
>   1 |  11 | aa
>   2 |  22 | bb
>   3 |  33 | cc
> (3 rows)
> 
> Time: 0.128 ms
>  pg_sleep |        clock_timestamp
> ----------+-------------------------------
>           | 2013-09-17 17:20:36.265542+00
> (1 row)
> 
> Time: 11011.639 ms
>  id | id2 | txt
> ----+-----+-----
>   1 |  11 | aa
>   2 |  22 | bb
>   3 |  33 | cc
>   4 |  44 | dd
> (4 rows)
> 
> Time: 0.802 ms
> 
> Obviously, the 2nd select result comes from the cache although there was
> an insert into the table before that. It takes one memqcache_expire
> period (10 sec) to make the cache forget the wrong result.
> 
> If schema names are omitted in the example above everything works. But
> it still feels very fragile if such a small change breaks things.

To handle schema qualified tables names, installing pgpool_egclass is
required. Are you sure that you have installed pgpool_regclass?

> Are there other invalidation methods? Something like
> 
>  /*PGPOOL: forget*/select * from torsten.xx

Currently we don't have such methods.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-general mailing list