[pgpool-general: 9007] 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.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp