search

pgpool / pgpool2

This is the official mirror of git://git.postgresql.org/git/pgpool2.git. Note that this is just a *mirror* - we don't work with pull requests on github. Please subscribe to pgpool-hackers mailing list from our website and submit your patch to this mailing list.
https://www.pgpool.net
Other
305 stars 87 forks source link

Query cache invalidation does not work as expected in simple transaction #19

Closed Zatvobor closed 5 years ago

Zatvobor commented 5 years ago

Hi There.

I have found an issue with query cache invalidation (environment/configuration details below) logic. Cache invalidation does not work in transaction (for me at least). Note, it works as expected with out transaction.

I tried to make an update in simple transaction. After that, I still got a cached result...

staging=# SELECT current from "memberships" WHERE "id" = 6308;
 current
---------
 f
(1 row)

staging=# BEGIN;
BEGIN
staging=# UPDATE "memberships" SET "current" = TRUE WHERE "id" = 6308;
UPDATE 1
staging=# COMMIT;
COMMIT
staging=# SELECT current from "memberships" WHERE "id" = 6308;
 current
---------
 f
(1 row)

staging=# SELECT current from "memberships" WHERE "id" = 6308 LIMIT 1;
 current
---------
 t
(1 row)

here what pgpool said:

2018-11-04 17:49:48: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: SELECT oid FROM pg_database WHERE datname = 'staging'
2018-11-04 17:49:48: pid 12: LOCATION:  pool_proto_modules.c:3290
2018-11-04 17:50:20: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: SELECT current from "memberships" WHERE "id" = 6308;
2018-11-04 17:50:20: pid 12: LOCATION:  pool_proto_modules.c:3290
2018-11-04 17:50:35: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: BEGIN;
2018-11-04 17:50:35: pid 12: LOCATION:  pool_proto_modules.c:3290
2018-11-04 17:50:50: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: UPDATE "memberships" SET "current" = TRUE WHERE "id" = 6308;
2018-11-04 17:50:50: pid 12: LOCATION:  pool_proto_modules.c:3290
2018-11-04 17:50:54: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: COMMIT;
2018-11-04 17:50:54: pid 12: LOCATION:  pool_proto_modules.c:3290
2018-11-04 17:51:01: pid 12: LOG:  fetch from memory cache
2018-11-04 17:51:01: pid 12: DETAIL:  query result fetched from cache. statement: SELECT current from "memberships" WHERE "id" = 6308;
2018-11-04 17:51:01: pid 12: LOCATION:  pool_memqcache.c:709
2018-11-04 17:53:15: pid 12: LOG:  DB node id: 0 backend pid: 11591 statement: SELECT current from "memberships" WHERE "id" = 6308 LIMIT 1;
2018-11-04 17:53:15: pid 12: LOCATION:  pool_proto_modules.c:3290

My question here, is that ok, or I've missed something? pgpool v4.0.1 with postgres v10.1 (configs are available over gist, https://gist.github.com/Zatvobor/8a80d718a37a956d404edfcc3bcc04d0).

My thanks to you for help.

tatsuo-ishii commented 5 years ago

It turned out it is a bug with Pgpool-II. If there's DMLs in an explicit transaction, info needed to invalidate query cache is wiped out while committing it. I will commit/push a fix to upstream git repository.

Zatvobor commented 5 years ago

🆒 got it. Many thanks to you.