[pgpool-hackers: 3324] COMMIT AND CHAIN

[Tatsuo Ishii]

Hi Pgpool-II developers,

PostgreSQL 12 will introduce a new option for COMMIT command:

AND CHAIN

    If AND CHAIN is specified, a new transaction is immediately
    started with the same transaction characteristics (see SET
    TRANSACTION) as the just finished one. Otherwise, no new
    transaction is started.

So I tested it out with disable_load_balance_on_write because it's one
of the sensible features to transacton commands.

1) set backend_weight1 to 0 so that read qieries are redirected to node 0.

2) start a transaction and issue a write query.

3) issue a read query to make sure that it's sent to node 0.

4) COMMIT AND CHAIN

5) issue a read query to make sure that it's sent node 1.

test=# begin;
BEGIN
test=# insert into t1 values(1);
INSERT 0 1
test=# select 3;
 ?column? 
----------
        3
(1 row)

test=# commit and chain;
COMMIT
test=# begin;
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
BEGIN
test=# select 4;
 ?column? 
----------
        4
(1 row)

2019-05-20 13:21:08: pid 17880: LOG:  DB node id: 0 backend pid: 17898 statement: select 3;
2019-05-20 13:21:35: pid 17880: LOG:  DB node id: 1 backend pid: 17897 statement: select 4;

So it seems in this case Pgpool-II seems to work well with COMMIT AND CHAIN.

If you guys find a problen with COMMIT AND CHAIN, please let me know.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp