[pgpool-general: 9017] Re: autosave=always jdbc option & it only sends query SAVEPOINT PGJDBC_AUTOSAVE and hangs

[Tatsuo Ishii]

> 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