Re: foreign key locks, 2nd attempt

Excerpts from Noah Misch's message of mié mar 14 19:10:00 -0300 2012:
>
> On Wed, Mar 14, 2012 at 01:23:14PM -0400, Robert Haas wrote:
> > On Tue, Mar 13, 2012 at 11:42 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > > More often than that; each 2-member mxid takes 4 bytes in an offsets file and
> > > 10 bytes in a members file. ?So, more like one fsync per ~580 mxids. ?Note
> > > that we already fsync the multixact SLRUs today, so any increase will arise
> > > from the widening of member entries from 4 bytes to 5. ?The realism of this
> > > test is attractive. ?Nearly-static parent tables are plenty common, and this
> > > test will illustrate the impact on those designs.
> >
> > Agreed. But speaking of that, why exactly do we fsync the multixact SLRU today?
>
> Good question. So far, I can't think of a reason. "nextMulti" is critical,
> but we already fsync it with pg_control. We could delete the other multixact
> state data at every startup and set OldestVisibleMXactId accordingly.

Hmm, yeah.

> > > You still have HEAP_XMAX_{INVALID,COMMITTED} to reduce the pressure on mxid
> > > lookups, so I think something more sophisticated is needed to exercise that
> > > cost. ?Not sure what.
> >
> > I don't think HEAP_XMAX_COMMITTED is much help, because committed !=
> > all-visible. HEAP_XMAX_INVALID will obviously help, when it happens.
>
> True. The patch (see ResetMultiHintBit()) also replaces a multixact xmax with
> the updater xid when all transactions of the multixact have ended.

I have noticed that this code is not correct, because we don't know that
we're holding an appropriate lock on the page, so we can't simply change
the Xmax and reset those hint bits. As things stand today, mxids
persist longer. (We could do some cleanup at HOT-style page prune, for
example, though the lock we need is even weaker than that.) Overall
this means that coming up with a test case demonstrating this pressure
probably isn't that hard.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support