<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.StylE-mailovZprvy17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><a name="_Hlk30747702"><span lang="EN-US">Hello,<o:p></o:p></span></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I have encountered an unexpected behavior of a DML (or DELETE to be exact) being issued from pgAdmin (v4.28) application accessing the database via pgPool configured with session
level load balancing and native replication. I am running 2 servers with the pgPool-II service (version 4.1.3) with the Watchdog component and 2 PostgreSQL servers (version 11.9).<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">When I create a new “Query Tool” window in pgAdmin, which creates a new session to pgPool/PostgreSQL that has one of the DB nodes set as load-balance node (doesn’t matter which,
it behaves the same on both), and write a SELECT command and right after it a DELETE command (or I assume any other DML) like so:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">SELECT * FROM <table> WHERE <primary_key> = <value>;<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">DELETE FROM <table> WHERE <primary_key> = <value>;<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">And then I let it run as a whole, instead of the DELETE command being replicated to both DB nodes, it is performed only on the load-balance node (the one that receives the SELECT
command). This causes data inconsistency as the record gets deleted only on that node, while on the other it is still present. If I issue these commands separately, it behaves as expected – SELECT gets load balanced to the chosen DB node and DELETE gets replicated
to both nodes.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I have also tried to turn off the load balancing (load_balance_mode = off), but that didn’t solve the issue – the DELETE was automatically sent to the DB node, which was currently
flagged as a “MASTER” node, but was not replicated to the other node. What helped, was setting replicate_select = on, which caused all the commands to be performed on both nodes, regardless of the type of the command.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I have also tried to replicate this behavior with PSQL command line, where I have put the SELECT and the DELETE commands in one line, but both commands were issued correctly (SELECT
on the load-balance node and DELETE on both), so it seems to be a pgAdmin specific issue. Now I understand that the pgAdmin is a 3<sup>rd</sup> party tool here, but I think it’s being used widely enough to warrant some further investigation – whether there
is some issue in pgPool code/behavior that causes it to treat the DELETE command as a part of the SELECT command (it is considered as a non-write command…) or if it is some “weird” way the pgAdmin parses those commands and sends them to pgPool.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I am sending my pgpool.conf file as an attachment for further information, if you need me to send or test anything else, let me know.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Thank you for your time.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">With best regards,<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Vladimír Láznička <o:p></o:p></span></span></p>
</div>
</body>
</html>