<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Hi Tatsuo,<div><br></div><div>I just downloaded the 4.5RC1 version to test it to check if clients disconnections are solved.</div><div>The problem is that I cannot compile this version in RHEL5. We had no problems to compile version 4.3.2.</div><div>Is the new version compatible with RHEL5 operating system?</div><div><br></div><div>Please find attached info from my OS:</div><div><br></div><div>Red Hat Enterprise Linux Client release 5.8 (Tikanga)</div><div>gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52)<br></div><div>ldd (GNU libc) 2.5</div><div>Postgres 12.5</div><div><br></div><div>The compilation error is:</div><div><br></div><div><div>gcc -std=gnu99 -DHAVE_CONFIG_H -I. -I../../src/include -D_GNU_SOURCE -I ../../src/include/parser -I /opt/postgresql/include -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fno-strict-aliasing -c -o copyfuncs.o copyfuncs.c</div><div>In file included from ../../src/include/parser/parsenodes.h:28,</div><div> from copyfuncs.c:30:</div><div>../../src/include/parser/primnodes.h:27: error: redefinition of type ‘TransactionId’</div><div>../../src/include/parser/pg_list.h:50: error: <span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit;color:rgb(12,13,14)">previous declaration of ‘</span>TransactionId<span style="background-color:transparent;font-family:inherit;font-style:inherit;font-variant-ligatures:inherit;font-variant-caps:inherit;font-weight:inherit;white-space:inherit;color:rgb(12,13,14)">’ was here</span><br></div></div><div><br></div><div>Thank you for your support.</div><div><br></div><div>Best,</div><div>Jesús</div><div><br></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">El lun, 15 may 2023 a las 4:14, Tatsuo Ishii (<<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>>) escribió:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> Ok, thank you for your great work.<br>
> <br>
> In this case the failover is due to the slave node.<br>
> Taking into account I have no load balancing, I think clients should can<br>
> connect to pgpool during this failover because master node is still the<br>
> same and it is alive.<br>
<br>
There are some code paths to access the standby node, which triggers<br>
session disconnection:<br>
<br>
1. When client connects to pgpool, pgpool tries to connect to standby<br>
PostgreSQL.<br>
<br>
2. When client connects to pgpool, pgpool sends SET application_name<br>
command.<br>
<br>
3. Pgpool detects the PostgreSQL shutdown event even if it's from<br>
standby, which results in session disconnection.<br>
<br>
Although I am not sure if I can eliminate all the code paths above, I<br>
will try for upcoming Pgpool 4.5.<br>
<br>
> Is there a plan to solve this limitation in future releases?<br>
> <br>
> Thanks in advance.<br>
> <br>
> Best,<br>
> Jesús<br>
> <br>
> El dom, 14 may 2023 4:06, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> escribió:<br>
> <br>
>> 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">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<br>
>> 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">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<br>
>> 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<br>
>> patch<br>
>> >> the<br>
>> >> >> > database is always available. Without your patch the database is<br>
>> 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<br>
>> 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">ishii@sraoss.co.jp</a>><br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> the<br>
>> >> >> >> function which has the logic inside can be called frequently<br>
>> 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">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<br>
>> 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<br>
>> 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" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> >> >> >> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >> >> >><br>
>> >> >> >><br>
>> >> >><br>
>> >><br>
>><br>
</blockquote></div>