Re: pg_upgrade < 9.3 -> >=9.3 misses a step around multixacts

On 2014-06-13 13:51:51 -0400, Alvaro Herrera wrote:
> Andres Freund wrote:
> > Hi,
> >
> > When upgrading a < 9.3 cluster pg_upgrade doesn't bother to keep the old
> > multixacts around because they won't be read after the upgrade (and
> > aren't compatible). It just resets the new cluster's nextMulti to the
> > old + 1.
> > Unfortunately that means that there'll be a offsets/0000 file created by
> > initdb around. Sounds harmless enough, but that'll actually cause
> > problems if the old cluster had a nextMulti that's bigger than that
> > page.
> >
> > When vac_truncate_clog() calls TruncateMultiXact() that'll scan
> > pg_multixact/offsets to find the earliest existing segment. That'll be
> > 0000. If the to-be-truncated data is older than the last existing
> > segment it returns. Then it'll try to determine the last required data
> > in members/ by accessing the oldest data in offsets/.
>
> I'm trying to understand the mechanism of this bug, and I'm not
> succeeding.  If the offset/0000 was created by initdb, how come we try
> to delete a file that's not also members/0000?  I mean, surely the file
> as created by initdb is empty (zeroed).  In your sample error message
> downthread,

The bit you're missing is essentially the following in TruncateMultiXact():
    /*
     * Note we can't just plow ahead with the truncation; it's possible that
     * there are no segments to truncate, which is a problem because we are
     * going to attempt to read the offsets page to determine where to
     * truncate the members SLRU.  So we first scan the directory to determine
     * the earliest offsets page number that we can read without error.
     */
That check is thwarted due to the 0000 segment. So the segment
containing the oldestMXact has already been removed, but we don't notice
that that's the case.

> ERROR: could not access status of transaction 2072053907
> DETAIL: Could not open file "pg_multixact/offsets/7B81": No such file or directory.
>
> what prompted the status of that multixid to be sought?  I see one
> possible path to this error message, which is SlruPhysicalReadPage().
> (There are other paths that lead to similar errors, but they use
> "transaction 0" instead, so we can rule those out; and we can rule out
> anything that uses MultiXactMemberCtl because of the path given in
> DETAIL.)

Same function:
    /*
     * First, compute the safe truncation point for MultiXactMember. This is
     * the starting offset of the multixact we were passed as MultiXactOffset
     * cutoff.
     */
    {
        int            pageno;
        int            slotno;
        int            entryno;
        MultiXactOffset *offptr;

        /* lock is acquired by SimpleLruReadPage_ReadOnly */

        pageno = MultiXactIdToOffsetPage(oldestMXact);
        entryno = MultiXactIdToOffsetEntry(oldestMXact);

        slotno = SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl, pageno,
                                            oldestMXact);
        offptr = (MultiXactOffset *)
            MultiXactOffsetCtl->shared->page_buffer[slotno];
        offptr += entryno;
        oldestOffset = *offptr;

        LWLockRelease(MultiXactOffsetControlLock);
    }

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services