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

[Tatsuo Ishii]

I think one of the problems is when "canceling statement..." happens,
the transaction has been already aborted by PostgreSQL. That means to
retry the SELECT, you need to restart the aborted transaction in the
first place. And to restart the transaction, you need to remember all
the SET command executed in the aborted transaction. I think this is
not trivial. Hope PostgreSQL would mitigate the user's pain in the
future...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
>>