[pgpool-general: 5688] Load balancing in transactions not behaving like expected

[Benjamin Firl]

Hi,

we are using pgpool 3.6.4 with postgresql 9.5.7 with replication and
load balancing. From my
understanding(http://www.pgpool.net/docs/pgpool-II-3.6.4/doc/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-CONDITION),
after a write query, every select should be replicated. But it is not:


load balancing works:

/test=# SELECT * FROM test;//
//LOG:  statement: SELECT * FROM test;//
//LOG:  DB node id: 11 backend pid: 42087 statement: SELECT * FROM test;//
// id | text//
//----+------//
//(0 rows)/


repliaction works:

/test=# INSERT INTO test VALUES (0, 'test0');//
//LOG:  statement: INSERT INTO test VALUES (0, 'test0');//
//LOG:  DB node id: 9 backend pid: 59676 statement: BEGIN//
//LOG:  DB node id: 11 backend pid: 42087 statement: BEGIN//
//LOG:  DB node id: 9 backend pid: 59676 statement: INSERT INTO test
VALUES (0, 'test0');//
//LOG:  DB node id: 11 backend pid: 42087 statement: INSERT INTO test
VALUES (0, 'test0');//
//LOG:  DB node id: 11 backend pid: 42087 statement: COMMIT//
//LOG:  DB node id: 9 backend pid: 59676 statement: COMMIT//
//INSERT 0 1/



selects in transactions are NEVER replicated:
/
//test=# BEGIN;//
//LOG:  statement: BEGIN;//
//LOG:  DB node id: 9 backend pid: 59676 statement: BEGIN;//
//LOG:  DB node id: 11 backend pid: 42087 statement: BEGIN;//
//BEGIN//
//test=# INSERT INTO test VALUES (1, 'test1');//
//LOG:  statement: INSERT INTO test VALUES (1, 'test1');//
//LOG:  DB node id: 9 backend pid: 59676 statement: INSERT INTO test
VALUES (1, 'test1');//
//LOG:  DB node id: 11 backend pid: 42087 statement: INSERT INTO test
VALUES (1, 'test1');//
//INSERT 0 1//
//test=# SELECT * FROM test;//
//LOG:  statement: SELECT * FROM test;//
//LOG:  DB node id: 9 backend pid: 59676 statement: SELECT * FROM test;//
// id | text//
//----+-------//
//  0 | test0//
//  1 | test1//
//(2 rows)//
//
//test=# COMMIT;//
//LOG:  statement: COMMIT;//
//LOG:  DB node id: 11 backend pid: 42087 statement: COMMIT;//
//LOG:  DB node id: 9 backend pid: 59676 statement: COMMIT;//
//COMMIT/



Is my understanding of how this should work correct? Can it be an
configuration issue?


On our production system we can see queries hanging forever in an active
state and holding locks. They even outlive the postgresql timeouts
(statement_timeout, lock_timeout) and block all future requests. I am
able to reproduce this, but not yet with a small example.
Forcing replication with /*REPLICATION*/, whenever using a select in a
transaction, fixed this. So from my understanding, forcing replication
in this case is what pgpool should do.


Thanks in advance and kind regards,
Benjamin Firl

-- 
+++++++++++++++++++++++++++++++++++++++++++++++++

+++ Jetzt neu Wissensmanagement für Netzwerke +++

+++             www.knodge.de                 +++

+++++++++++++++++++++++++++++++++++++++++++++++++

 

--

www.wisit.com

www.knodge.de

 

 

wisit media GmbH

Ehrenbergstr. 11

D-98693 Ilmenau

---------------------------------------------------------------------------

-

wisit media GmbH, Ehrenbergstr. 11, D-98693 Ilmenau

Registergericht Jena HRB 512472

Geschaeftsfuehrung: Dipl. Ing. Markus Duelli

 

 

Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich

erhalten haben, informieren Sie bitte sofort den Absender und vernichten

Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe

dieser E-Mail ist nicht gestattet. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170807/10b24b79/attachment.html>