On 2013-11-27 18:18:02 -0500, Noah Misch wrote:
> > I think the likelihood of the problem affecting !all-visible pages is
> > close to zero. Each vacuum will try to clean those, so they surely will
> > get vacuumed at some point. I think the only way that could happen is if
> > the ConditionalLockBufferForCleanup() fails in each vacuum. And that
> > seems a bit unlikely.
>
> The page could have sat all-visible (through multiple XID epochs, let's say)
> until a recent UPDATE.
Good point.
> > > At first, I supposed we could offer a tool to blindly freeze such tuples.
> > > However, there's no guarantee that they are in harmony with recent changes to
> > > the database; transactions that wrongly considered those tuples invisible may
> > > have made decisions incompatible with their existence. For example, reviving
> > > such a tuple could violate a UNIQUE constraint if the user had already
> > > replaced the missing row manually.
> >
> > Good point, although since they are all on all-visible pages sequential
> > scans will currently already find those. It's primarily index scans that
> > won't. So it's not really reviving them...
>
> True. Since a dump/reload of the database would already get the duplicate key
> violation, the revival is not making anything clearly worse. And if we hope
> for manual repair, many DBAs just won't do that at all.
Especially if it involves compiling C code...
> > The primary reason why I think it might be a good idea to "revive"
> > automatically is, that an eventual full-table/freeze vacuum will
> > currently delete them which seems bad.
>
> Will it? When the page became all-visible, the tuples were all hinted. They
> will never be considered dead. Every 2B transactions, they will alternate
> between live and not-yet-committed.
Good point again. And pretty damn good luck.
Although 9.3+ multixact rows look like they could return _DEAD since
we'll do an TransactionIdDidAbort() (via GetUpdateXid()) and
TransactionIdDidCommit() in there and we don't set XMAX_COMMITTED hint
bits for XMAX_IS_MULTI rows. As an additional problem, once multixact.c
has pruned the old multis away we'll get errors from there on.
But that's less likely to affect many rows.
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services