[pgpool-general: 2142] query cache?
Torsten Förtsch
torsten.foertsch at gmx.net
Wed Sep 18 03:54:44 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.
Are there other invalidation methods? Something like
/*PGPOOL: forget*/select * from torsten.xx
Thanks,
Torsten
More information about the pgpool-general
mailing list