Replication slot xmin is not reset if HS feedback is turned off while standby is shut down

From: Ants Aasma <ants(dot)aasma(at)eesti(dot)ee>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Replication slot xmin is not reset if HS feedback is turned off while standby is shut down
Date: 2016-12-20 16:52:20
Message-ID: CA+CSw_v5bhRD7=bV7_Uaya3qTo=LYN6t3R50WDeL25LhZKpCuQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I just had a client issue with table bloat that I traced back to a
stale xmin value in a replication slot. xmin value from hot standby
feedback is stored in replication slot and used for vacuum xmin
calculation. If hot standby feedback is turned off while walreceiver
is active then the xmin gets reset by HS feedback message containing
InvalidTransactionId. However, if feedback gets turned off while
standby is shut down this message never gets sent and a stale value
gets left behind in the replication slot holding back vacuum.

The simple fix seems to be to always send out at least one feedback
message on each connect regardless of hot_standby_feedback setting.
Patch attached. Looks like this goes back to version 9.4. It could
conceivably cause issues for replication middleware that does not know
how to handle hot standby feedback messages. Not sure if any exist and
if that is a concern.

A shell script to reproduce the problem is also attached, adjust the
PGPATH variable to your postgres install and run in an empty
directory.

Regards,
Ants Aasma

Attachment Content-Type Size
send-hot-standby-feedback-on-first-connect.patch text/x-patch 1.7 KB
slot-xmin-not-reset-reproduce.sh application/x-sh 2.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-12-20 16:53:55 Re: Declarative partitioning - another take
Previous Message Alvaro Herrera 2016-12-20 16:45:32 Re: Declarative partitioning - another take