[pgpool-general: 9017] Re: autosave=always jdbc option & it only sends query SAVEPOINT PGJDBC_AUTOSAVE and hangs
Tatsuo Ishii
ishii at sraoss.co.jp
Sat Feb 10 16:20:36 JST 2024
> Hi,
>
>> Hi,
>>
>> version: pgpool-II-4.4.2-1.rhel8.x86_64
>>
>> log file attached, there is pid 3996197 that is affected by the
>> autosave issue, in database are connections reported as idle in
>> transaction and waiting on client. This testing environment has just
>> primary database configured.
>
> I have taken a look at the log. I tried to reproduce the problem using
> pgproto (a protocol level speaking test tool coming with Pgpool-II)
> and succeeded. The essential condition seems:
>
> 1) set backend_weight for the standby node 0 so that everything goes
> to primary PostgreSQL.
>
> 2) after sequence of extended queries, send simple query like
> "SAVEPOINT PGJDBC_AUTOSAVE" (I think this is generated by the JDBC
> driver).
>
> Here is the minimal data set for pgproto to reproduce the problem:
>
> 'P' "" "BEGIN" 0
> 'B' "" "" 0 0 0
> 'E' "" 0
> 'Q' "SAVEPOINT PGJDBC_AUTOSAVE"
> 'Y'
> 'P' "" "SELECT 1" 0
> 'X'
>
> This represents following command sequence:
>
> Parse "BEGIN"
> Bind to an unamed portal
> Execute the portal
> Send simple query "SAVEPOINT PGJDBC_AUTOSAVE"
> Wait for response from server
> Parse "SELECT 1"
> Terminate the session
>
> I think the key to reproduce the problem is, sending a simple query
> before sending "Sync" message (which indicates the end of an extended
> protocol messages). I am not sure this is violation against the
> PostgreSQL protocol, but it seems PostgreSQL accepts the sequence.
>
> Response from pgproto against pgpool:
>
> FE=> Parse(stmt="", query="BEGIN")
> FE=> Bind(stmt="", portal="")
> FE=> Execute(portal="")
> FE=> Query (query="SAVEPOINT PGJDBC_AUTOSAVE")
> --> hung here
>
> Response from pgproto against PostgreSQL:
>
> FE=> Parse(stmt="", query="BEGIN")
> FE=> Bind(stmt="", portal="")
> FE=> Execute(portal="")
> FE=> Query (query="SAVEPOINT PGJDBC_AUTOSAVE")
> <= BE ParseComplete
> <= BE BindComplete
> <= BE CommandComplete(BEGIN)
> <= BE CommandComplete(SAVEPOINT)
> <= BE ReadyForQuery(T)
> FE=> Parse(stmt="", query="SELECT 1")
> FE=> Terminate
> --> no hung
>
>> Our development team has decided to get rid of autosave for future
>> releases, but I still have the current version, so I can test your
>> recommendations if you can provide any.
>
> I will work on this issue.
Status report on this. In summary I think there's an issue with JDBC
driver. I believe JDBC driver should send SAVEPOINT after ending
extended query protocol message by sending a Sync message, but the
drive does not do that (alternatively the drive could send SAVEPOINT
using the extended query protocol).
I have opened a discussion on this in the PostgreSQL developer's
mailing list.
https://www.postgresql.org/message-id/20240130.082257.800729082723903188.t-ishii%40sranhm.sra.co.jp
Also I have contacted to a JDBC driver developer and he requested me
to create a GitHub issue on this. I did so.
https://github.com/pgjdbc/pgjdbc/issues/3107
I am waiting for a response from the JDBC driver community.
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list