<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Hi,<br>
<br>
we are using pgpool 3.6.4 with postgresql 9.5.7 with replication and
load balancing. From my
understanding(<a class="moz-txt-link-freetext" href="http://www.pgpool.net/docs/pgpool-II-3.6.4/doc/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-CONDITION">http://www.pgpool.net/docs/pgpool-II-3.6.4/doc/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-CONDITION</a>),
after a write query, every select should be replicated. But it is
not:<br>
<br>
<br>
load balancing works:<br>
<br>
<i>test=# SELECT * FROM test;</i><i><br>
</i><i>LOG: statement: SELECT * FROM test;</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: SELECT *
FROM test;</i><i><br>
</i><i> id | text</i><i><br>
</i><i>----+------</i><i><br>
</i><i>(0 rows)</i><br>
<br>
<br>
repliaction works:<br>
<br>
<i>test=# INSERT INTO test VALUES (0, 'test0');</i><i><br>
</i><i>LOG: statement: INSERT INTO test VALUES (0, 'test0');</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: BEGIN</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: BEGIN</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: INSERT INTO
test VALUES (0, 'test0');</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: INSERT
INTO test VALUES (0, 'test0');</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: COMMIT</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: COMMIT</i><i><br>
</i><i>INSERT 0 1</i><br>
<br>
<br>
<br>
selects in transactions are NEVER replicated:<br>
<i><br>
</i><i>test=# BEGIN;</i><i><br>
</i><i>LOG: statement: BEGIN;</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: BEGIN;</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: BEGIN;</i><i><br>
</i><i>BEGIN</i><i><br>
</i><i>test=# INSERT INTO test VALUES (1, 'test1');</i><i><br>
</i><i>LOG: statement: INSERT INTO test VALUES (1, 'test1');</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: INSERT INTO
test VALUES (1, 'test1');</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: INSERT
INTO test VALUES (1, 'test1');</i><i><br>
</i><i>INSERT 0 1</i><i><br>
</i><i>test=# SELECT * FROM test;</i><i><br>
</i><i>LOG: statement: SELECT * FROM test;</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: SELECT *
FROM test;</i><i><br>
</i><i> id | text</i><i><br>
</i><i>----+-------</i><i><br>
</i><i> 0 | test0</i><i><br>
</i><i> 1 | test1</i><i><br>
</i><i>(2 rows)</i><i><br>
</i><i><br>
</i><i>test=# COMMIT;</i><i><br>
</i><i>LOG: statement: COMMIT;</i><i><br>
</i><i>LOG: DB node id: 11 backend pid: 42087 statement: COMMIT;</i><i><br>
</i><i>LOG: DB node id: 9 backend pid: 59676 statement: COMMIT;</i><i><br>
</i><i>COMMIT</i><br>
<br>
<br>
<br>
Is my understanding of how this should work correct? Can it be an
configuration issue?<br>
<br>
<br>
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.<br>
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.<br>
<br>
<br>
Thanks in advance and kind regards,<br>
Benjamin Firl<br>
<br>
<pre class="moz-signature" cols="72">--
+++++++++++++++++++++++++++++++++++++++++++++++++
+++ Jetzt neu Wissensmanagement für Netzwerke +++
+++ <a class="moz-txt-link-abbreviated" href="http://www.knodge.de">www.knodge.de</a> +++
+++++++++++++++++++++++++++++++++++++++++++++++++
--
<a class="moz-txt-link-abbreviated" href="http://www.wisit.com">www.wisit.com</a>
<a class="moz-txt-link-abbreviated" href="http://www.knodge.de">www.knodge.de</a>
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. </pre>
</body>
</html>