[pgpool-committers: 4085] pgpool: Fix a posible hang with streaming replication and extended prot

Yugo Nagata nagata at sraoss.co.jp
Fri Jun 9 11:01:02 JST 2017

Fix a posible hang with streaming replication and extended protocol

This hang occured under a certain condition. The following is an example.

- pgpool.conf is configured so that all read queries are sent to the standby.
- First, issue a writing query in a transaction block
- After commiting the transaction, issue a select query.
- When processing the query, send Describe (statement) message just after Parse.

Without using JDBC, we can reproduce the problem by pgproto with the followeing messages.

'Q' "CREATE TABLE test_tbl(i int)"
'Q' "INSERT INTO test_tbl VALUES(1)"

'P' "" "BEGIN" 0
'B' "" "" 0 0 0
'E' "" 0

'P' "" "INSERT INTO test_tbl VALUES(1)" 0
'B' "" "" 0 0 0
'E' "" 0

'P' "" "COMMIT" 0
'B' "" "" 0 0 0
'E' "" 0

'P' "S_1" "SELECT * FROM test_tbl" 0
'D' 'S' "S_1"
'B' "C_1" "S_1" 0 0 0
'E' "C_1" 0


In practice, such Describe message can be sent by JDBC when executing PreparedStatement
with a parameter of timestamp type more than prepare_threshold times. In addition,
queries can be issued outside of a transaction block when autocommit is enabled.

When a writing query is issued in a transaction block, writing_transaction flag
is set, but after commiting the transaction this flag is not unset. The problem
is that parse_before_bind() is always called when writing_transaction is set.

Alhough Parse of the select and Describe following are sent to standby
due to load balancing, another Parse and Bind, Execute are to be sent to
primary due to parse_before_bind.

In this situation, Pgpool-II waits for the responces of Describe from
standby forever because any messages after Describe is not sent to the
standby and any responce is not returned.

To fix it, parse_before_bind() should be called only if we are in a transaction
block so that we can send Bind and Execute to the right backend.



Modified Files
src/protocol/pool_proto_modules.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)

More information about the pgpool-committers mailing list