[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