Re: [ADMIN] Replication slots and isolation levels

> 29 окт. 2015 г., в 15:29, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> написал(а):
>
> On Thu, Oct 29, 2015 at 12:35 PM, Vladimir Borodin wrote:
>> 29 окт. 2015 г., в 14:03, Michael Paquier написал(а):
>>> Standby will receive the record but not replay it until the
>>> transaction doing REPEATABLE READ transactions that needs those rows
>>> commits on the standby. The WAL flush position on the standby
>>> continues to move on.
>>
>> By replication lag on standby I mean exactly replay_location, not
>> flush_location.
>> Well, the initial problem is that in read commited mode heavy
>> SELECT-statement hits max_standby_streaming_delay but in repeatable read
>> mode doesn’t. My question is if it is expected behavior? If yes, why is it
>> so?
>
> Er, well. If I enforce on master the deletion then VACUUM-cleanup of a
> page with a REPEATABLE READ transaction on standby still expecting to
> have this page items visible until its commit the startup process puts
> itself in waiting state when trying to replay the cleanup record, and
> the replay_location does not move on, still the wal receiver gets WAL
> in parallel, so it continues to flush things and flush_position
> progresses. With a READ COMMITTED transaction running on the standby,
> this transaction considers as visible stuff that has been committed,
> so WAL replay can move on, and indeed there is a risk to face a
> recovery conflict. So this behavior as-is is correct, based on how
> isolation levels should behave when a node performs recovery.

Everything you describe is exactly true for setups without replication slots. And the ability to run heavy SELECT statements on hot standby without replication lag and recovery conflicts was the reason why I tried to use them. And the documentation [0] directly says that «Replication slots provide an automated way to ensure ... that the master does not remove rows which could cause a recovery conflict even when the standby is disconnected». My question is why is it true for REPEATABLE READ transactions but it doesn’t work for READ COMMITED queries? Seems, that «even when the standby is disconnected» is much stronger limitation and READ COMMITED should work fine, but it doesn’t.

If I understand right, with hot_standby_feedback = on standby tells the master xmin of the earliest transaction on standby. And autovacuum worker on master takes it into account when doing vacuum cleanup (because it can see it from pg_replications_slots view), AFAIK with GetOldestXmin function. But I don’t understand why with READ COMMITED transactions xmin in pg_replication_slots view on master continues to increase while with REPEATABLE READ xmin freezes until this transaction finishes.

[0] http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS <http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS>
> --
> Michael
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Да пребудет с вами сила…
https://simply.name/ru