<div dir="auto">I forget to indicate the pgpool version: 4.3.2</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">El jue, 6 oct 2022 9:25, Jesús Campoy <<a href="mailto:jesuscampoy@gmail.com">jesuscampoy@gmail.com</a>> escribió:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif">Hi,</p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"> </p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">I’m writing
this email because I have problems with load balance. It would be great if
someone could help me with this issue.</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">My
environment is:</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<ul style="margin-top:0cm;margin-bottom:0cm" type="disc">
<li style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">OS: RHEL 5.7</span></li>
<li style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Postgres 11</span></li>
<li style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">streaming replication as
redundancy mode</span></li>
<li style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">In one server runs pgpool and
the primary database (node 0), in another server runs the standby database
(node 1).</span></li>
<li style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Read Commited as default
transaction isolation configured in Postgres</span></li>
</ul>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Redundancy
and load balancing is working in most of the cases as expected and replication
delay is 0.</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"></span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">However,
some transactions are not working and pgpool shows the following log:</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.544:
<span style="font-size:14.6667px">test_app </span> pid 441:
LOG: DB node id: 0 backend pid: 525
statement: BEGIN;DELETE FROM scenario WHERE Id =-1 and Role = 3</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.546:
<span style="font-size:14.6667px">test_app </span> pid 441:
LOG: statement: SAVEPOINT
_EXEC_SVP_0xa3ff298</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.546:
<span style="font-size:14.6667px">test_app </span> pid 441:
LOG: DB node id: 0 backend pid: 525
statement: SAVEPOINT _EXEC_SVP_0xa3ff298</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.546:
<span style="font-size:14.6667px">test_app </span> pid 441:
LOG: DB node id: 1 backend pid: 15985
statement: SAVEPOINT _EXEC_SVP_0xa3ff298</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.547: </span>
<span style="font-size:14.6667px">test_app </span> <span style="font-size:11pt">pid 441:
LOG:</span><span style="font-size:11pt"> </span><span style="font-size:11pt">pool_send_and_wait: Error or notice
message from backend: : DB node id: 1 backend pid: 15985 statement:
"SAVEPOINT _EXEC_SVP_0xa3ff298" message: "SAVEPOINT can only be
used in transaction blocks"</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.547:
<span style="font-size:14.6667px">test_app </span> pid 441:
WARNING: packet kind of backend 1 ['E']
does not match with main/majority nodes packet kind ['C']</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.547:
<span style="font-size:14.6667px">test_app </span> pid 441:
FATAL: failed to read kind from backend</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.547:
<span style="font-size:14.6667px">test_app </span> pid 441:
DETAIL: kind mismatch among backends.
Possible last query was: "SAVEPOINT _EXEC_SVP_0xa3ff298" kind details
are: 0[C] 1[E: SAVEPOINT can only be used in transaction blocks]</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.547:
<span style="font-size:14.6667px">test_app </span> pid 441:
HINT: check data consistency among db
nodes</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt 35.4pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">2022-09-29 10:59:05.548: main pid 379:
LOG: child process with pid: 441 exits
with status 512</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">If I
disable the load balance mode, this transaction always works.</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">I also
tried without success changing the following parameters:</span></p><p style="margin:0cm 0cm 0.0001pt 72pt;text-align:justify;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier New"">o<span style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:7pt;line-height:normal;font-family:"Times New Roman"">
</span></span>disable_load_balance_on_write</p><p style="margin:0cm 0cm 0.0001pt 72pt;text-align:justify;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier New"">o<span style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:7pt;line-height:normal;font-family:"Times New Roman"">
</span></span>app_name_redirect_preference_list</p><p style="margin:0cm 0cm 0.0001pt 72pt;text-align:justify;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier New"">o<span style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:7pt;line-height:normal;font-family:"Times New Roman"">
</span></span>black_query_pattern_list</p><p style="margin:0cm 0cm 0.0001pt 72pt;text-align:justify;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier New"">o<span style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:7pt;line-height:normal;font-family:"Times New Roman"">
</span></span>primary_routing_query_pattern_list</p><p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">
</span></p><p style="margin:0cm 0cm 0.0001pt 72pt;text-align:justify;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-family:"Courier New"">o<span style="font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:7pt;line-height:normal;font-family:"Times New Roman"">
</span></span>database_redirect_preference_list</p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Thanks in
advance.</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US"> </span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Best,</span></p>
<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US">Jesús</span></p></div>
</blockquote></div>