Re: Disabled features on Hot Standby

On Fri, Jan 13, 2012 at 8:27 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The open items list for 9.2 says
>
> "Index-only scans need to be disabled when running under Hot Standby"
>
> There is no explanation, so please explain here so we can change it,
> if possible.
>
> Not sure its great policy to keep implementing things that don't work
> on HS, while not giving a proper chance for other people to fix that.

Well, I agree that it's not a great policy to implement lots of things
that don't work on Hot Standby, but this particular issue is an old
one.

http://archives.postgresql.org/pgsql-hackers/2008-10/msg01422.php

Since the xmin horizon on the standby may precede the xmin horizon on
the master, we can't assume that a page is all-visible on the standby
just because it's all-visible on the master. The crash-safe
visibililty map code is in some ways a step forward for Hot Standby,
because in previous releases, we didn't WAL-log setting visibility map
bits at all, and therefore a just-promoted Hot Standby server would
have no bits set apart from those which had been continuously set
since the base backup was taken, or those it happened to get by chance
in full page images. Even in old releases, that's not particularly
good, because it means that (1) sequential scans on a just-promoted
standby will be slower, since those can leverage PD_ALL_VISIBLE to
avoid retail MVCC checks on every tuple and (2) the first VACUUM on
each table on the standby after promotion will possibly need to scan a
lot more of the table than was being scanned by a typical VACUUM on
the old master, owing to the relevant bits in the visibillity map not
being set. 9.2 will be better in this regard, because the bits will
propagate from master to standby as they're set on the master, but
it's still not safe to rely on them until we've exited recovery.

I'm not sure I have a brilliant idea for how to fix this. We could
skip replay of any WAL record that sets a visibility map or
PD_ALL_VISIBLE bit unless the page is also all-visible under the
standby's xmin horizon, but we'd need some way to know that, and we'd
need to worry not only about the records emitted by vacuum that
explicitly set the bit, but also about any place where we send the
whole page and the bit goes along with it; we'd need to add code to go
in and clear the bit. That seems a bit complex and error-prone. Or,
we could try to lift this restriction in the special case when
hot_standby_feedback is set, though I have a feeling that's not really
robust - any time you lose the connection to the master, it'll lose
your xmin holdback and possibly mark some things all-visible that
really aren't on the standby, and then you reconnect and replay those
WAL records and bad things happen.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company