[pgpool-general: 9439] Re: Clarification on query results cache visibility

[Achilleas Mantzios - cloud]

Hello Tatsuo

On 4/28/25 01:52, Tatsuo Ishii wrote:
> Hello Achilleas,
>
>>>>> So we may need to try to reproduce it with java and send you the full
>>>>> working bug. Would you like us to to do this?
>>> No, I am not familiar with Java applications.
>> Ok, the JDBC driver handles prepared statements using the low level
>> protocol : Parse, Bind, Execute flow (Extended Query protocol):
>>
>> https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements
>>
>> https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
>>
>> If the low level protocol is involved somehow in the bug I am unaware
>> how it could be reproduced. But at the moment we are unsure about
>> this.
> I am familiar with the extended query protocol. I meant I am not
> familar with Java language and wildfly application server, and I can
> not run a test program for wildfly even if you could provide that.  On
> the other hand, if you could find a reproducable test case using
> wildfly, you enable log_client_messages of pgpool and can get logs
> which can be replayed by pgproto program which is coming with
> pgpool. That will greatly help us to find out bugs within pgpool.

Thanks! Expect another detailed email later today.

If we manage to reproduce with java it will be just plain vanilla java 
standard edition (no wildfly or any other middleware of any sort), ready 
to be run from any system with java and postgresql jdbc driver in place, 
which is fairly easy to do.

>
>>>>   From the tests I am doing today, I am very confident that this hasn't
>>>> got to do with search_path.
>>>>
>>>> It is like it created a "hidden" cache entry that is only surfacing
>>>> under some conditions.
>>>>
>>>> Please look :
>>> How did you set search_path? According to the explanation above, the
>>> search_path was set by Java app.
>> Like I wrote before :
>>
>> <new-connection-sql>SET application_name to 'SMA ';select
>> set_search_path('bdynacom,epaybdynacom,epay');</new-connection-sql>
>>
>> it runs the above at the start of every connection.
> So, although you did not include the command in the psql session, you
> actualy executed in the begging of the psql session something like:
>
> SET application_name to 'SMA ';select set_search_path('bdynacom,epaybdynacom,epay');
Not in all tests, we are trying to reproduce in SQL, I will come back 
today with all the logging in place, as much closer to the java app we can.
> (I don't think <new-connection-sql> or </new-connection-sql> are valid
> SQL, and removed them in above.)
>
>>>> straight to pgsql :
>>>>
>>>> achill at smadevnu:~ % psql -U amantzio
>>> Maybe you forgot to copy database name here? I am asking because if
>>> the database name is missing, psql will connect to "amantzio"
>>> database, which is different database from the pgpool session.
>> No,  I am using PgSQL since 2000, I'd be a very lousy dba if I made
>> such mistakes. |PGDATABASE is set to : dynacom , and there is no db :
>> amantzio.
> Ok, you set the PGDATABASE environment variable. That makes sense.
>
>>>> only different thing is the space in the select ,
>>> That's an expected behavior. When pgpool fetches a cache entry, it is
>>> based on the exact string match of the query strings. So even a white
>>> space matters.
>> Ok but seeing stale and irrelevant results for the same query is not
>> right.
> Of course.
>
>> Maybe you could thing of using postgresql's queryid ? just saying
> Probably we can't since query_id can assign the same id to different
> queryies. For example "select 1" and "select 2" are assigned the same
> query_id 2800308901962295548.
>
> test=# select query,query_id from pg_stat_activity;
>                      query                     |       query_id
> ----------------------------------------------+----------------------
>   select 1;                                    |  2800308901962295548
>   select query,query_id from pg_stat_activity; | -7870935841619459132
>   select 2;                                    |  2800308901962295548
>   
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp