[pgpool-hackers: 1258] Re: [pgpool-3.4.3 old query cache returned after DROP/CREATE table]

[Tatsuo Ishii]

> Thanks for reply and so sorry for lately response for below thread.
> http://www.pgpool.net/pipermail/pgpool-hackers/2015-December/001201.html
> I could not reproduce above schema problem in head source of V3_4_STABLE.
> I think it was fixed in 43ff7d3de9db4c8a1143258f4ffff98682dab560 commit.
> 
> So, i found one more problem (it is undocumented) about In Memory Cache.
> That is, after DROP/CREATE/SELECT in one session, the old query cached 
> can be returned in the next session.
> 
> === reproduce queries ===
> testdb=# drop table if exists t; -- table t exists
> DROP TABLE
> testdb=# create table t(id integer);
> CREATE TABLE
> testdb=# select * from t;
>   id
>  ----
>  (0 rows)
> 
> testdb=# \q 
> $ psql -p 9999  # start new session
> psql (9.4.4)
> Type "help" for help.
> 
> testdb=# insert into t values(1);
> INSERT 0 1
> testdb=# select * from t; 
>  id
> ----
> (0 rows)
> 
> ===
> 
> I found below messages in pgpool-II log,
> 
>   []DEBUG:  memcache invalidating query cache
>   []DETAIL:  failed to open "/var/log/pgpool/oiddir/16451/16622". reason:"No such file or directory"
> 
> Confirm table's oid and oid file name, i found that, SELECT in 
> first session (above) created oid file with the name of DROPPED TABLE's 
> oid.
> Relation cache (in pgpool-II) of DROPPED table is not invalidated ?
> So in new session (relcache are cleared), INSERT command could not 
> found oid file (with new name), in order to discard query cache.
> 
> I think it is necessary to invalidate relation cache when drop table, 
> because if not it can get the old query result from cache continuously.
> Do you agree with me?

Yes, but...

> I attached a patch (for V3_4_STABLE).
> Can you confirm it for me.

I have not tried your patch yet but I think your proposal only solves
the half of the problem: because relation cache is session local (more
precisely, process local), the cache invalidation only affects the
local session. See examples below (suppose your proposal applied):

Session1: ALTER TABLE t...; --- create relcache for t
Session2: DROP TABLE t; --- invalidate relcache for t in session2, but not in session1
Session2: CREATE TABLE t...; --- create a new relcache for t in session2
Session1: SELECT * FROM t; --- create a new query cache entry using the old relcache
Session2: INSERT INTO t...; -- does not invalidate the query cache because oid is different
Session2: SELECT * FROM t; --- retrieve outdated data from the query cache

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp