Re: max_standby_delay considered harmful

Robert Haas wrote:
> On Sun, May 9, 2010 at 6:58 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> On Monday 10 May 2010 00:25:44 Florian Pflug wrote:
>>> On May 9, 2010, at 22:01 , Robert Haas wrote:
>>>> On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
>> wrote:
>>>> Seems like it could take FOREVER on a busy system. Surely that's not
>>>> OK. The fact that Hot Standby has to take exclusive locks that can't
>>>> be released until WAL replay has progressed to a certain point seems
>>>> like a fairly serious wart.
>>> If this is a serious wart then it's not one of hot standby, but one of
>>> postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as
>>> opposed to UPDATE/DELETE-blocking locks) are never necessary from a
>>> correctness POV, they're only there for implementation reasons.
>>>
>>> Getting rid of them doesn't seem completely insurmountable either - just as
>>> multiple row versions remove the need to block SELECTs dues to concurrent
>>> UPDATEs, multiple datafile versions could remove the need to block SELECTs
>>> due to concurrent ALTERs. But people seem to live with them quite well,
>>> judged from the amount of work put into getting rid of them (zero). I
>>> therefore fail to see why they should pose a significant problem in HS
>>> setups.
>> The difference is that in HS you have to wait for a moment where *no exclusive
>> lock at all* exist, possibly without contending for any of them, while on the
>> master you might not even blocked by the existence of any of those locks.
>>
>> If you have two sessions which in overlapping transactions lock different
>> tables exlusively you have no problem shutting the master down, but you will
>> never reach a point where no exclusive lock is taken on the slave.
>
> A possible solution to this in the shutdown case is to kill anyone
> waiting on a lock held by the startup process at the same time we kill
> the startup process, and to kill anyone who subsequently waits for
> such a lock as soon as they attempt to take it.

If you're not going to apply any more WAL records before shutdown, you
could also just release all the AccessExclusiveLocks held by the startup
process. Whatever the transaction was doing with the locked relation, if
we're not going to replay any more WAL records before shutdown, we will
not see the transaction committing or doing anything else with the
relation, so we should be safe. Whatever state the data on disk is in,
it must be valid, or we would have a problem with crash recovery
recovering up to this WAL record and then starting up too.

I'm not 100% clear if that reasoning applies to AccessExclusiveLocks
taken explicitly with LOCK TABLE. It's not clear what the application
would use the lock for.

Nevertheless, maybe killing the transactions that wait for the locks
would be more intuitive anyway.

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