Re: failover vs. read only queries

On Thu, Jun 10, 2010 at 5:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> The fact that failover current does *not* terminate existing queries and
>> transactions was regarded as a feature by the audience, rather than a
>> bug, when I did demos of HS/SR.  Of course, they might not have been
>> thinking of the delay for writes.
>
>> If there were an easy way to make the trigger file cancel all running
>> queries, apply remaining logs and come up, then I'd vote for that for
>> 9.0.  I think it's the more desired behavior by most users.  However,
>> I'm opposed to any complex solutions which might delay 9.0 release.
>
> My feeling about it is that if you want fast failover you should not
> have your failover target server configured as hot standby at all, let
> alone hot standby with a long max_standby_delay.  Such a slave could be
> very far behind on applying WAL when the crunch comes, and no amount of
> query killing will save you from that.  Put your long-running standby
> queries on a different slave instead.
>
> We should consider whether we can improve the situation in 9.1, but it
> is not a must-fix for 9.0; especially when the correct behavior isn't
> immediately obvious.

OK. Let's revisit in 9.1.

I attached the proposal patch for 9.1. The patch treats max_standby_delay
as zero (i.e., cancels all the conflicting queries immediately), ever since
the trigger file is created. So we can cause a recovery to end without
waiting for any lock held by queries, and minimize the failover time.
OTOH, queries which don't conflict with a recovery survive the failover.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center