Re: improving wraparound behavior

On Fri, May 3, 2019 at 8:45 PM Andres Freund <andres@anarazel.de> wrote:
> Part of my opposition to just disabling it when close to a wraparound,
> is that it still allows to get close to wraparound because of truncation
> issues.

Sure ... it would definitely be better if vacuum didn't consume XIDs
when it truncates.  On the other hand, only a minority of VACUUM
operations will truncate, so I don't think there's really a big
problem in practice here.

> IMO preventing getting closer to wraparound is more important
> than making it more "comfortable" to be in a wraparound situation.

I think that's a false dichotomy.  It's impossible to create a
situation where no user ever gets into a wraparound situation, unless
we're prepared to do things like automatically drop replication slots
and automatically roll back (or commit?) prepared transactions.  So,
while it is good to prevent a user from getting into a wraparound
situation where we can, it is ALSO good to make it easy to recover
from those situations as painlessly as possible when they do happen.

> The second problem I see is that even somebody close to a wraparound
> might have an urgent need to free up some space. So I'm a bit wary of
> just disabling it.

I would find that ... really surprising.  If you have < 11 million
XIDs left before your data gets eaten by a grue, and you file a bug
report complaining that vacuum won't truncate your tables until you
catch up on vacuuming a bit, I am prepared to offer you no sympathy at
all.  I mean, I'm not going to say that we couldn't invent more
complicated behavior, at least on master, like making the new VACUUM
(TRUNCATE) object ternary-valued: default is on when you have more
than 11 million XIDs remaining and off otherwise, but you can override
either value by saying VACUUM (TRUNCATE { ON | OFF }).  But is that
really a thing?  People have less than 11 million XIDs left and
they're like "forget anti-wraparound, I want to truncate away some
empty pages"?

> Wonder if there's a reasonable way that'd allow to do the WAL logging
> for the truncation without using an xid. One way would be to just get
> rid of the lock on the primary as previously discussed. But we could
> also drive the locking through the WAL records that do the actual
> truncation - then there'd not be a need for an xid.  It's probably not a
> entirely trivial change, but I don't think it'd be too bad?

Beats me.  For me, this is just a bug, not an excuse to redesign
vacuum truncation.  Before Hot Standby, when you got into severe
wraparound trouble, you could vacuum all your tables without consuming
any XIDs.  Now you can't.  That's bad, and I think we should come up
with some kind of back-patchable solution to that problem.

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