Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated)

tgarnett@panjiva.com wrote:

> ERROR: could not access status of transaction 303450738
> DETAIL: Could not open file "pg_multixact/members/7B49": No such file or
> directory.

Bruce and Kevin both pinged me about this issue recently.  Turns out
that I have an incomplete patch to close the problem.  Just to clarify,
this is a completely new problem, not related to #8673.

The fix is to raise an ERROR when generating a new multixact, if we
detect that doing so would get close to the oldest multixact that the
system knows about.  If that happens, the solution is to vacuum so that
the "oldest" point is advanced a bit more and you have room to generate
more multixacts.  In production, you would typically adjust the
multixact freeze parameters so that "oldest multixact" is advanced more
aggressively and you don't hit the ERROR.

A fix I pushed to master (for a performance regression reportes as bug
#8470) would make the problem less common, by having typical multixact
sizes be smaller.  I didn't backpatch that fix due to lack of feedback,
but since it is connected to this data-eating bug, maybe we should look
into doing so.  This problem only arises if your multixacts are larger
than 24 members in average (or something like that.  I don't recall the
exact number.)  That should be atypical, except that prior to the #8470
fix the multixact size is related to number of subtransactions doing
certain operations in a loop.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services