[pgpool-hackers: 2017] Re: [pgpool-committers: 3788] pgpool: Fix yet another kind mismatch error in streaming replication mo

[Tatsuo Ishii]

For a record, here is the test data for Pgproto
(https://github.com/tatsuo-ishii/pgproto).
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

------------------------------------------------------------------------
# Test data for a bug which was there since Pgpool-II 3.5.0 to 3.6.1.

# 1) Parse "BEGIN" using statement S1, and it is sent to both node 0 and 1.
# 2) Close S1.
# 3) Parse SELECT using S1, and it is sent to node 0 (or 1).
# 4) Bind retrieves info (sent_messages) regarding S1. Since Pgpool-II
#   only removes info on S1 when CloseComplete received, Bind decides
#   to send bind message to both node 0 & 1 because it was the info
#   regarding BEGIN. Node 0 or 1 tries to bind to non existent
#   statement S1.
#
#   As a result, something like "failed to read kind from backend.
#   kind mismatch among backends. Possible last query was: "BEGIN" kind
#   details are: 0[E: prepared statement "S1" does not exist] 1[3]
#   check data consistency among db nodes" occurs.
#
# Note that in 3) if other than S1 is used, the problem does not occur.
# Solution is, removing S1 when Close message is received. This problem
# has been there since 3.5.0 was out.
# 
# FE=> Sync
# <= BE ParseComplete
# <= BE BindComplete
# <= BE CommandComplete(BEGIN)
# <= BE NoticeResponse(S WARNING C XX000 M packet kind of backend 1
# ['3'] does not match with master/majority nodes packet kind ['E'] F
# pool_process_query.c L 3603 ) <= BE ErrorResponse(S FATAL C XX000 M
# failed to read kind from backend D kind mismatch among
# backends. Possible last query was: "BEGIN" kind details are: 0[E:
# prepared statement "S1" does not exist] 1[3] H check data consistency
# among db nodes F pool_process_query.c L 3708 )

# Also this is in the pgpool log:
# 2017-01-27 08:11:38: pid 7504: LOG:  pool process query
# 2017-01-27 08:11:38: pid 7504: DETAIL:  discard 3 packet from backend 0

# Start a transaction
'P'	"S1"	"BEGIN"	0
'B'	""	"S1"	0	0	0
'E'	""	0
'C'	'S'	"S1"

# Issue SELECT
'P'	"S1"	"SELECT 1"	0
'B'	""	"S1"	0	0	0
'E'	""	0
'C'	'S'	"S1"

# Issue COMMIT
'P'	"S1"	"COMMIT"	0
'B'	""	"S1"	0	0	0
'E'	""	0
'C'	'S'	"S1"

'S'
'Y'
'X'
------------------------------------------------------------------------

From: Tatsuo Ishii <ishii at postgresql.org>
Subject: [pgpool-committers: 3788] pgpool: Fix yet another kind mismatch error in streaming replication mo
Date: Mon, 30 Jan 2017 06:43:53 +0000
Message-ID: <E1cY5gv-0002GQ-HO at gothos.postgresql.org>

> Fix yet another kind mismatch error in streaming replication mode.
> 
> 1) Parse "BEGIN" using statement S1, and it is sent to both node 0 and 1.
> 
> 2) Close S1.
> 
> 3) Parse SELECT using S1, and it is sent to node 0 (or 1).
> 
> 4) Bind retrieves info (sent_messages) regarding S1. Since Pgpool-II
>    only removes info on S1 when CloseComplete received, Bind decides
>    to send bind message to both node 0 & 1 because it was the info
>    regarding BEGIN. Node 0 or 1 tries to bind to non existent
>    statement S1.
> 
>    As a result, something like "failed to read kind from backend.
>    kind mismatch among backends. Possible last query was: "BEGIN" kind
>    details are: 0[E: prepared statement "S1" does not exist] 1[3]
>    check data consistency among db nodes" occurs.
> 
> Note that in 3) if other than S1 is used, the problem does not occur.
> Solution is, removing S1 when Close message is received. This problem
> has been there since 3.5.0 was out.
> 
> Branch
> ------
> master
> 
> Details
> -------
> http://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=6c2d73da42689eaba2f192052896a0229c9bbaef
> 
> Modified Files
> --------------
> src/protocol/pool_proto_modules.c | 8 ++++++++
> 1 file changed, 8 insertions(+)
>