<div dir="ltr">Actually our slaves are never very far behind at all, but it only takes one long-running query to get stuck and that causes PostgreSQL to cancel all currently running queries. Ideally the database wouldn't need to cancel the non-offending queries but that's currently a limitation of streaming replication. We've already increased the max_standby_archive_delay and max_standby_streaming_delay to very long amounts, and we just want any queries that happen to take longer than that amount to be timed-out but that unfortunately has the side effect of affecting unrelated queries in the way I've described.<div>
<br></div><div style>Since we have hundreds of different applications and pages written in different languages and by owned by different users/customers, it's not really practical for us to "fix our code" to each attempt to retry every possible SQL statement if that error occurs. Since all of these applications go through pgpool, I was thinking that it would be a perfect place to introduce this sort of retry/failover logic.</div>
<div style><br></div><div style><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, Apr 4, 2013 at 6:05 PM, Lonni J Friedman <span dir="ltr"><<a href="mailto:netllama@gmail.com" target="_blank">netllama@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">From my perspective this doesn't seem like a useful feature. But its<br>
not my call to make.<br>
<br>
Wouldn't it better to figure out why your slaves are getting too far<br>
behind, and fixing that problem? Or fix your code to be more<br>
resilient to inconsistent data?<br>
<div><div class="h5"><br>
On Thu, Apr 4, 2013 at 3:56 PM, Jeff Lawson <<a href="mailto:jeff@bovine.net">jeff@bovine.net</a>> wrote:<br>
> We're running streaming replication on PostgreSQL 9.2 and we occasionally<br>
> hit the well-known "canceling statement due to conflict with recovery" error<br>
> whenever a slave gets too far behind. Since this error is seemingly<br>
> triggered on unrelated queries that happen to still be running at the same<br>
> time as the offending long-running query, it's difficult to make all parts<br>
> of our code resilient to this problem.<br>
><br>
> I'd like to suggest that pgpool add optional retry logic that could be<br>
> configured to automatically repeat a query up to a specified number of times<br>
> if the database triggers that specific error message. Opinions?<br>
><br>
><br>
> Jeff<br>
><br>
</div></div>> _______________________________________________<br>
> pgpool-general mailing list<br>
> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
><br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
<br>
--<br>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br>
L. Friedman <a href="mailto:netllama@gmail.com">netllama@gmail.com</a><br>
LlamaLand <a href="https://netllama.linux-sxs.org" target="_blank">https://netllama.linux-sxs.org</a><br>
</font></span></blockquote></div><br></div>