Re: [ADMIN] Replication slots and isolation levels
| От | Vladimir Borodin |
|---|---|
| Тема | Re: [ADMIN] Replication slots and isolation levels |
| Дата | |
| Msg-id | 86D2C6D2-C332-4BF9-A82E-C9F7FA471712@simply.name обсуждение исходный текст |
| Ответ на | Re: [ADMIN] Replication slots and isolation levels (Michael Paquier <michael.paquier@gmail.com>) |
| Ответы |
Re: [ADMIN] Replication slots and isolation levels
(Oleksii Kliukin <alexk@hintbits.com>)
|
| Список | pgsql-hackers |
29 окт. 2015 г., в 15:29, Michael Paquier <michael.paquier@gmail.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.
--
Michael
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
В списке pgsql-hackers по дате отправления: