pgpool query cache

[lilei1128]

I have a question, that fetching query result from memcache may get old value? For example: In pgpool code Implementation: DML/DDL invalidate cache in ReadyForQuery function but the DML/DDL committed , so if failed to invalidate the cache , nexted-query will get a old wrong result.

hope reply, thank you!

[tatsuo-ishii]

so if failed to invalidate the cache ,

In what case the cache invalidation failed? You mean something like this?

BEGIN;
INSERT INTO t1 VALUES ...;
ABORT;

[lilei1128]

The time for pgpool to invalidate cache is after the transaction is committed. However, if some errors occur during invalidation, resulting in invalidation failure, the next query will return the old results.

[tatsuo-ishii]

if some errors occur during invalidation,

What are "some errors"? Besides bugs of pgpool I cannot think of errors during the invalidation. Or you actually have found any bugs?

[lilei1128]

I haven't verified it, but from the pgpool code I see that even if the invalidation fails, no other processing is done. For example, if the invalidation of memcache fails, what is the behavior?

[tatsuo-ishii]

Emit log and give up invalidation. See the source code for more details.

[lilei1128]

Although the emit fails to invalidate the log, the next query will still be able to read the old error result from the cache, right?

[tatsuo-ishii]

That depends on what kind of error happened with memcached. If that's a network error, subsequent fetching attempt likely fails too. In that case pgpool disables use of query cache.I hardly think the case only deleting item on memcached fails but fetcing data from memcached suceeds.

[lilei1128]

Can inconsistency be avoided if invalidation is performed before transaction commit?

[tatsuo-ishii]

Assuming you are talking about an explicit transaction case, no it causes inconsistency if the transaction is rolled back, rather than committed. Until commit or rollback (abort) command is arrived from the client, there's no way to judge invalidation should be done or not.

[lilei1128]

For exaple: T1: select * from tab; -- the result will be save in cache;

T2: BEGIN; DELETE From tab; COMMIT; -- when execute the transaction the operation, invalidate the cache. If the process of invalidate operation has any error just rollback the transaction.

[tatsuo-ishii]

COMMIT; -- when execute the transaction the operation, invalidate the cache. If the process of invalidate operation has any error just rollback the transaction.

Pgpool Invalidates the cache after the completion of COMMIT command in PostgreSQL. i.e.

1. Pgpool sends COMMIT to PostgreSQL
2. PostgreSQL commits the transaction and replies back with Command Complete, then Ready for query
3. Pgpool invalidates the query cache

So it's too late for Pgpool to rollback the transaction at 3.

[lilei1128]

Considering adding query cache to postgresql, can the invalidate operation be performed before the transaction is committed?

[tatsuo-ishii]

I haven't tried it before but it seems there's no reason that is impossible.

[lilei1128]

Thanks for your time.