Re: Re: pgsql: In HS, Startup process sets SIGALRM when waiting for buffer pin.

Simon Riggs wrote:
> On Sat, 2010-01-23 at 21:40 +0000, Greg Stark wrote:
>> On Sat, Jan 23, 2010 at 8:28 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> What is your proposed way of handling buffer pin deadlocks? That will be
>>> acceptable and working to some extent in the next week?
>>>
>>> Wait forever isn't always a good idea, anymore, if it ever was.
>> I've never said it was always a good idea. But killing correctly
>> running queries isn't always a good idea either. I'm interested in
>> using HS for running read-only replicas for load balancing. It would
>> pretty sad if queries dispatched to a read-only replica received a
>> spurious unpredictable errors for reasons the application programmer
>> cannot control.
>
> I understand your concern and seek to provide the best way forwards in
> the time available. Hopefully you have a better way, but we can do
> little about the time. Your input is welcome, and your code also.

I just woke up to this thread too. I have to agree with Greg, we must
think harder.

Can you summarize the problem again? I don't immediately see how the
deadlock could happen.

Would this simple scheme work:

When the startup process has waited for a short while (ie
deadlock_timeout), it sends the signal "please check if you're holding a
pin on buffer X" to all backends. When a backend receives that signal,
it checks if it is holding a pin on the given buffer *and* waiting on a
lock. If it is, abort the transaction. Assuming that a backend can only
block waiting on a lock held by the startup process, deadlock detection
is as simple as that.

> Given the stop gap does what -1 says it will never do, ISTM that having
> -1 would be contradictory. I did not wish to remove it, but it seemed
> safer to do so. Putting it back is straightforward, if it makes sense.

For all practical purposes, INT_MAX, which is the upper limit for
max_standby_delay, is the same as infinity. So removing -1 doesn't
really get you out of jail. And no, let's not make the upper limit
smaller, there's no natural upper limit for that setting.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com