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

[Tatsuo Ishii]

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
------
V3_3_STABLE

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

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