Re: BUG #13443: master will remove dead rows when hot standby(use slot) disconnect

On Mon, Jun 15, 2015 at 5:52 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Tue, Jun 16, 2015 at 4:30 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > On Mon, Jun 15, 2015 at 2:05 AM, <digoal(at)126(dot)com> wrote:
> >>
> http://www.postgresql.org/docs/devel/static/warm-standby.html#STREAMING-REPLICATION
> >>
> >> 25.2.6. Replication Slots
> >> Replication slots provide an automated way to ensure that the master
> does
> >> not remove WAL segments until they have been received by all standbys,
> and
> >> that the master does not remove rows which could cause a recovery
> conflict
> >> even when the standby is disconnected.
> >>
>
>
> > One potential doc bug I see is that the it seems to imply that
> replication
> > slots replaces the need for hot_standby_feedback, when it fact it must be
> > used in conjunction with it. Do you have hot_standby_feedback turned on
> in
> > the standby?
>
> As far as I recall, using replication slots implies that the
> RecentGlobalXmin horizon is updated to guarantee the presence of
> tuples on the standby once it reconnects. Perhaps I am missing
> something?
>

I haven't looked at the code, or paid much attention when the feature went
in. From the docs, I thought that is what would happen as well. But
experimentally, that only happens if hot_standby_feedback is on. I get the
same behavior
on 9.4.4 and on 9.5devel.

postgresql.conf:

wal_level = logical
max_wal_senders = 3
max_replication_slots = 3
hot_standby = on
hot_standby_feedback = off ## toggled on standby over course of
experiment
port=5433 ### on standby only. default on master

on master:
SELECT * FROM pg_create_physical_replication_slot('node_a_slot');

recovery.conf:
standby_mode = 'on'
primary_conninfo = 'port=5432 user=jjanes'
primary_slot_name = 'node_a_slot'

I repeat Digoal's example, only don't crash the standby.

I leave the stand-by connected, with a client sitting in a repeatable read
transaction, and then delete the tuples on the master side. Vacuum will
quickly reclaim the tuples, and then after 30s the stand-by session gets
disconnect due to conflict. When I toggle hot_standby_feedback on, then it
behaves as expected, with the master never cleaning up the deleted tuples.

But if I do set hot_standby_feedback to on, then I still can't reproduce
Digoal's example.

Cheers,

Jeff