View Issue Details

IDProjectCategoryView StatusLast Update
0000209Pgpool-IIBugpublic2016-12-20 14:05
ReporterochedruAssigned Tot-ishii 
Status closedResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary0000209: Query cache invalidation performed too late
DescriptionQuery cache invalidation (done by pool_handle_query_cache), is performed from ReadyForQuery() AFTER the frontend has been notified (see send_ready flag).

As a consequence, the frontend can continue querying on another connection and get inconsistent results.

The frontend must be notified only after the cache is in a consistent state.
Steps To ReproduceSee the attached testcase using Java 8/JDBC.
The expected output is like:

java.lang.RuntimeException: Got 133 but expected 134
    at Main.lambda$main$2(
    at Main$
    at java.util.concurrent.ThreadPoolExecutor.runWorker(
    at java.util.concurrent.ThreadPoolExecutor$
TagsNo tags attached.



2016-06-27 23:08

reporter (2,142 bytes)


2016-06-27 23:47

reporter   ~0000869

Uploaded new test-case with call to "executor.shutdown()": if the test passes we want the program to exit successfully.


2016-06-27 23:47

reporter (2,150 bytes)


2016-06-27 23:51

reporter   ~0000870

I could make the test pass by moving the block below at the end of function ReadyForQuery. Not sure if there are possible side effects, but my frontend application is working fine now.

    if (send_ready)
        pool_write(frontend, "Z", 1);

        if (MAJOR(backend) == PROTO_MAJOR_V3)
            len = htonl(len);
            pool_write(frontend, &len, sizeof(len));
            pool_write(frontend, &state, 1);


2016-07-01 17:19

reporter   ~0000876

FYI, actually the application is still not working fine; I have problems with cache auto invalidation after a transaction commit: query_context->temp_cache is null sometimes, thus preventing pool_handle_query_cache() to invalidate the modified table.
I'm trying to setup a testcase for this one.


2016-08-09 10:43

developer   ~0000971

I think your proposal does not solve the problem entirely.

Even if Pgpool-II invalidates query cache right after "Command Complete" message (which is sent from PostgreSQL *before* "Ready for query"), it's always possible that other client can see the old cache *after* Command Complete message and *before* the cache invalidation.
We cannot eliminate the window unless DML execution and cache invalidation is an atomic operation, which is not possible in our architecture. To implement that, the query cache needs to be built-in in PostgreSQL.


2016-08-17 17:50

reporter   ~0001007

I agree the whole operation cannot be atomic. However, the client application usually has some sort of synchronization built-in: for example, some transactions are done sequentially (c.f. test case).
IMHO, ending the transaction with the cache invalidated is better because the inconsistency window is smaller.
However the fix does not work because sometimes invalidation is not performed: query_context->temp_cache is null and I do not know why.


2016-12-20 14:05

developer   ~0001241

I have decided we are not going change the current behavior. Sorry.

Issue History

Date Modified Username Field Change
2016-06-27 23:08 ochedru New Issue
2016-06-27 23:08 ochedru File Added:
2016-06-27 23:47 ochedru Note Added: 0000869
2016-06-27 23:47 ochedru File Added:
2016-06-27 23:51 ochedru Note Added: 0000870
2016-07-01 15:27 t-ishii Assigned To => t-ishii
2016-07-01 15:27 t-ishii Status new => assigned
2016-07-01 17:19 ochedru Note Added: 0000876
2016-08-09 10:43 t-ishii Note Added: 0000971
2016-08-09 10:43 t-ishii Status assigned => feedback
2016-08-17 17:50 ochedru Note Added: 0001007
2016-08-17 17:50 ochedru Status feedback => assigned
2016-12-20 14:05 t-ishii Note Added: 0001241
2016-12-20 14:05 t-ishii Status assigned => closed