View Issue Details

IDProjectCategoryView StatusLast Update
0000072Pgpool-IIBugpublic2013-09-19 17:05
Reporterg.sorbaraAssigned Tot-ishii 
PriorityhighSeverityblockReproducibilityalways
Status resolvedResolutionopen 
PlatformOSCentOS OS Versionrelease 6.4
Product Version 
Target VersionFixed in Version 
Summary0000072: query cache invalidation fails on fq table name
DescriptionThe invalidation of cache doesn't work correctly when using table with a fully qualified name <schema>.<table>
This prevents us to consider pgpool for production environment as we rely heavily on fully qualified table names.
Steps To Reproduce- create a table on a schema different from public
- schema must not be part of the search_path variable
- insert values in table
- select from table (cache is loaded and hit)
- delete from table (cache is not invalidated)
- select from table (getting old results)
Additional InformationSetting on our test machine

memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 3600
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''
TagsNo tags attached.

Activities

t-ishii

2013-09-09 20:27

developer   ~0000321

Did not reproduce here.

test=# create schema foo;
CREATE SCHEMA
test=# create table foo.t1(i int);
CREATE TABLE
test=# insert into foo.t1 values(1);
INSERT 0 1
test=# select * from foo.t1;
 i
---
 1
(1 row)

test=# select * from foo.t1;
 i
---
 1
(1 row)

# note that this query fetches data from cache
# 2013-09-09 18:56:25 LOG: pid 15695: query result fetched from cache.

statement: select * from foo.t1;

test=# delete from foo.t1;
DELETE 1
test=# select * from foo.t1;
 i
---
(0 rows)

g.sorbara

2013-09-09 20:59

reporter   ~0000322

Last edited: 2013-09-09 21:01

View 2 revisions

Probably I am missing something somewhere on my side:

sdw_datastore=# create schema foo;
CREATE SCHEMA
sdw_datastore=# create table foo.t1(i int);
CREATE TABLE
sdw_datastore=# insert into foo.t1 values(1);
INSERT 0 1
sdw_datastore=# select * from foo.t1;
 i
---
 1
(1 row)

sdw_datastore=# select * from foo.t1;
 i
---
 1
(1 row)

sdw_datastore=# delete from foo.t1;
DELETE 1
sdw_datastore=# select * from foo.t1;
 i
---
 1
(1 row)

++++++++++++++++++

Postgres 9.0.13
PgPool-II 3.3.1 compiled from source

t-ishii

2013-09-09 21:07

developer   ~0000324

Can you show debug log? You can get debug log by restarting pgpool-II with -d option.

g.sorbara

2013-09-09 21:35

reporter  

debug.log (48,515 bytes)

t-ishii

2013-09-12 12:13

developer   ~0000328

It seems you did not install pgpool_regclass. Without the function, pgpool cannot handle schema well.

g.sorbara

2013-09-18 17:06

reporter   ~0000331

First tests show that pgpool is working correctly. Thank you.

t-ishii

2013-09-19 17:04

developer   ~0000333

You are welcome.

Issue History

Date Modified Username Field Change
2013-09-09 17:55 g.sorbara New Issue
2013-09-09 20:27 t-ishii Note Added: 0000321
2013-09-09 20:28 t-ishii Assigned To => t-ishii
2013-09-09 20:28 t-ishii Status new => feedback
2013-09-09 20:59 g.sorbara Note Added: 0000322
2013-09-09 20:59 g.sorbara Status feedback => assigned
2013-09-09 21:01 g.sorbara Note Edited: 0000322 View Revisions
2013-09-09 21:07 t-ishii Note Added: 0000324
2013-09-09 21:35 g.sorbara File Added: debug.log
2013-09-12 12:13 t-ishii Note Added: 0000328
2013-09-12 12:13 t-ishii Status assigned => feedback
2013-09-18 17:06 g.sorbara Note Added: 0000331
2013-09-18 17:06 g.sorbara Status feedback => assigned
2013-09-19 17:04 t-ishii Note Added: 0000333
2013-09-19 17:05 t-ishii Status assigned => resolved