[pgpool-hackers: 4288] 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.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v3-0001-Allow-to-use-multiple-statements-extensively.patch
Type: text/x-patch
Size: 11980 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230212/f19ab194/attachment-0002.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v3-0002-Test-enhance-regression-001.load_balance.patch
Type: text/x-patch
Size: 18828 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230212/f19ab194/attachment-0003.bin>