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

[Tatsuo Ishii]

Peng,

For the record, this commit is a fix for this:

http://www.pgpool.net/mantisbt/view.php?id=313

This might be a useful information when you write a release note.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: Yugo Nagata <nagata at sraoss.co.jp>
Subject: [pgpool-committers: 4085] pgpool: Fix a posible hang with streaming replication and extended prot
Date: Fri, 09 Jun 2017 02:01:02 +0000
Message-ID: <E1dJ9EU-0007YR-3g at gothos.postgresql.org>

> 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' "DROP TABLE IF EXISTS test_tbl"
> 'Y'
> 'Q' "CREATE TABLE test_tbl(i int)"
> 'Y'
> 'Q' "INSERT INTO test_tbl VALUES(1)"
> 'Y'
> 
> 'P' "" "BEGIN" 0
> 'B' "" "" 0 0 0
> 'E' "" 0
> 'S'
> 'Y'
> 
> 'P' "" "INSERT INTO test_tbl VALUES(1)" 0
> 'B' "" "" 0 0 0
> 'E' "" 0
> 'S'
> 'Y'
> 
> 'P' "" "COMMIT" 0
> 'B' "" "" 0 0 0
> 'E' "" 0
> 'S'
> 'Y'
> 
> 'P' "S_1" "SELECT * FROM test_tbl" 0
> 'D' 'S' "S_1"
> 'B' "C_1" "S_1" 0 0 0
> 'E' "C_1" 0
> 'S'
> 'Y'
> 
> 'X'
> ---------------------------------------------
> 
> 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.
> 
> Branch
> ------
> master
> 
> Details
> -------
> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=dfdde7387186b6fe7180358c5e2630588f98befa
> 
> Modified Files
> --------------
> src/protocol/pool_proto_modules.c | 3 ++-
> 1 file changed, 2 insertions(+), 1 deletion(-)
>