<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div id="yui_3_16_0_1_1429799079393_7070" dir="ltr">My pg-pool setup is as follows -</div><div id="yui_3_16_0_1_1429799079393_6736" dir="ltr"><br></div><div dir="ltr">I use Pgpool II - 3.3.1<br></div><div id="yui_3_16_0_1_1429799079393_6826" dir="ltr">Two 9.1 backends in load balanced mode. </div><div id="yui_3_16_0_1_1429799079393_6825" dir="ltr">The 2 backends use postgres streaming replication.</div><div id="yui_3_16_0_1_1429799079393_6827" dir="ltr">DB node 0 - primary node, DB node 1 - hot standby<br></div><div id="yui_3_16_0_1_1429799079393_6828" dir="ltr">We are __not__ using pgpool for connection pool or replication.</div><div id="yui_3_16_0_1_1429799079393_6844" dir="ltr">Front end is a web application using JDBC + DBCP connection pool.<br></div><div id="yui_3_16_0_1_1429799079393_6824" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_7069" dir="ltr">I see the error in the pgpool logs as shown below, for one of the updates in my application.</div><div id="yui_3_16_0_1_1429799079393_7400" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_7353" dir="ltr">Some details on the nature of the transaction -</div><div id="yui_3_16_0_1_1429799079393_7354" dir="ltr"><br></div><div style="" class="" id="yui_3_16_0_1_1429799079393_7231" dir="ltr">There
are several records updated across multiple tables, in a single
transaction. Each update statement updates only a few records. 2 of
these tables have deferred update triggers.</div><div style="" class="" id="yui_3_16_0_1_1429799079393_7095" dir="ltr"><br style="" class=""></div><div style="" class="" id="yui_3_16_0_1_1429799079393_7096" dir="ltr">Total
time consumed in completing all the updates could be high (say 1-2
minutes), but each update could take only 1-2 seconds, not more. The default
statement timeout is set at 30 seconds.</div><div style="" class="" id="yui_3_16_0_1_1429799079393_7098" dir="ltr"><br style="" class=""></div><div style="" class="" id="yui_3_16_0_1_1429799079393_7155" dir="ltr">I have increased the statement timeout to 3 minutes, just to make sure that the transaction goes to completion.</div><div style="" class="" id="yui_3_16_0_1_1429799079393_7154" dir="ltr">The above error happens only some times and not always. <br></div><div id="yui_3_16_0_1_1429799079393_7351" style="" class="" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_7352" style="" class="" dir="ltr">Looks like, node 0 reports completion of transaction, while node 1 reports an statement timeout error.<br></div><div style="" class="" id="yui_3_16_0_1_1429799079393_7156" dir="ltr"><br style="" class=""></div><div style="" class="" id="yui_3_16_0_1_1429799079393_7160" dir="ltr">What am I doing wrong and how do I go about troubleshooting this ?</div><div id="yui_3_16_0_1_1429799079393_7399" style="" class="" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_7355" style="" class="" dir="ltr">Thanks for your help. <br></div><div id="yui_3_16_0_1_1429799079393_7356" dir="ltr"><br></div><div dir="ltr">================ Pgpool error logs ===============================================</div><div id="yui_3_16_0_1_1429799079393_7167" dir="ltr">Apr 23 09:54:27 auhhisapp01 pgpool[25760]: DB node id: 0 backend pid: 944 statement: Execute: set search_path = 'nmc'<br style="" class="">Apr 23 09:54:27 auhhisapp01 pgpool[25760]: DB node id: 1 backend pid: 28735 statement: Execute: set search_path = 'nmc'<br style="" class="">Apr 23 09:54:27 auhhisapp01 pgpool[25760]: DB node id: 0 backend pid: 944 statement: Parse: set statement_timeout = 30000<br style="" class="">Apr 23 09:54:27 auhhisapp01 pgpool[25760]: DB node id: 1 backend pid: 28735 statement: Parse: set statement_timeout = 30000<br style="" class="">Apr 23 09:54:27 auhhisapp01 rsyslogd-2177: imuxsock begins to drop messages from pid 25760 due to rate-limiting<br style="" class="">Apr 23 09:54:29 auhhisapp01 pgpool[12387]: DB node id: 1 backend pid: 28738 statement: Execute: COMMIT<br style="" class="">Apr 23 09:54:29 auhhisapp01 pgpool[12387]: pool_send_and_wait: Error or notice message from backend: : DB node id: 1 backend pid: 28738 statement: COMMIT message: canceling statement due to statement timeout<br style="" class="">Apr 23 09:54:29 auhhisapp01 pgpool[12387]: read_kind_from_backend: 1 th kind E does not match with master or majority connection kind C<br style="" class="">Apr 23 09:54:29 auhhisapp01 pgpool[12387]: kind mismatch among backends. Possible last query was: "COMMIT" kind details are: 0[C] 1[E: canceling statement due to statement timeout]<br style="" class="">Apr 23 09:54:29 auhhisapp01 pgpool[12387]: do_child: exits with status 1 due to error<br style="" class="">Apr 23 09:54:30 auhhisapp01 pgpool[15802]: DB node id: 0 backend pid: 15013 statement: Parse: set search_path = 'nmc'<br style="" class="">Apr 23 09:54:30 auhhisapp01 pgpool[15802]: DB node id: 1 backend pid: 5694 statement: Parse: set search_path = 'nmc'<br style="" class="">Apr 23 09:54:30 auhhisapp01 pgpool[15802]: DB node id: 0 backend pid: 15013 statement: B message<br style="" class="">=============================================================================================</div><br><div id="yui_3_16_0_1_1429799079393_7159" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_6798" dir="ltr"><br></div><div id="yui_3_16_0_1_1429799079393_6613"><br></div></div></body></html>