[pgpool-committers: 1294] pgpool: Fix data inconsistency problem with native replication mode + e

Tatsuo Ishii ishii at postgresql.org
Tue Oct 15 11:16:41 JST 2013


Fix data inconsistency problem with native replication mode + extended protocol case.

It is reported that concurrent INSERT using JDBC driver causes data
difference among database node. This only happens following conditions
are all met:

1) Native replication mode
2) Extended protocol used
3) The portal created by parse message is reused by bind message
4) autocommit is on
5) SERIAL (sequence) is used

Pgpool-II's parse message function knows it has to lock the target
table when INSERT (plus #5) is issued by clients. Unfortunately bind
message function did not know it. Once parse/bind/execute finishes,
pgpool releases the lock obtained by parse because of #4. JDBC wants
to reuse the portal and starts the cycle from bind message, which does
not obtain lock. As as result, lock-free INSERT are floating around
which causes data inconsistency of course.  The solution is, lock the
table in bind phase.

For this bind needs to issue LOCK in extended protocol. This was a
little bit hard because the module (do_command()) to issue internal
SQL command (other than SELECT) does not support extended protocol. To
solve the problem do_query() is modified so that it accepts other than
SELECT because it already accepts extended protocol. The modification
is minimum and is only tested for the case called from insert_lock(). I
do not recommend to replace every occurrence of do_command() with
do_query() at this point.

BTW the reason why the bug is not reported is, most users uses JDBC
with auto commit = off. In this case, the lock obtained by parse
persists until user explicitly issues commit or rollback.

Per bug report by Steve Kuekes in [pgpool-general: 2142].

Branch
------
master

Details
-------
http://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=35e28aa17bb9460b01859e9c413815f7b9ce9f1b

Modified Files
--------------
src/protocol/pool_process_query.c                  |   79 +++++++++++++++-----
src/protocol/pool_proto_modules.c                  |   25 +++++++
.../tests/002.native_replication/test.sh           |   15 ++++
.../tests/002.native_replication/PgTester.java     |   47 ++++++++++++
.../tests/002.native_replication/create.sql        |    6 ++
5 files changed, 155 insertions(+), 17 deletions(-)



More information about the pgpool-committers mailing list