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

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: v2-0001-Allow-to-use-multiple-statements-extensively.patch
Type: text/x-patch
Size: 30637 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230212/ad4b5ad2/attachment-0001.bin>