<div dir="auto">Ok, thank you for your great work.<div dir="auto"><br></div><div dir="auto">In this case the failover is due to the slave node.</div><div dir="auto">Taking into account I have no load balancing, I think clients should can connect to pgpool during this failover because master node is still the same and it is alive.</div><div dir="auto"><div dir="auto"><br></div><div dir="auto">Is there a plan to solve this limitation in future releases?</div><div dir="auto"><br></div><div dir="auto">Thanks in advance.</div><div dir="auto"><br></div><div dir="auto">Best,</div><div dir="auto">Jesús </div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">El dom, 14 may 2023 4:06, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> escribió:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Jesús,<br>
<br>
> Hi Tatsuo. Thank you very much.<br>
> Should we add It as a bug in Pgpool-II Bug Tracker?<br>
<br>
No, you don't need to as it's a known limitation that pgpool does not<br>
accept new connection while failover.<br>
<br>
> El sáb, 13 may 2023 9:24, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank" rel="noreferrer">ishii@sraoss.co.jp</a>> escribió:<br>
> <br>
>> Ok. The errors were generated while clients tried to connect to<br>
>> pgpool. My patch covers the case when failover happens while<br>
>> connections from clients to pgpool are *kept*. However the patch does<br>
>> not cover the case when clients try to establish connection to pgpool<br>
>> while failover.<br>
>><br>
>> I tested my patch using pgbench. If pgbench is given "-C" (create<br>
>> connection for each transaction), I get same errors you mentioned.<br>
>><br>
>> I have to admit my patch does not cover all the cases. I need more<br>
>> time to deal with these problems.<br>
>><br>
>> > Hi,<br>
>> ><br>
>> > I think we cannot connect to pgpool. I will show you the output of my<br>
>> > dbCheck script.<br>
>> ><br>
>> ><br>
>> > - *Pgpool without patch and backend1 as slave**:*<br>
>> ><br>
>> > [root@pg_client1 services]# ./dbcheck.sh $VIP_PGPOOL<br>
>> ><br>
>> > psql: ERROR: do command failed<br>
>> ><br>
>> > DETAIL: backend error: "SFATAL"<br>
>> ><br>
>> > psql: ERROR: unable to read data from DB node 1<br>
>> ><br>
>> > DETAIL: socket read failed with error "Connection reset by peer"<br>
>> ><br>
>> > psql: server closed the connection unexpectedly<br>
>> ><br>
>> > This probably means the server terminated abnormally<br>
>> ><br>
>> > before or while processing the request.<br>
>> ><br>
>> > psql: server closed the connection unexpectedly<br>
>> ><br>
>> > This probably means the server terminated abnormally<br>
>> ><br>
>> > before or while processing the request.<br>
>> ><br>
>> > This probably means the server terminated abnormally<br>
>> ><br>
>> > before or while processing the request.<br>
>> ><br>
>> ><br>
>> ><br>
>> > - *Pgpool with patch and backend1 as slave:*<br>
>> ><br>
>> > psql: ERROR: unable to read message kind<br>
>> ><br>
>> > DETAIL: kind does not match between main(52)<br>
>> ><br>
>> ><br>
>> ><br>
>> > - *Pgpool with patch and backend1 as master**:*<br>
>> ><br>
>> > psql: ERROR: unable to read data from DB node<br>
>> ><br>
>> > DETAIL: socket read failed with error "Connection reset by peer"<br>
>> ><br>
>> > server closed the connection unexpectedly<br>
>> ><br>
>> > This probably means the server terminated abnormally<br>
>> ><br>
>> > before or while processing the request.<br>
>> ><br>
>> > connection to server was lost<br>
>> ><br>
>> > server closed the connection unexpectedly<br>
>> ><br>
>> > This probably means the server terminated abnormally<br>
>> ><br>
>> > before or while processing the request.<br>
>> ><br>
>> > connection to server was lost<br>
>> ><br>
>> ><br>
>> > Anyway, with a client which uses ODBC, if it tries to access the database<br>
>> > during failover (from slave node) the following error is displayed:<br>
>> "Driver<br>
>> > Unable to Establish Connection with Data Source".<br>
>> ><br>
>> > El vie, 12 may 2023 a las 9:40, Tatsuo Ishii (<<a href="mailto:ishii@sraoss.co.jp" target="_blank" rel="noreferrer">ishii@sraoss.co.jp</a>>)<br>
>> > escribió:<br>
>> ><br>
>> >> What do you mean by "database is not available"?<br>
>> >><br>
>> >> 1. You can connect to pgpool but pgpool does not reply back.<br>
>> >><br>
>> >> 2. You can cannect to pgpool but pgpool immediately disconnects.<br>
>> >><br>
>> >> > Hi Tatsuo,<br>
>> >> ><br>
>> >> > I'm working with your patch but I continue facing a problem because<br>
>> the<br>
>> >> > database is not available during 1 second aprox (I have a script<br>
>> calling<br>
>> >> > select query every 0.1 seconds to check the time is not available the<br>
>> >> > database).<br>
>> >> ><br>
>> >> > I will explain two different cases:<br>
>> >> ><br>
>> >> > 1. Slave node (backend1 in pgpool.conf) is turn off. With your patch<br>
>> the<br>
>> >> > database is always available. Without your patch the database is not<br>
>> >> > available during 1 second.<br>
>> >> > 2. Master node (backend0) is turn off. Failover is done to promote<br>
>> >> > backend1. After that, I turn on again backend0, which is now slave<br>
>> node.<br>
>> >> If<br>
>> >> > I turn off this slave node (backend0), the database is not available<br>
>> >> during<br>
>> >> > 1 second (with or without your patch)<br>
>> >> ><br>
>> >> > Do you have any idea why is this behaviour?<br>
>> >> ><br>
>> >> > Thanks in advance.<br>
>> >> ><br>
>> >> > Best,<br>
>> >> > Jesús<br>
>> >> ><br>
>> >> > El vie, 14 abr 2023 3:41, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank" rel="noreferrer">ishii@sraoss.co.jp</a>> escribió:<br>
>> >> ><br>
>> >> >> Hi Jesús,<br>
>> >> >><br>
>> >> >> > Hi Tatsuo,<br>
>> >> >> ><br>
>> >> >> > At first, thank you so much for your time to investigate this<br>
>> issue.<br>
>> >> >><br>
>> >> >> No problem.<br>
>> >> >><br>
>> >> >> > I have compiled pgpool 4.3.2 with your patch and the problem with<br>
>> >> pgbench<br>
>> >> >> > is solved.<br>
>> >> >> > I still need to test it in my environment.<br>
>> >> >> ><br>
>> >> >> > Anyway, I had a look your code and I have seen that the session is<br>
>> >> closed<br>
>> >> >> > only if failover is not completed in 30 seconds.<br>
>> >> >> > I have the following doubt related to this change. Is this session<br>
>> >> >> > operative during the failover? I mean, if failover spends 20<br>
>> seconds,<br>
>> >> is<br>
>> >> >> > this session blocked during this time or this session can accept<br>
>> any<br>
>> >> >> > transaction?<br>
>> >> >><br>
>> >> >> It is likely the session is blocked. The reason for "likely" is the<br>
>> >> >> function which has the logic inside can be called frequently during<br>
>> >> >> session but it is not always. It is possible that a pgpool process<br>
>> >> >> already called the function by the time when failover starts, then<br>
>> >> >> proceeds and sends a query to backend.<br>
>> >> >><br>
>> >> >> > Let me another question. Should we add this issue as a bug?<br>
>> >> >><br>
>> >> >> No you don't need. Developers already recognize this a bug report.<br>
>> >> >><br>
>> >> >> > Thanks in advance.<br>
>> >> >> ><br>
>> >> >> > Best,<br>
>> >> >> > Jesús<br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> > El mié, 12 abr 2023 3:33, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank" rel="noreferrer">ishii@sraoss.co.jp</a>><br>
>> escribió:<br>
>> >> >> ><br>
>> >> >> >> > However a downside of this is, while failover clients cannot<br>
>> >> process<br>
>> >> >> >> > queries or at least slow down processing. Below is the log from<br>
>> >> >> >> > pgbench using "-P 1" option to show progress. As you can see<br>
>> from<br>
>> >> 170<br>
>> >> >> >> > s pgbench starts to slow down and recovers at 194 s. That is,<br>
>> the<br>
>> >> >> >> > slowdown continued for 24 seconds.<br>
>> >> >> >> ><br>
>> >> >> >><br>
>> >> >> >> After more research, I suspect the slow down is due to effect of<br>
>> >> >> >> checkpointing. If I add "-S" option to change the transaction<br>
>> time, I<br>
>> >> >> >> don't see the slow down anymore.<br>
>> >> >> >><br>
>> >> >> >> Best reagards,<br>
>> >> >> >> --<br>
>> >> >> >> Tatsuo Ishii<br>
>> >> >> >> SRA OSS LLC<br>
>> >> >> >> English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> >> >> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >> >><br>
>> >> >><br>
>> >><br>
>><br>
</blockquote></div>