[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