View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000095 | Pgpool-II | Bug | public | 2014-02-26 08:47 | 2015-09-17 15:29 |
| Reporter | coreyh | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | closed | Resolution | reopened | ||
| Platform | Linux | OS | Ubuntu | OS Version | 12.04 |
| Summary | 0000095: Query Cache does not recognize when a Materialized view has been refreshed. | ||||
| Description | The query cache continues to serve stale values for a query of a materialized view after a refresh of that same materialized view. | ||||
| Steps To Reproduce | 1. Find a materialized view with data that should change momentarily. 2. Select from that materialized view using a connection that goes through query caching. 3. Confirm that the query was cached. 4. Do some update operation that will affect the materialized view. 5. In another session not connected via pgpool, refresh the materialized view 6. In the pgpool session, select from the materialized view again and verify that the stale results were not purged. | ||||
| Additional Information | The zip file attached contains two files: - one is the transcript of the uncached, unpooled session which created the materialized view, waited for the user to query the table and get the results into the cache, then refreshed the materialized view and verified that locally the changes are seen. - the second is a transcript of the pooled+cached session which selected from the materialized view, after it was initially created, and again after it was refreshed. The query cache did not detect the change, so it reported the now-stale values. Timestamps are included to aid in the timing of attempts to reproduce the bug. | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
Good catch. Pgpool-II does not cache if a table is a view. However if the table is a materialized view, it does not realize that the materialized view should not be cached. |
|
|
Fix committed. http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=4a772e468be7404fcf39efb26e9b7c0f3903ce1b |
|
|
While simply not caching materialized views is a simple solution, it prevents us from caching statements against views that don't change very often (in this case, once per day). I am told that the relfilenode in pg_class will change every time the view is refreshed. Could that be used as the trigger to determine whether or not to flush the cache of statements involving that materialized view instead? |
|
|
I don't know if that is true forever. In the future it could be possible that MV is refreshed by updating part of it, instead of replacing whole MV contents. I would think that REFRESH command can be better trigger. Unfortunately pgpool-II's parser does not recognize the command since it's imported from older version of PostgreSQL. So, if we upgrade our parser in the future, my idea would be doable. |
|
|
No feed back over 1 year. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-02-26 08:47 | coreyh | New Issue | |
| 2014-02-26 08:47 | coreyh | File Added: pgpool2_query_cache_bug.zip | |
| 2014-02-26 11:29 | t-ishii | Note Added: 0000394 | |
| 2014-02-26 11:30 | t-ishii | Assigned To | => t-ishii |
| 2014-02-26 11:30 | t-ishii | Status | new => assigned |
| 2014-02-26 15:54 | t-ishii | Note Added: 0000395 | |
| 2014-02-26 15:54 | t-ishii | Status | assigned => resolved |
| 2014-02-26 16:01 | t-ishii | Changeset attached | => pgpool2 master 4a772e46 |
| 2014-03-26 02:31 | coreyh | Note Added: 0000397 | |
| 2014-03-26 02:31 | coreyh | Status | resolved => feedback |
| 2014-03-26 02:31 | coreyh | Resolution | open => reopened |
| 2014-03-27 08:38 | t-ishii | Note Added: 0000399 | |
| 2014-05-07 21:02 | t-ishii | Changeset attached | => pgpool2 master dbf7db2d |
| 2015-09-17 15:29 | t-ishii | Note Added: 0000563 | |
| 2015-09-17 15:29 | t-ishii | Status | feedback => closed |
