[pgpool-hackers: 4289] Re: multi-statement queries

[Tatsuo Ishii]

>>> Hi,
>>> 
>>> Pgpool-II currently cannot fully handle multiple statement
>>> (multi-statement) queries. For example,
>>> 
>>> BEGIN;SELECT;
>>> SAVEPOINT foo;
>>> 
>>> will fail in streaming replication mode because "BEGIN" was sent to
>>> the primar node, but "SAVEPOINT" will be sent to both the primary and
>>> standbys, and standbys will complain "SAVEPOINT can only be used in
>>> transaction blocks".
>>> 
>>> Once I tried to solve the problem by spliting the multi-statements
>>> into multiple single statements but this resulted in a fundamental
>>> issue: any queries in a multi-statement query have to be executed in
>>> the same transaction.
>>> 
>>> https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.3
>>> 
>>> So pgpool should treat a multi-statement as it is. In this direction I
>>> have come up with attached patches.
>>> 
>>> Comments and suggestions are welcome.
>> 
>> Here is the v2 patch for this.
>> 
>>> Note that SELECTs are not load balanced
>>> until the transaction gets committed. Maybe this should be enhanced.
>> 
>> I fixed this in v2 patch. Also add more tests.
> 
> This is the v3 patch. This just splitted v2 patch into two patches:
> 
> Program code (v3-0001-Allow-to-use-multiple-statements-extensively.patch)
> regression test (v3-0002-Test-enhance-regression-001.load_balance.patch)
> 
> Just in case if we want to backpatch the program code patches to
> existing stable releases.  The regression test patch only works in
> master branch since it uses the new feature: notice_per_node_statement.

I have pushed
v3-0001-Allow-to-use-multiple-statements-extensively.patch to master
to 4.2 branches. 4.1 or before looks hard to back-patch.

As of v3-0002-Test-enhance-regression-001.load_balance.patch, it was
only pushed to the master branch.

I am thinking about to implement subset of 001.load_balance test to
4.4 to 4.2.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp