[pgpool-general: 198] Re: PgPool II in load_balance_mode break transaction boundary?

[Pius Chan]

Hi,

I have set up two 9.1.2 database nodes  with streaming replication between them. Then I set up pgPool II 3.1.1 in another machine. The pgPool II is configured to run with master_slave_mode and load_balance_mode ON:

#------------------------------------------------------------------------------
# CONNECTION POOLING 
#------------------------------------------------------------------------------
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off 
replicate_select = off 
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off 
failover_if_affected_tuples_mismatch = off 

# - Degenerate handling -

replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on 
ignore_leading_white_space = on 
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval,add_login_audit'

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on 
master_slave_sub_mode = 'stream'
# - Streaming -

sr_check_period = 10
sr_check_user = 'replicator'
sr_check_password = 'contigo'
# delay_threshold is in bytes, set here to ~ 10MB
delay_threshold = 1000000 
log_standby_delay = 'if_over_threshold'


I want to verify that explicit transaction should be routed to MASTER database only. So I use psql on the machine running pgPool II to connect to my test database MONSOON_91X:

psql -U postgres -h 127.0.0.1 MONSOON_91X
psql (9.1.2)
Type "help" for help.

MONSOON_91X=# show pool_nodes;
 node_id |   hostname    | port | status | lb_weight 
---------+---------------+------+--------+-----------
 0       | 192.168.6.152 | 5432 | 2      | 0.500000
 1       | 192.168.6.153 | 5432 | 2      | 0.500000


Then I issue the several sequence of SQLs.

(1) commands issued:
BEGIN;
SELECT * FROM PIUS;
UPDATE PIUS SET NAME='ABC' WHERE ID=2;
SELECT * FROM PIUS

(2) commands issued;
BEGIN;
SELECT * FROM PIUS;
SELECT NEXTVAL('CUSTOMER_SEQ');
SELECT * FROM PIUS;

In both case, most of the time, even with an explicit BEGIN:,  the first SELECT can be routed to the SLAVE (i.e. hot standby). In test 1, the UPDATE SQL is routed to the MASTER and the SELECT after it is also routed to the MASTER.

In test 2, the SELECT NEXTVAL is routed to the master BUT the SELECT after it is routed to the SLAVE.

I wonder if within a transaction, this behavior may break the transaction boundary or integrity if SLAVE lags behind MASTER significantly due to network error. For example, if I issue two transactions:

(1) Transaction 1
begin;
insert into order_detail for order_id = 1, order_line = 1, amt=10.00;
insert into order_detail for order_id = 1, order_line = 2, amt=9.00;
commit;

(2) Immediately starts another transaction:
begin;
select sum(amt) from order_detail where order_id=1;
insert into order_summary for order_id = 1
commit;

If the above two transactions are very close to each other and SLAVE lags behind MASTER significantly, then the first select in transaction 2 may get nothing.  Any advice on this?

Thanks a lot,

Pius