[pgpool-general: 1572] Re: Automatic retry on canceled statements

[Jeff Lawson]

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.

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.




On Thu, Apr 4, 2013 at 6:05 PM, Lonni J Friedman <netllama at gmail.com> wrote:

> From my perspective this doesn't seem like a useful feature.  But its
> not my call to make.
>
> Wouldn't it better to figure out why your slaves are getting too far
> behind, and fixing that problem?  Or fix your code to be more
> resilient to inconsistent data?
>
> On Thu, Apr 4, 2013 at 3:56 PM, Jeff Lawson <jeff at bovine.net> wrote:
> > We're running streaming replication on PostgreSQL 9.2 and we occasionally
> > hit the well-known "canceling statement due to conflict with recovery"
> error
> > whenever a slave gets too far behind.  Since this error is seemingly
> > triggered on unrelated queries that happen to still be running at the
> same
> > time as the offending long-running query, it's difficult to make all
> parts
> > of our code resilient to this problem.
> >
> > I'd like to suggest that pgpool add optional retry logic that could be
> > configured to automatically repeat a query up to a specified number of
> times
> > if the database triggers that specific error message.  Opinions?
> >
> >
> > Jeff
> >
> > _______________________________________________
> > pgpool-general mailing list
> > pgpool-general at pgpool.net
> > http://www.pgpool.net/mailman/listinfo/pgpool-general
> >
>
>
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> L. Friedman                                    netllama at gmail.com
> LlamaLand                       https://netllama.linux-sxs.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130404/e7aaa42e/attachment-0001.html>